michael_krell
asked on
VB.net SQL insert resulting in duplicate records
I am working on a vb application that grabs PLC data and inserts it in batches into a sql table. Everything seems to work however i am getting duplicate records. Can anyone tell me why? I have attached the code that does the SQL insert.
Thanks
Thanks
Private Sub InsertSQL()
Dim sqlstring As String, dr As DataRow
sqlstring = ""
Try
For Each drw As DataRow In DsetSQL.Tables(0).Rows
sqlstring = sqlstring + "INSERT INTO [PNJ_Data].[dbo].[L1Pack_Weights] ([Manuf_Time],[Filler_Num],[Setpoint],[Weight],[Container],[Preact],[Bulk_Pulses],[Trim_Pulses],[ID]) VALUES ("
sqlstring = sqlstring + "'" + drw(0).ToString + "',"
sqlstring = sqlstring + drw(1).ToString + ","
sqlstring = sqlstring + drw(2).ToString + ","
sqlstring = sqlstring + drw(3).ToString + ","
sqlstring = sqlstring + drw(4).ToString + ","
sqlstring = sqlstring + drw(5).ToString + ","
sqlstring = sqlstring + drw(6).ToString + ","
sqlstring = sqlstring + drw(7).ToString + ","
sqlstring = sqlstring + drw(8).ToString + "); "
Next
Try
' MsgBox(sqlstring)
myConnection.Close()
myConnection.Open()
dr = DsetLog.Tables(0).NewRow
dr(0) = DateTime.Now
dr(1) = sqlstring 'drw(8).ToString
DsetLog.Tables(0).Rows.Add(dr)
da.InsertCommand = New SqlCommand(sqlstring, myConnection)
da.InsertCommand.ExecuteNonQuery()
Catch ex As Exception
dr = DsetLog.Tables(0).NewRow
dr(0) = DateTime.Now
dr(1) = "Error Inserting Row: " + ex.Message
DsetLog.Tables(0).Rows.Add(dr)
Exit Sub
End Try
dr = DsetLog.Tables(0).NewRow
dr(0) = DateTime.Now
dr(1) = "Batch insert complete"
DsetLog.Tables(0).Rows.Add(dr)
Catch ex As Exception
dr = DsetLog.Tables(0).NewRow
dr(0) = DateTime.Now
dr(1) = "Error Inserting Batch Data: " + ex.Message
DsetLog.Tables(0).Rows.Add(dr)
SQLFailed = True
Exit Sub
End Try
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There are two questions here: the cause of duplicates being inserted and how to detect the ones already inserted. I suggest you separate the two questions.
For the causes...
<<i will do that, but it still doesn't explain why i have duplicates. >>
As I said, the reason why you have difficulty knowing is because you are trying to implement unicity on the client, when the only effective way to do that is on the server side, using constraints.
For detecting the duplicates...
<<Is there anyway that i can run a query that shows me transactions to help me troubleshoot this?>>
Can you list the columns on L1Pack_Weights table make you consider a row to be a duplicate of another when these column values are the same for two different rows. Without this information we can not help you detect the duplicates.
HTH
For the causes...
<<i will do that, but it still doesn't explain why i have duplicates. >>
As I said, the reason why you have difficulty knowing is because you are trying to implement unicity on the client, when the only effective way to do that is on the server side, using constraints.
For detecting the duplicates...
<<Is there anyway that i can run a query that shows me transactions to help me troubleshoot this?>>
Can you list the columns on L1Pack_Weights table make you consider a row to be a duplicate of another when these column values are the same for two different rows. Without this information we can not help you detect the duplicates.
HTH
ASKER
Is there anyway that i can run a query that shows me transactions to help me troubleshoot this?