Why SQL insert two records at the same time duplicate from VB?

Question

Here is my code in vb :

 cmd = New SqlCommand("INSERT INTO export (eDate, qty, stockID, empID, machineID, jobID, receiptNO, plateQty, eDesc) VALUES (@eDate, @qty, @stockID, @empID, @machineID, @jobID, @receiptNO, @plateQty, @eDesc)", con)
    cmd.CommandTimeout = 1000
    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("@eDate", dateTXT.Text)
    cmd.Parameters.AddWithValue("@qty", qtyTXT.Text)
    cmd.Parameters.AddWithValue("@stockID", comboStock.SelectedValue)
    cmd.Parameters.AddWithValue("@empID", comboEmp.SelectedValue)
    cmd.Parameters.AddWithValue("@machineID", comboMachine.SelectedValue)
    cmd.Parameters.AddWithValue("@jobID", comboJob.SelectedValue)
    cmd.Parameters.AddWithValue("@receiptNO", receiptTXT.Text)
    cmd.Parameters.AddWithValue("@plateQty", TextBox1.Text)
    cmd.Parameters.AddWithValue("@eDesc", descTXT.Text)

    cmd.ExecuteNonQuery()
    If (cmd.ExecuteNonQuery) Then
        cmd = New SqlCommand("UPDATE stock SET qty = qty - @qty WHERE id = '" & comboStock.SelectedValue & "'", con)
        cmd.Parameters.AddWithValue("@qty", qtyTXT.Text)
        cmd.ExecuteNonQuery()
        MsgBox.Show("Successfuly saved!")
    Else

        MessageBox.Show("Not saved!")

    End If

I have two tables which are (Exports and Stock) when I insert a record in export table it should also update the stock items but my question is that when I insert a record to export it save it on table duplicate. how should I do this?

You are executing your query 2 times. cmd.ExecuteNonQuery() If (cmd.ExecuteNonQuery) Then — Mata Prasad Chauhan, Jan 15 '18 at 05:35

score 1 · Answer 1 · answered Jan 15 '18 at 05:38

You are executing your insert query 2 times

cmd.ExecuteNonQuery()
If (cmd.ExecuteNonQuery) Then

If you want to check if something was inserted, remove the ExecuteNonQuery() on top and change your If statement to this.

If (cmd.ExecuteNonQuery) > 0 Then

Brien Foss · Answer 2 · 2018-01-15T05:43:17.520

This part of your code is calling ExecuteNonQuery() again:

If (cmd.ExecuteNonQuery) Then

which is what is causing the duplicate INSERT of:

SqlCommand("INSERT INTO export (eDate, qty, stockID, empID, machineID, jobID, receiptNO, plateQty, eDesc) 
            VALUES (@eDate, @qty, @stockID, @empID, @machineID, @jobID, @receiptNO, @plateQty, @eDesc)", con)

I would personally create an Insert trigger for your requirement of:

when I insert a record in export table it should also update the stock items

score 0 · Answer 3 · edited Jan 15 '18 at 12:18

 cmd.ExecuteNonQuery()    '//This will insert to the DB

 '// I know you are only checking to see if the insert was successful, '
 '// but you are essentially inserting again, then testing.'
 If (cmd.ExecuteNonQuery) Then
    ...
    ...
 End If

Here is one way to avoid duplicate insert.

Dim NumberOfRowsInserted = cmd.ExecuteNonQuery()
'// now this variable will hold either 1 if the insert command was successful'
'// or 0 if the insert failed'

If NumberOfRowsInserted > 0 Then
    '// do whatever'
    ...
    ...
    ...
End If

Why SQL insert two records at the same time duplicate from VB?

3 Answers3