Database not getting updated


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Database not getting updated

  1. #1
    Join Date
    May 2008
    Posts
    3

    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.

  2. #2
    Join Date
    May 2004
    Location
    Duluth MN
    Posts
    353
    Just to clarify, you have your inserts (master and detail) wrapped in a transaction and you still have orphan detail records (detail records with no matching master). This occurs occasionally (1 in 500).

    Do the users have the ability to delete records? If so are you cascading deletes to remove the detail records also?

    ...joe

  3. #3
    Join Date
    May 2008
    Posts
    3
    Well, thanks for the reply...

    Yes you try understood the problem.

    But as you said that it happens 1 in 500...

    1. Why is it so?
    2. What preventions to take?
    3. Lastly how to solve it out as we just cannot afford a single miss also.

    As for deletion, the user doesnt have any option to delete.

    Regards and thanks once again.

  4. #4
    Join Date
    May 2004
    Location
    Duluth MN
    Posts
    353
    Obviously, if you've wrapped the inserts inside a transaction correctly it shouldn't be happening. Are you sure your trapping any insert error and rolling back the transaction if they occur? Can you create a log file to track the inserts? Without actually seeing an example of the code your using it would be hard to identify just where your problem might be. These type of issues can be prevented through code as well as correctly setting up relationships/constraints on your database tables. If your inserts are occuring without error, then something has to be deleting the master record after the fact.

    ...joe

  5. #5
    Join Date
    May 2008
    Posts
    3
    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()

  6. #6
    Join Date
    May 2004
    Location
    Duluth MN
    Posts
    353
    Quote Originally Posted by vsc33
    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.
    Since your retrieving the ID from the master table after the insert and before the insert of the detail records. It must have been inserted correctly. If it's missing at a later date, it was deleted somehow. Do you have cascading deletes setup on the tables? If so the orphan detail records should be deleted also.

    ...joe

  7. #7
    Join Date
    Dec 2004
    Posts
    163
    vsc33,

    Have you considered retrieving the database-generated identity value with "Select Scope_Identity()" instead of the select statement that you are currently using?

    Kerry Moorman

Similar Threads

  1. Access Database, using VB as front end
    By binnymann in forum Database
    Replies: 3
    Last Post: 04-13-2008, 02:23 AM
  2. Replies: 0
    Last Post: 08-23-2006, 01:21 AM
  3. Cool 3 Tier Java database solution
    By russ in forum Java
    Replies: 1
    Last Post: 06-05-2002, 10:07 AM
  4. Really Cool 3 tier Java database solution
    By Russ in forum Database
    Replies: 0
    Last Post: 06-04-2002, 01:57 AM
  5. Database Security General Discussion
    By Michael Tzoanos in forum Database
    Replies: 0
    Last Post: 04-12-2002, 12:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center