Solved

VB.net SQL insert resulting in duplicate records

Posted on 2010-11-11
3
703 Views
Last Modified: 2012-05-10
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
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

Open in new window

0
Comment
Question by:michael_krell
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 34115011
A suggestion: If you implement a unicity constraint on the [L1Pack_Weights]  table, the database will reject your insert and probably tell you why.   That is why constraints are used in the first place:
> Prevent dupplicates to be inserted
> Tell you when and how the dupplicate breaks the unicity.

Hope this helps...
0
 

Author Comment

by:michael_krell
ID: 34115696
i will do that, but it still doesn't explain why i have duplicates. And it just seems random.

Is there anyway that i can run a query that shows me transactions to help me troubleshoot this?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 34116374
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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now