Errors on database insert - works only sometimes


Mar 13, 2013
Programming Experience
I'm working in Visual Studio 2012 and I'm running this code to insert a row into a history database when a field on an page is changed. It seems to work 1 out of every three times I test it without debug on - and has absolutely no problems when running in debug mode (which made me think that I just needed to add wait functions at points of my code - but that didn't work). For the most part, it just doesn't insert the row, but for one user, it gives the error: "Index was out of range. Must be non-negative and less than the size of the collection." Any help would be much appreciated.

If e.Item.OwnerTableView.Name = "DigitalInsertions" Then
       Dim fullAdNumber As String = e.Item.GetDataKeyValue("AdNumber")
       Dim Adnumber As String = Mid(fullAdNumber, 3, 7)
       Dim IS_Property_Updated As String
       Dim IS_Label_Updated As String
       Dim IS_DeliveryType_Updated As String
       Dim IS_ESOV_Updated As String
       Dim IS_StartDate_Updated As String
       Dim IS_MonthlyImpressions_Updated As String
       Dim IS_CPM_Updated As String
       Dim IS_FlatRateAmount_Updated As String
       Dim IS_YahooProgram_Updated As String
       Dim IS_YahooTarget_Updated As String
       Dim IS_GeoZones_Updated As String
       Dim IS_YahooDMATarget_Updated As String
       Dim IS_CreativeWorkOrderNum_Updated As String
       Dim IS_Subsite_Updated As String
       Dim IS_Position_Updated As String
       Dim IS_EndDate_Updated As String
       Dim IS_TotalImpressions_Updated As String
       Dim IS_TotalAmount_Updated As String
       Dim IS_ClickThruURL_Updated As String
       Dim IS_CancelIO_Updated As String
       Dim IS_BTComments_Updated As String
       Dim LastUpdatedby As String
       Dim text1 As String
       Dim text2 As String
       Dim text3 As String
       Dim text4 As String
       Dim text5 As String
       Dim text6 As String
       Dim text7 As String
       Dim text8 As String
       Dim text9 As String
       Dim text10 As String
       Dim text11 As String
       Dim text12 As String
       Dim text13 As String
       Dim text14 As String
       Dim text15 As String
       Dim text16 As String
       Dim text17 As String
       Dim text18 As String
       Dim text19 As String
       Dim text20 As String
       Dim text21 As String
       Dim LastUpdatedDateSql As String = "SELECT [dbo].[fn_MostRecentModifiedDateForThisAdDIR]('" + AdNumber + "')"
       Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("CRMConnectionString").ToString()
       Dim command As SqlCommand = Nothing
       command = New SqlCommand(LastUpdatedDateSql, New SqlConnection(strConnString))
       Dim LastUpdatedDate As String = command.ExecuteScalar().ToString
       Dim mostrecentsavedate As DateTime = DateTime.ParseExact(LastUpdatedDate, "M/dd/yyyy h:mm:ss tt", Globalization.CultureInfo.InvariantCulture)
       Dim mysearchdate As String = mostrecentsavedate.ToString("yyyy-MM-dd HH:mm:ss")
       Dim sql2 As String = "SELECT * FROM [dbo].[fn_GetChangedRowsDIR] () where AdNumber='" + Adnumber + "' and Lastupdateddate >= '" + mysearchdate + "' and lastupdateddate< dateadd(s,3,'" + mysearchdate + "')"
       command = New SqlCommand(sql2, New SqlConnection(strConnString))
       Dim reader As SqlDataReader = command.ExecuteReader()
           While reader.Read
               IS_Property_Updated = reader.GetValue(1).ToString
               IS_Label_Updated = reader.GetValue(2).ToString
               IS_DeliveryType_Updated = reader.GetValue(3).ToString
               IS_ESOV_Updated = reader.GetValue(4).ToString
               IS_StartDate_Updated = reader.GetValue(5).ToString
               IS_MonthlyImpressions_Updated = reader.GetValue(6).ToString
               IS_CPM_Updated = reader.GetValue(7).ToString
               IS_FlatRateAmount_Updated = reader.GetValue(8).ToString
               IS_YahooProgram_Updated = reader.GetValue(9).ToString
               IS_YahooTarget_Updated = reader.GetValue(10).ToString
               IS_GeoZones_Updated = reader.GetValue(11).ToString
               IS_YahooDMATarget_Updated = reader.GetValue(12).ToString
               IS_CreativeWorkOrderNum_Updated = reader.GetValue(13).ToString
               IS_Subsite_Updated = reader.GetValue(14).ToString
               IS_Position_Updated = reader.GetValue(15).ToString
               IS_EndDate_Updated = reader.GetValue(16).ToString
               IS_TotalImpressions_Updated = reader.GetValue(17).ToString
               IS_TotalAmount_Updated = reader.GetValue(18).ToString
               IS_ClickThruURL_Updated = reader.GetValue(19).ToString
               IS_CancelIO_Updated = reader.GetValue(20).ToString
               IS_BTComments_Updated = reader.GetValue(21).ToString
               LastUpdatedby = reader.GetValue(54).ToString
               If IS_Property_Updated = "True" Then
                   text1 = "Property was updated, "
                   text1 = ""
               End If
               If IS_Label_Updated = "True" Then
                   text2 = "Label was updated, "
                   text2 = ""
               End If
               If IS_DeliveryType_Updated = "True" Then
                   text3 = "DeliveryType was updated, "
                   text3 = ""
               End If
               If IS_ESOV_Updated = "True" Then
                   text4 = "ESOV was updated, "
                   text4 = ""
               End If
               If IS_StartDate_Updated = "True" Then
                   text5 = "StartDate was updated, "
                   text5 = ""
               End If
               If IS_MonthlyImpressions_Updated = "True" Then
                   text6 = "MonthlyImpressions was updated, "
                   text6 = ""
               End If
               If IS_CPM_Updated = "True" Then
                   text7 = "CPM was updated, "
                   text7 = ""
               End If
               If IS_FlatRateAmount_Updated = "True" Then
                   text8 = "FlatRateAmount was updated, "
                   text8 = ""
               End If
               If IS_YahooProgram_Updated = "True" Then
                   text9 = "YahooProgram was updated, "
                   text9 = ""
               End If
               If IS_YahooTarget_Updated = "True" Then
                   text10 = "YahooTarget was updated, "
                   text10 = ""
               End If
               If IS_GeoZones_Updated = "True" Then
                   text11 = "GeoZones was updated, "
                   text11 = ""
               End If
               If IS_YahooDMATarget_Updated = "True" Then
                   text12 = "YahooDMATarget was updated, "
                   text12 = ""
               End If
               If IS_CreativeWorkOrderNum_Updated = "True" Then
                   text13 = "CreativeWorkOrderNum was updated, "
                   text13 = ""
               End If
               If IS_Subsite_Updated = "True" Then
                   text14 = "Subsite was updated, "
                   text14 = ""
               End If
               If IS_Position_Updated = "True" Then
                   text15 = "Position was updated, "
                   text15 = ""
               End If
               If IS_EndDate_Updated = "True" Then
                   text16 = "EndDate was updated, "
                   text16 = ""
               End If
               If IS_TotalImpressions_Updated = "True" Then
                   text17 = "TotalImpressions was updated, "
                   text17 = ""
               End If
               If IS_TotalAmount_Updated = "True" Then
                   text18 = "TotalAmount was updated, "
                   text18 = ""
               End If
               If IS_ClickThruURL_Updated = "True" Then
                   text19 = "ClickThruURL was updated, "
                   text19 = ""
               End If
               If IS_CancelIO_Updated = "True" Then
                   text20 = "CancelIO was updated, "
                   text20 = ""
               End If
               If IS_BTComments_Updated = "True" Then
                   text21 = "BTComments was updated, "
                   text21 = ""
               End If
               Dim mychange As String = "" +  text1 & text2 & text3 & text4 & text5 & text6 &  text7 & text8 & text9 & text10 & text11 & text12  & text13 & text14 & text15 & text16 & text17 &  text18 & text19 & text20 & text21 + "."
               Dim Sql3 As String = "INSERT INTO [dbo].[DigitalInsertionOrderHistory] " &
       "([LastUpdatedby]" &
       ",[LastUpdatedDate]" &
       ",[DIORecID]" &
       ",[Changes])" &
       " VALUES (" &
       "'" & LastUpdatedby & "'" &
       ",'" & mysearchdate & "'" &
       ",'" & Adnumber & "'" &
       ",'" & mychange & "')"
               Dim strConnString2 As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("CRMConnectionString").ToString()
               Dim sqlConn As SqlConnection = Nothing
               sqlConn = New SqlConnection(strConnString2)
               Dim sqlcmd As New SqlCommand()
               sqlcmd.Connection = sqlConn
               sqlcmd.CommandText = Sql3
           End While
       Catch ex As Exception
       End Try
   End If


p.s. I had posted something similar earlier - but thought I had found the solution in the form of the wait function - so I edited it to say nevermind (I couldn't figure out how to edit a second time to remove the 'nevermind' comment) and I was hoping that that is the reason I didn't receive any further response. sorry for posting twice, but I really really need some help with this.
You've got a great big wad of code inside a Try block there and if anything goes wrong you simply ignore and carry on. Don't ignore the exception you are catching because it tells you what went wrong and where.
You've got a great big wad of code inside a Try block there and if anything goes wrong you simply ignore and carry on. Don't ignore the exception you are catching because it tells you what went wrong and where.

I removed the Try - Catch -Finally - End Try from the code. It runs perfectly everytime while in debug mode. In Live, it runs perfectly one out of every two tries it seems and then fails silently (no error) the other 50% of the time.
It will always fail silently if you don't catch the error. You need to catch the SQLException and display the exception's Message property.

Okay, I added back in the Try - Catch- Finally - End Try and tried adding this:

Catch ex As Exception
                Dim str As String
                str = "Source : " & ex.Source
                str &= ControlChars.NewLine
                str &= "Exception Message : " & ex.Message
                Console.WriteLine("General Exception" & str)

It is still failing silently. It is weird because it ran fine 5 times in a row and then nothing.