Solved

VB.net SQL insert resulting in duplicate records

Posted on 2010-11-11
3
728 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

737 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