Errors on database insert - works only sometimes

D

debbieb

Member
Joined
Mar 13, 2013
Messages
8
Programming Experience
Beginner
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 asp.net 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))
       command.Connection.Open()
 
       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")
 
       System.Threading.Thread.Sleep(3000)
       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))
       command.Connection.Open()
       Dim reader As SqlDataReader = command.ExecuteReader()
       Try
           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
               System.Threading.Thread.Sleep(2000)
               If IS_Property_Updated = "True" Then
                   text1 = "Property was updated, "
               Else
                   text1 = ""
               End If
               If IS_Label_Updated = "True" Then
                   text2 = "Label was updated, "
               Else
                   text2 = ""
               End If
               If IS_DeliveryType_Updated = "True" Then
                   text3 = "DeliveryType was updated, "
               Else
                   text3 = ""
               End If
               If IS_ESOV_Updated = "True" Then
                   text4 = "ESOV was updated, "
               Else
                   text4 = ""
               End If
               If IS_StartDate_Updated = "True" Then
                   text5 = "StartDate was updated, "
               Else
                   text5 = ""
               End If
               If IS_MonthlyImpressions_Updated = "True" Then
                   text6 = "MonthlyImpressions was updated, "
               Else
                   text6 = ""
               End If
               If IS_CPM_Updated = "True" Then
                   text7 = "CPM was updated, "
               Else
                   text7 = ""
               End If
               If IS_FlatRateAmount_Updated = "True" Then
                   text8 = "FlatRateAmount was updated, "
               Else
                   text8 = ""
               End If
               If IS_YahooProgram_Updated = "True" Then
                   text9 = "YahooProgram was updated, "
               Else
                   text9 = ""
               End If
               If IS_YahooTarget_Updated = "True" Then
                   text10 = "YahooTarget was updated, "
               Else
                   text10 = ""
               End If
               If IS_GeoZones_Updated = "True" Then
                   text11 = "GeoZones was updated, "
               Else
                   text11 = ""
               End If
               If IS_YahooDMATarget_Updated = "True" Then
                   text12 = "YahooDMATarget was updated, "
               Else
                   text12 = ""
               End If
               If IS_CreativeWorkOrderNum_Updated = "True" Then
                   text13 = "CreativeWorkOrderNum was updated, "
               Else
                   text13 = ""
               End If
               If IS_Subsite_Updated = "True" Then
                   text14 = "Subsite was updated, "
               Else
                   text14 = ""
               End If
               If IS_Position_Updated = "True" Then
                   text15 = "Position was updated, "
               Else
                   text15 = ""
               End If
               If IS_EndDate_Updated = "True" Then
                   text16 = "EndDate was updated, "
               Else
                   text16 = ""
               End If
               If IS_TotalImpressions_Updated = "True" Then
                   text17 = "TotalImpressions was updated, "
               Else
                   text17 = ""
               End If
               If IS_TotalAmount_Updated = "True" Then
                   text18 = "TotalAmount was updated, "
               Else
                   text18 = ""
               End If
               If IS_ClickThruURL_Updated = "True" Then
                   text19 = "ClickThruURL was updated, "
               Else
                   text19 = ""
               End If
               If IS_CancelIO_Updated = "True" Then
                   text20 = "CancelIO was updated, "
               Else
                   text20 = ""
               End If
               If IS_BTComments_Updated = "True" Then
                   text21 = "BTComments was updated, "
               Else
                   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 + "."
               System.Threading.Thread.Sleep(5000)
               Dim Sql3 As String = "INSERT INTO [dbo].[DigitalInsertionOrderHistory] " &
       "([LastUpdatedby]" &
       ",[LastUpdatedDate]" &
       ",[DIORecID]" &
       ",[Changes])" &
       " VALUES (" &
       "'" & LastUpdatedby & "'" &
       ",'" & mysearchdate & "'" &
       ",'" & Adnumber & "'" &
       ",'" & mychange & "')"
               System.Threading.Thread.Sleep(2000)
               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()
               sqlConn.Open()
               sqlcmd.Connection = sqlConn
               System.Threading.Thread.Sleep(1000)
               sqlcmd.CommandText = Sql3
               sqlcmd.ExecuteNonQuery()
               sqlConn.Close()
           End While
       Catch ex As Exception
       Finally
           command.Connection.Close()
command.Connection.Close()
       End Try
   End If


Thanks!

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.
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
jmcilhinney said:
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.
Click to expand...


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.
 
Upvote 0 Downvote
Herman said:
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.
Click to expand...

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)
            Finally


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

Similar threads

krishanusen
  • Question
Question table and database connection through textbox and add save edit command not working
Replies
4
Views
7K
jdelano
J
ismetothman
  • Question
No error in code but exception unhandled was appear during debugging
Replies
2
Views
5K
jmcilhinney
jmcilhinney
krishanusen
  • Question
database not connected and getting error
Replies
1
Views
3K
krishanusen
krishanusen
J
  • Question
Perform search on Datagridview using SQL queries
Replies
0
Views
1K
joshface98
J
P
  • Question
Question click button on window via sendmessage doesn`t work
Replies
0
Views
4K
peterg2000
P

Share this page

Latest posts

Back
Top