p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   VB How-To (http://p2p.wrox.com/forumdisplay.php?f=78)
-   -   Database not getting updated (http://p2p.wrox.com/showthread.php?t=67994)

vsc33 May 9th, 2008 12:52 AM

Database not getting updated
 
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.

Raghavendra_Mudugal May 13th, 2008 11:33 AM

hello,

that is weird...

even still using commit... u have this problem... then it's unbelievable. can you post a sample script of your stored procedure on how you have approaching this..?

in general using commmit wont do a trick, it also depends on how the statement is stated.

look out points:
- how the transaction is began?
> are you inserting the data to tran table only after inserted in the master table?
- if anything goes wrong/internal script warning in inserting into tran table then are you rolling back the master data inserting statement?
- are you using error trapping system variable to see the statement execution result?
- any PK-FK warning?

I have so many such application where the concept of master-tran is used and i never faced such problems in real time.

So post some of the sample of your code here.. so it will help to see and understand your current process.

Hope this helps.

With Regards,
Raghavendra Mudugal

vsc33 May 15th, 2008 07:11 AM

Thanks for the reply. Here is the code. Waiting for your suggestion.

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,dbamoun t) 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()


gbianchi May 15th, 2008 07:39 AM

Hi there.. how do you make the counter_bill_no?? maybe you a concurrency problem??

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

vsc33 May 15th, 2008 10:30 AM

Say if we have 5 counters, counter_bill_no is seperate for each counters.

So we retrieve the maximum counter_bill_no for that particular counter through a query and than add it with 1.

Hope i am clear.

Thanks.





All times are GMT -4. The time now is 11:42 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.