Database not getting updated

[vsc33]

Hello everybody in the problem solving world !!!!.........

Well I have come across a unique problem and am not able to find any logic behind that.

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.

 

[cjard]

How can your relationship FK in SQLS allow the child to exist without a parent?

 

[theAsocialApe]

are you doing your sql statements in logical units of work?

is there possibly some kind of deadly embrace problem?

can you post your sql?

 

[vsc33]

Here is the code:

con = connect()
con.Open()

'transaction begins here-------
trans = con.BeginTransaction()

Try

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

cmd = New SqlClient.SqlCommand("INSERT INTO mastertable(mbno,mbdate,mbamount,mbcperson) VALUES(" & counter_bill_no & ",'" & server_date & "'," & billvalue & "," & countercode & ")", con, trans)
cmd.ExecuteNonQuery()

cmd = New SqlClient.SqlCommand("select mbid from mastertable WHERE mbno=" & counter_bill_no & " and mbcperson=" & countercode & " ", con, trans)
mb_id = cmd.ExecuteScalar

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

i = 0
For i = 0 To row_no - 1
selling_rate = DataGridView1.Item(2, i).Value
If selling_rate <> 0 Then
cmd = New SqlClient.SqlCommand("INSERT INTO detailtable(dbno,dbdate,dbicode,biquantity,dbamount) VALUES(" & mb_id & ",'" & server_date & "'," & DataGridView1.Item(4, i).Value & "," & DataGridView1.Item(1, i).Value & "," & DataGridView1.Item(3, i).Value & ")", con, trans)
cmd.ExecuteNonQuery()
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()

 

[cjard]

read this:
http://www.vbdotnetforums.com/misc.php?do=bbcode about CODE tags

and then read the PQ link in my signature..

 

[theAsocialApe]

hey there - thanks for the code.

I just glanced at it, and have to some stuff at work, but the first thing that pops out at me is that you're doing this in inline sql.

Stored procedures will let you test stuff more easily in the db as you do it, so you could easily put in some error checking and diagnostics. Also, you could do the whole thing, the write to the master table and the write to the detail table in a single logical unit of work, where you;ll be guaranteed that the whole thing will write or the whole thing won't. that will ensure the ri of your data.

try that, and i'll try to get back and look at the code in more depth at lunchtime.

-aApe

 

[vsc33]

hey there - thanks for the code.

I just glanced at it, and have to some stuff at work, but the first thing that pops out at me is that you're doing this in inline sql.

Stored procedures will let you test stuff more easily in the db as you do it, so you could easily put in some error checking and diagnostics. Also, you could do the whole thing, the write to the master table and the write to the detail table in a single logical unit of work, where you;ll be guaranteed that the whole thing will write or the whole thing won't. that will ensure the ri of your data.

try that, and i'll try to get back and look at the code in more depth at lunchtime.

-aApe

Thanks Ape,

A lot of other people also asked me to use Stored Procedures. But am a little confused and actually I dont know much about stored procedures. Can you get me some example of it. than how the whole process will work.

Regards,