Results 1 to 12 of 12

Thread: SQL Indexing out of sync possibly?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    SQL Indexing out of sync possibly?

    I will try to explain this as best I can. I apologize for the lengthy post but I am trying to be clear.

    My one table in my MSSQL DB has Indexing messed up, or so it seems.

    I have ran into this issue within the last few weeks and not sure what is causing this.

    I currently have 816 INDEXED lines within my LOAD_INFO_TABLE, MS SQL database.

    FURTHER detail to clarify....

    My concern is within the last week when I want to add a new record I immediately receive an error that there is no row at position xxx (currently the last record is at index 816). If I bypass out of that error message box and create the record anyways the record saves fine and takes the next available, and proper, index number... thereby the next record would then be index 817.

    I verified this by deleting a few of the last records, and recreated them. Every time I go past the notification box, the record writes fine and indexes to the next available index, however the message box consistently comes back on every ADD NEW request.

    I thought maybe something was amiss so I deleted a few of the latest records, only to find no matter what I do the messagebox comes back every time I try to add a new record, regardless of index position, telling me there is no record at index xxx and is always the index PRIOR to the last record, like it is two indexes behind all the time. (ie since I currently have 816 indexes, the message box notes there is no row at index 814).

    The side effect to this issue is I need to import expenses, via a second form within the program... this also has ceased recently. When I try to import expenses and save the data to the proper record I now receive the error "An item with the same key has already been added." Debug shows it is looking at index 814 (which does exist and obviously cannot write to that index number).

    Very few changes to the forms were made and nothing related to "updates" or writes have been made to the program. Cosmetic corrections to the Primary form ONLY and some updating SQL code inside the form to avoid SQL Injection was the only thing changed. The biggest change, if it truly is a change, was the program was previously compiled using VS 2015 and now is being compiled using VS 2017.

    The expense information is written to a separate database. Once that data is saved to the EXPENSE_TABLE an import is done to the LOAD_INFO_TABLE from the EXPENSE TABLE where applicable. Some expenses are not imported as they are not relevant to the LOAD_INFO_TABLE.

    Again, it needs to be fully understood, this entire setup worked fine, error free, up to about a week ago.

    As a test, I did go back to older code (at least 2-3 versions back, where there is definitely some differences) on both the Load Input form as well as the Expense form (since no real changes ot the Expense form have been made there really was no need but did it just in case I overlooked something) and am experiencing the exact same problem where I guarantee all this worked perfectly fine at the time the older code was in production. So both current production and 2 versions back of code are experiencing the same issue. Therefore, I have to surmise something is wrong in the database.

    Once I load a record I can step forward, backward, 1st, last record and the form fills fine, no issue, no errors no complaints. the only issue is in the ADD NEW record event. Even if I make a change to the an existing record via the form I can update and save the changes fine. I am calling the ADD NEW from the standard navigation bar provided by VS and do nothing except check to ensure the record does not have changes (dirty() function) prior to adding the new record.

    Is there a proper repair for the SQL indexing that I can use? I tried a few items but it does not seem to resolve the issue. I used the MSSMS Reorganize as well as Rebuild tools but that did not resolve the issue.

    I did run "DBCC checkdb" and the following returned (not including everything just the last few lines:

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'PETS_Database'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    I desperately need to understand and find this bug as soon as possible. Please anyone who can assist I would be eternally grateful.
    Last edited by K3JAE; Apr 6th, 2019 at 10:16 AM.

  2. #2
    coder. Lord Orwell's Avatar
    Join Date
    Feb 2001
    Location
    Elberfeld, IN
    Posts
    7,621

    Re: SQL Indexing out of sync possibly?

    I would like to see your code, but is it possible you think "there are 816 records so the last one is at index 816"? You need to count 0 so your last record would be 815. I don't think that's your problem though. I think you have a different one. Indexes do not have to match the last record, at all. They never get reused unless you specifically assign that index when you write the record.
    If you delete 816 then create a new one, the new one will be 817.
    My light show youtube page (it's made the news) www.youtube.com/@artnet2twinkly
    Contact me on the socials www.facebook.com/lordorwell

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: SQL Indexing out of sync possibly?

    It is my understanding that the ID field is what "cannot" can be reused not the Index field... there is both an Index and an ID field in my table.

    There are a total of 816 records in the database - via a SELECT COUNT(*) on the database. The index shows 816, so that appears to be proper.

    Highest record ID is 862 because there have been records, throughout the life of the database. Thus a previously used ID cannot be reused.

    As for viewing the code, the code is extremely lengthy (over 3400 lines) and could be an issue posting it on the forum (not even sure it would take it). We could look at other options if you have time and ability.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: SQL Indexing out of sync possibly?

    I have a vague idea of what the issue is (it doesn't seem like the database is the issue), but in order to be sure (and provide a solution) I'd need to see some code.

    We don't need all of the code, probably just the routine that is getting the error, and possibly also whatever sets up things like datasets.
    Last edited by si_the_geek; Apr 6th, 2019 at 01:09 PM.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: SQL Indexing out of sync possibly?

    The routine where the error occurs really is nothing more than the call to add new record off the NAV bar.

    Here is the LOADFORM (I have removed a lot of the default settings to shorten the code.

    Code:
    Private Sub Load_Info_Form(sender As Object, e As EventArgs) Handles MyBase.Load
            Fuel_TableTableAdapter.Fill(Fuel.Fuel_Table)
            LoadForm = True
            Load_Info_TableTableAdapter.Fill(LoadInfoDataSet.Load_Info_Table)
    
            'Loads last record on to form
            LoadInfoDataSetBindingSource.Position = LoadInfoDataSet.Load_Info_Table.Rows.Count - 1
    
            'Setting Default Values
            LoadInfoDataSet.Load_Info_Table.Columns("ACT_MILES_DIFF_CALC").DefaultValue = 0
            LoadInfoDataSet.Load_Info_Table.Columns("ACTUAL_DH_MILES").DefaultValue = 0
            LoadInfoDataSet.Load_Info_Table.Columns("ACTUAL_LOADED_MILES").DefaultValue = 0
            LoadInfoDataSet.Load_Info_Table.Columns("TOTAL_MILES_DIFF_CALC").DefaultValue = 0
            LoadInfoDataSet.Load_Info_Table.Columns("TRACTOR_FUEL_TAX_REFUND").DefaultValue = 0D
            LoadInfoDataSet.Load_Info_Table.Columns("TRAILER_DROPPED_01").DefaultValue = "N / A"
            LoadInfoDataSet.Load_Info_Table.Columns("TRAILER_DROPPED_90").DefaultValue = "N / A"
            LoadInfoDataSet.Load_Info_Table.Columns("TRAILER_HOOKED_01").DefaultValue = "N / A"
            LoadInfoDataSet.Load_Info_Table.Columns("TRAILER_HOOKED_90").DefaultValue = "N / A"
            LoadInfoDataSet.Load_Info_Table.Columns("TRANSFLO_NUMBER").DefaultValue = "NO TRANSFLO - EMAILED"
            LoadInfoDataSet.Load_Info_Table.Columns("TRANSFLO_DATE_TIME").DefaultValue = "N / A"
            LoadInfoDataSet.Load_Info_Table.Columns("TRIP_MPG").DefaultValue = 0D
            LoadInfoDataSet.Load_Info_Table.Columns("TRIP_END_MILES").DefaultValue = 0
            LoadInfoDataSet.Load_Info_Table.Columns("TRIP_START_MILES").DefaultValue = 0
            LoadInfoDataSet.Load_Info_Table.Columns("TRLR_SHUTTLE_PAY").DefaultValue = 0
            LoadInfoDataSet.Load_Info_Table.Columns("TRUCK_FUEL_PROFIT_LOSS").DefaultValue = 0D
            LoadInfoDataSet.Load_Info_Table.Columns("TRUCK_SUPPLIES").DefaultValue = 0D
    
            DV = New DataView(LoadInfoDataSet.Load_Info_Table)
    
            StateMiles()
            MedCertExpire()
            FHUYear()
            IFTAdue()
            TrkRegdue()
            TrkInspdue()
            TrlInspdue()
            LoadResp()
            Detention01()
    
            Calc()
            LoadForm = False
        End Sub

    Code:
       Private Sub BtnAddNewItem_Click(sender As Object, e As EventArgs) Handles btnAddNewItem.Click
            If Not LoadForm Then
                Dirty()
            End If
        End Sub
    Code:
        Private Sub Dirty()
            If Not LoadForm Then
                Cursor = Cursors.WaitCursor
                Validate()
                LoadInfoDataSetBindingSource.EndEdit()
                If LoadInfoDataSet.HasChanges Then
    
                    MeMsgBoxSaveChanges.ShowDialog()
                    If MeMsgBoxSaveChanges.Result = "Yes" Then
                        btnSaveNav.PerformClick()
                    Else
                        LoadInfoDataSet.RejectChanges()
                    End If
                End If
                Cursor = Cursors.Default
            End If
        End Sub

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: SQL Indexing out of sync possibly?

    Which line does the error occur on?

    If you aren't sure, add a breakpoint and step the code until the error occurs.

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: SQL Indexing out of sync possibly?

    The error occurs on ADD NEW RECORD. In debug it never errors out back to code, it simply displays a messagebox noting "there is no row at position xxx"

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: SQL Indexing out of sync possibly?

    If you step the code as I suggested in my previous post, does the message appear when a particular line of code runs?

    If you don't know how to do it, here is a tutorial: https://docs.microsoft.com/en-gb/vis...r?view=vs-2019

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: SQL Indexing out of sync possibly?

    As soon as I click ADD NEW the next line to run is the 2nd piece of code, below, which makes no sense... there is no call to go there. This repeats 4x before the error displays.

    Code:
       Private Sub BtnStartNew_Click(sender As Object, e As EventArgs) Handles btnStartNew.Click
            If Not LoadForm Then
                Dirty()
                LoadInfoDataSetBindingSource.AddNew()
            End If
    End Sub

    Code:
       Private Sub Arrival_ValueChanged(sender As Object, e As EventArgs) Handles dtp01ArriveDate.ValueChanged, dtp01ArriveTime.ValueChanged,
                                                                                     dtp90ArriveDate.ValueChanged, dtp01ArriveTime.ValueChanged,
                                                                                     dtp01DepartDate.ValueChanged, dtp01DepartTime.ValueChanged,
                                                                                     dtp90DepartDate.ValueChanged, dtp90DepartTime.ValueChanged,
                                                                                     dtp01SchedDate.ValueChanged, dtp01SchedTime.ValueChanged,
                                                                                     dtp90SchedDate.ValueChanged, dtp90SchedTime.ValueChanged,
                                                                                     ccb01DetOverride.CheckedChanged, ccb90DetOverride.CheckedChanged
    
            Dim a As Decimal
    
            Dim shipS = (dtp01SchedDate.Value.Date) + (dtp01SchedTime.Value.TimeOfDay)   ' 01 Scheduled P/U Date/Time
            Dim shipA = (dtp01ArriveDate.Value.Date) + (dtp01ArriveTime.Value.TimeOfDay) ' 01 Arrival Date/Time
            Dim shipD = (dtp01DepartDate.Value.Date) + (dtp01DepartTime.Value.TimeOfDay) ' 01 Departure Date/Time
    
            Dim rcvrS = (dtp90SchedDate.Value.Date) + (dtp90SchedTime.Value.TimeOfDay)   ' 90 Scheduled Delivery Date/Time
            Dim rcvrA = (dtp90ArriveDate.Value.Date) + (dtp90ArriveTime.Value.TimeOfDay) ' 90 Arrival Date/Time
            Dim rcvrD = (dtp90DepartDate.Value.Date) + (dtp90DepartTime.Value.TimeOfDay) ' 90 Departure Date/Time
    
            Dim ts1 As TimeSpan = shipA.Subtract(shipS) ' Shipper Arrival Date/Time - Shipper Scheduled Date/Time
            Dim ts2 As TimeSpan = rcvrA.Subtract(rcvrS) ' Receiver Arrival Date/Time - Receiver Scheduled Date/Time
            Dim ts3 As TimeSpan = shipD.Subtract(shipS) ' Shipper Departure Date/Time - Shipper Scheduled Date/Time
            Dim ts4 As TimeSpan = rcvrD.Subtract(rcvrS) ' Receiver Departure Date/Time - Receiver Scheduled Date/Time
    
            'Convert a timespan to an absolute (positive) value
            Dim arrive01 As Double = Math.Abs(ts1.TotalMinutes)
            Dim arrive90 As Double = Math.Abs(ts2.TotalMinutes)
            Dim depart01 As Double = Math.Abs(ts3.TotalMinutes)
            Dim depart90 As Double = Math.Abs(ts4.TotalMinutes)
    
            'Round DOWN to the nearest 15 minute mark to determine Detention.
            Dim sArriveDN = Decimal.Floor(CDec(arrive01 / 15)) * 15
            Dim sDepartDN = Decimal.Floor(CDec(depart01 / 15)) * 15
            Dim rArriveDN = Decimal.Floor(CDec(arrive90 / 15)) * 15
            Dim rDepartDN = Decimal.Floor(CDec(depart90 / 15)) * 15
    
            'Round DOWN to the nearest 15 minute mark to determine Detention.
            Dim sArriveUP = Decimal.Ceiling(CDec(arrive01 / 15)) * 15
            Dim sDepartUP = Decimal.Ceiling(CDec(depart01 / 15)) * 15
            Dim rArriveUP = Decimal.Ceiling(CDec(arrive90 / 15)) * 15
            Dim rDepartUP = Decimal.Ceiling(CDec(depart90 / 15)) * 15
    
            Try
                ' Is Detention Due from Shipper?
                Select Case ts3.TotalMinutes
                    Case Is >= 180
                        tb01.BackColor = Color.Red
                    Case Is >= 120
                        tb01.BackColor = Color.Yellow
                    Case Else
                        tb01.BackColor = Color.Chartreuse
                End Select
    
                ' Is Detention Due from Receiver?
                Select Case ts4.TotalMinutes
                    Case Is >= 180
                        tb90.BackColor = Color.Red
                    Case Is >= 120
                        tb90.BackColor = Color.Yellow
                    Case Else
                        tb90.BackColor = Color.Chartreuse
                End Select
    
                'Did truck arrive on time at Shipper?
                Select Case ts1.TotalMinutes
                    Case Is >= 30
                        lblPUTime.BackColor = Color.Red
                        lblPUTime.ForeColor = Color.White
                    Case Is >= 15
                        lblPUTime.BackColor = Color.Yellow
                        lblPUTime.ForeColor = Color.Black
                    Case Is >= 5
                        lblPUTime.BackColor = Color.Cyan
                        lblPUTime.ForeColor = Color.Black
                    Case Else
                        lblPUTime.BackColor = Color.Transparent
                        lblPUTime.ForeColor = Color.Black
                End Select
    
                'Did truck arrive on time at Receiver?
                Select Case ts2.TotalMinutes
                    Case Is >= 30
                        lblDroppedDate.BackColor = Color.Red
                        lblDroppedDate.ForeColor = Color.White
                    Case Is >= 15
                        lblDroppedDate.BackColor = Color.Yellow
                        lblDroppedDate.ForeColor = Color.Black
                    Case Is >= 5
                        lblDroppedDate.BackColor = Color.Cyan
                        lblDroppedDate.ForeColor = Color.Black
                    Case Else
                        lblDroppedDate.BackColor = Color.Transparent
                        lblDroppedDate.ForeColor = Color.Black
                End Select
            Catch ex As Exception
                Call New MeMsgCalcError(ex, "'Detention Determination' Calculation Error. Lines 1104-1155").Show()
            End Try
    
            Try
                Select Case tbBrokerAgency.Text
                 'Coyote Logistics, RTS, Schneider, TQL or Lipsey Logistics
                    Case "COYOTE LOGISTICS", "LIPSEY LOGISTICS", "RELIABLE TRANSPORTATION SOLUTIONS", "SCHNEIDER FREIGHT", "TOTAL QUALITY LOGISTICS"
                        If (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 AndAlso (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = CDec((sDepartDN - 120) / (30 * 17.5)) + CDec((rDepartDN - 120) / 30 * 17.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 Then
                            a = CDec((sDepartDN - 120) / 30 * 17.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = CDec((rDepartDN - 120) / 30 * 17.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        Else
                            a = 0
                            tbDetentionOwed.Text = a.ToString("C2")
                        End If
    
                 'CH Robinson Logistics
                    Case "CH ROBINSON"
                        If (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 AndAlso (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = ((sDepartUP - 120) / (30 * 25)) + (rDepartUP - 120) / 30 * 25
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 Then
                            a = (sDepartUP - 120) / 30 * 25
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = (rDepartUP - 120) / 30 * 25
                            tbDetentionOwed.Text = a.ToString("C2")
                        Else
                            a = 0
                            tbDetentionOwed.Text = a.ToString("C2")
                        End If
    
                 'Uber Freight
                    Case "UBER FREIGHT"
                        If (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 AndAlso (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = CDec((sDepartUP - 120) / (30 * 37.5)) + CDec((rDepartUP - 120) / 30 * 37.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 Then
                            a = CDec((sDepartUP - 120) / 30 * 37.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = CDec((rDepartUP - 120) / 30 * 37.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        Else
                            a = 0
                            tbDetentionOwed.Text = a.ToString("C2")
                        End If
                    Case Else
                        Exit Select
    
                End Select
            Catch ex As Exception
                Call New MeMsgCalcError(ex, "'Detention Owed' Calculation Error. Lines 1157-1210").Show()
            End Try
        End Sub

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: SQL Indexing out of sync possibly?

    As soon as I click ADD NEW (first code set) the next line to run is the 2nd piece of code, below, which makes no sense... there is no call to go there. This repeats 4x before the error displays.

    Code:
       Private Sub BtnStartNew_Click(sender As Object, e As EventArgs) Handles btnStartNew.Click
            If Not LoadForm Then
                Dirty()
                LoadInfoDataSetBindingSource.AddNew()
            End If
    End Sub

    Code:
       Private Sub Arrival_ValueChanged(sender As Object, e As EventArgs) Handles dtp01ArriveDate.ValueChanged, dtp01ArriveTime.ValueChanged,
                                                                                     dtp90ArriveDate.ValueChanged, dtp01ArriveTime.ValueChanged,
                                                                                     dtp01DepartDate.ValueChanged, dtp01DepartTime.ValueChanged,
                                                                                     dtp90DepartDate.ValueChanged, dtp90DepartTime.ValueChanged,
                                                                                     dtp01SchedDate.ValueChanged, dtp01SchedTime.ValueChanged,
                                                                                     dtp90SchedDate.ValueChanged, dtp90SchedTime.ValueChanged,
                                                                                     ccb01DetOverride.CheckedChanged, ccb90DetOverride.CheckedChanged
    
            Dim a As Decimal
    
            Dim shipS = (dtp01SchedDate.Value.Date) + (dtp01SchedTime.Value.TimeOfDay)   ' 01 Scheduled P/U Date/Time
            Dim shipA = (dtp01ArriveDate.Value.Date) + (dtp01ArriveTime.Value.TimeOfDay) ' 01 Arrival Date/Time
            Dim shipD = (dtp01DepartDate.Value.Date) + (dtp01DepartTime.Value.TimeOfDay) ' 01 Departure Date/Time
    
            Dim rcvrS = (dtp90SchedDate.Value.Date) + (dtp90SchedTime.Value.TimeOfDay)   ' 90 Scheduled Delivery Date/Time
            Dim rcvrA = (dtp90ArriveDate.Value.Date) + (dtp90ArriveTime.Value.TimeOfDay) ' 90 Arrival Date/Time
            Dim rcvrD = (dtp90DepartDate.Value.Date) + (dtp90DepartTime.Value.TimeOfDay) ' 90 Departure Date/Time
    
            Dim ts1 As TimeSpan = shipA.Subtract(shipS) ' Shipper Arrival Date/Time - Shipper Scheduled Date/Time
            Dim ts2 As TimeSpan = rcvrA.Subtract(rcvrS) ' Receiver Arrival Date/Time - Receiver Scheduled Date/Time
            Dim ts3 As TimeSpan = shipD.Subtract(shipS) ' Shipper Departure Date/Time - Shipper Scheduled Date/Time
            Dim ts4 As TimeSpan = rcvrD.Subtract(rcvrS) ' Receiver Departure Date/Time - Receiver Scheduled Date/Time
    
            'Convert a timespan to an absolute (positive) value
            Dim arrive01 As Double = Math.Abs(ts1.TotalMinutes)
            Dim arrive90 As Double = Math.Abs(ts2.TotalMinutes)
            Dim depart01 As Double = Math.Abs(ts3.TotalMinutes)
            Dim depart90 As Double = Math.Abs(ts4.TotalMinutes)
    
            'Round DOWN to the nearest 15 minute mark to determine Detention.
            Dim sArriveDN = Decimal.Floor(CDec(arrive01 / 15)) * 15
            Dim sDepartDN = Decimal.Floor(CDec(depart01 / 15)) * 15
            Dim rArriveDN = Decimal.Floor(CDec(arrive90 / 15)) * 15
            Dim rDepartDN = Decimal.Floor(CDec(depart90 / 15)) * 15
    
            'Round DOWN to the nearest 15 minute mark to determine Detention.
            Dim sArriveUP = Decimal.Ceiling(CDec(arrive01 / 15)) * 15
            Dim sDepartUP = Decimal.Ceiling(CDec(depart01 / 15)) * 15
            Dim rArriveUP = Decimal.Ceiling(CDec(arrive90 / 15)) * 15
            Dim rDepartUP = Decimal.Ceiling(CDec(depart90 / 15)) * 15
    
            Try
                ' Is Detention Due from Shipper?
                Select Case ts3.TotalMinutes
                    Case Is >= 180
                        tb01.BackColor = Color.Red
                    Case Is >= 120
                        tb01.BackColor = Color.Yellow
                    Case Else
                        tb01.BackColor = Color.Chartreuse
                End Select
    
                ' Is Detention Due from Receiver?
                Select Case ts4.TotalMinutes
                    Case Is >= 180
                        tb90.BackColor = Color.Red
                    Case Is >= 120
                        tb90.BackColor = Color.Yellow
                    Case Else
                        tb90.BackColor = Color.Chartreuse
                End Select
    
                'Did truck arrive on time at Shipper?
                Select Case ts1.TotalMinutes
                    Case Is >= 30
                        lblPUTime.BackColor = Color.Red
                        lblPUTime.ForeColor = Color.White
                    Case Is >= 15
                        lblPUTime.BackColor = Color.Yellow
                        lblPUTime.ForeColor = Color.Black
                    Case Is >= 5
                        lblPUTime.BackColor = Color.Cyan
                        lblPUTime.ForeColor = Color.Black
                    Case Else
                        lblPUTime.BackColor = Color.Transparent
                        lblPUTime.ForeColor = Color.Black
                End Select
    
                'Did truck arrive on time at Receiver?
                Select Case ts2.TotalMinutes
                    Case Is >= 30
                        lblDroppedDate.BackColor = Color.Red
                        lblDroppedDate.ForeColor = Color.White
                    Case Is >= 15
                        lblDroppedDate.BackColor = Color.Yellow
                        lblDroppedDate.ForeColor = Color.Black
                    Case Is >= 5
                        lblDroppedDate.BackColor = Color.Cyan
                        lblDroppedDate.ForeColor = Color.Black
                    Case Else
                        lblDroppedDate.BackColor = Color.Transparent
                        lblDroppedDate.ForeColor = Color.Black
                End Select
            Catch ex As Exception
                Call New MeMsgCalcError(ex, "'Detention Determination' Calculation Error. Lines 1104-1155").Show()
            End Try
    
            Try
                Select Case tbBrokerAgency.Text
                 'Coyote Logistics, RTS, Schneider, TQL or Lipsey Logistics
                    Case "COYOTE LOGISTICS", "LIPSEY LOGISTICS", "RELIABLE TRANSPORTATION SOLUTIONS", "SCHNEIDER FREIGHT", "TOTAL QUALITY LOGISTICS"
                        If (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 AndAlso (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = CDec((sDepartDN - 120) / (30 * 17.5)) + CDec((rDepartDN - 120) / 30 * 17.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 Then
                            a = CDec((sDepartDN - 120) / 30 * 17.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = CDec((rDepartDN - 120) / 30 * 17.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        Else
                            a = 0
                            tbDetentionOwed.Text = a.ToString("C2")
                        End If
    
                 'CH Robinson Logistics
                    Case "CH ROBINSON"
                        If (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 AndAlso (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = ((sDepartUP - 120) / (30 * 25)) + (rDepartUP - 120) / 30 * 25
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 Then
                            a = (sDepartUP - 120) / 30 * 25
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = (rDepartUP - 120) / 30 * 25
                            tbDetentionOwed.Text = a.ToString("C2")
                        Else
                            a = 0
                            tbDetentionOwed.Text = a.ToString("C2")
                        End If
    
                 'Uber Freight
                    Case "UBER FREIGHT"
                        If (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 AndAlso (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = CDec((sDepartUP - 120) / (30 * 37.5)) + CDec((rDepartUP - 120) / 30 * 37.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts1.TotalMinutes <= 10 Or ccb01DetOverride.CheckState = CheckState.Checked) AndAlso ts3.TotalMinutes > 120 Then
                            a = CDec((sDepartUP - 120) / 30 * 37.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        ElseIf (ts2.TotalMinutes <= 10 Or ccb90DetOverride.CheckState = CheckState.Checked) AndAlso ts4.TotalMinutes > 120 Then
                            a = CDec((rDepartUP - 120) / 30 * 37.5)
                            tbDetentionOwed.Text = a.ToString("C2")
                        Else
                            a = 0
                            tbDetentionOwed.Text = a.ToString("C2")
                        End If
                    Case Else
                        Exit Select
    
                End Select
            Catch ex As Exception
                Call New MeMsgCalcError(ex, "'Detention Owed' Calculation Error. Lines 1157-1210").Show()
            End Try
        End Sub

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: SQL Indexing out of sync possibly?

    There doesn't need to be a direct call to it, because it is an event (ie: something that automatically runs in response to something).

    BtnStartNew_Click calls Dirty(), which in turn calls various things including LoadInfoDataSetBindingSource.EndEdit(), and that is likely to cause the event to fire. It sounds like you aren't stepping one line of code at a time, and I recommend you do because it makes things much clearer.

    The fact it runs 4 times implies that 4 of the controls listed in the Handles clause have changed values.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: SQL Indexing out of sync possibly?

    OK, after spending some time, and reverting back to older code, again,and inputting the newer individual items back in that were added since the old code, and after each new piece I tested the following has been determined:

    Code:
       Private Sub Detention_ValueChanged(sender As Object, e As EventArgs) Handles dtp01ArriveDate.ValueChanged, dtp01ArriveTime.ValueChanged,
                                                                                     dtp90ArriveDate.ValueChanged, dtp01ArriveTime.ValueChanged,
                                                                                     dtp01DepartDate.ValueChanged, dtp01DepartTime.ValueChanged,
                                                                                     dtp90DepartDate.ValueChanged, dtp90DepartTime.ValueChanged,
                                                                                     dtp01SchedDate.ValueChanged, dtp01SchedTime.ValueChanged,
                                                                                     dtp90SchedDate.ValueChanged, dtp90SchedTime.ValueChanged,
                                                                                     ccb01DetOverride.CheckedChanged, ccb90DetOverride.CheckedChanged
    The above sub is where the problem lies. I need to be able to have this section work if any of the items in the handle are changed while in the record, but as soon as I try to start a new record the error returns. If I rename the sub to:

    Code:
    Private Sub Detention()
    I can add the new record fine with no errors, but I lose the handles. Is there a way to still make this function work when one of the handles change and not cause a problem with There is no row at position xxx error?

    I tried adding "If formload=true then exit sub end if" just after the declaration of the sub but that does not resolve the issue.
    Last edited by K3JAE; Apr 8th, 2019 at 12:59 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width