Database not getting updated

[vsc33]

My vb.net application runs on a sql server 2000 and it is about making bills in a fast running gift counter with 5 to 6 client and server is placed at a distance of 200 meters. The store sells maximum of 50-60 gifts. The user does all the transaction through clicks.

Two tables are involved for making a transaction. One is mastertable and detailtable.

In the mastertable my application is inserting the total of the bill, date, amount and the counter person’s code. It then generates an auto number which I picks up after the data is inserted in the mastertable and then uses the same in the detailtable.

The detailtable holds fields like item, qty, rate,id of mastertable etc.

My application is running smooth, but recently I noticed that say one in 500th time, row is not added to mastertable while it is present in detailtable with the id of mastertable. I have binded the sql with commit. I have digged my code but am not finding any logic behind this error.

Please help. Thanks in advance.

[r035198x]

My vb.net application runs on a sql server 2000 and it is about making bills in a fast running gift counter with 5 to 6 client and server is placed at a distance of 200 meters. The store sells maximum of 50-60 gifts. The user does all the transaction through clicks.

Two tables are involved for making a transaction. One is mastertable and detailtable.

In the mastertable my application is inserting the total of the bill, date, amount and the counter person’s code. It then generates an auto number which I picks up after the data is inserted in the mastertable and then uses the same in the detailtable.

The detailtable holds fields like item, qty, rate,id of mastertable etc.

My application is running smooth, but recently I noticed that say one in 500th time, row is not added to mastertable while it is present in detailtable with the id of mastertable. I have binded the sql with commit. I have digged my code but am not finding any logic behind this error.

Please help. Thanks in advance.

You'll probably need to post the relevant code then.

[vsc33]

Here is the code:

con = connect()
con.Open()

'transaction begins here-------
trans = con.BeginTransa ction()

Try

'Data is inserted into mastertable---------

cmd = New SqlClient.SqlCo mmand("INSERT INTO mastertable(mbn o,mbdate,mbamou nt,mbcperson) VALUES(" & counter_bill_no & ",'" & server_date & "'," & billvalue & "," & countercode & ")", con, trans)
cmd.ExecuteNonQ uery()

cmd = New SqlClient.SqlCo mmand("select mbid from mastertable WHERE mbno=" & counter_bill_no & " and mbcperson=" & countercode & " ", con, trans)
mb_id = cmd.ExecuteScal ar

'Data inserted into detailtable-----------

i = 0
For i = 0 To row_no - 1
selling_rate = DataGridView1.I tem(2, i).Value
If selling_rate <> 0 Then
cmd = New SqlClient.SqlCo mmand("INSERT INTO detailtable(dbn o,dbdate,dbicod e,biquantity,db amount) VALUES(" & mb_id & ",'" & server_date & "'," & DataGridView1.I tem(4, i).Value & "," & DataGridView1.I tem(1, i).Value & "," & DataGridView1.I tem(3, i).Value & ")", con, trans)
cmd.ExecuteNonQ uery()
End If
Next i

'Data commited--------

trans.Commit()

Catch ex As Exception

trans.Rollback( )
MsgBox("Bill could not be generated, Please try again.")

End Try

con.Close()