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.
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.
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.
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.
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
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.
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"
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
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
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
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.
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.
