No Update and no Exception

  • 12 years ago

    I have an application that runs on sql server with 5-6 client machine. I have two tables in my database. TableMaster and TableTransaction.

    Say a customer has deposited Rs. 500 to his account, this entry is recorded with his unique account number, date in TableTransaction.

    In the TableMaster, his account balance amount is updated.

    Say his opening balance was Rs. 200, he made a deposit of Rs. 500, so the TableMaster is updated by Rs. 700.

    First the TableTransaction sql inserts and than TableMaster sql updates and they are binded through a commit.
    This is my process of transaction

    'Connection Opens-----
    con = connect()
    con.Open()

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

    Try

    'Data is inserted into Table Transaction---------

    cmd = New SqlClient.SqlCommand("INSERT INTO TableTransaction........", con, trans)
    cmd.ExecuteNonQuery()

    'Data is updated in Table Master-----------

    cmd = New SqlClient.SqlCommand("update tablemaster set..........", con, trans)
    cmd.ExecuteNonQuery()

    'Data commited--------

    trans.Commit()

    Catch ex As Exception

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

    End Try

    con.Close()

    Problem:
    --------

    Say 1 out of 1000 entry, my TableMaster field is not getting updated. The balance of the particualar customer remains Rs. 200 only thus creating a dispute.

    Its not throwing any exception also thus stopping the rollback of the transaction. Is it possible that a row doesnt get updated and still doesnt throws any exception??? I am in a fix.

    I read about non-fatal errors which are not caught by sql, but they are caught through try/catch block in vb.net.

    The program is designed in a way that at one time only one person can access a customer's account, thus stopping any dispute.

    Am not able to diagonise where the error is taking place. Please help. I really want to know why this error is taking place.

    Thanks in advance.

  • 12 years ago

     [quote]The program is designed in a way that at one time only one person can access a customer's account, thus stopping any dispute.[/quote]

    How have you determined that this is in fact the case? ...since this may well have an impact on what you've discovered (ie a dispute).

    Which db locking 'model' are you using .... optomistic? pessimistic? There's a nice article on the impact of overlooking this here:

    http://aspnet.4guysfromrolla.com/articles/052108-1.aspx 

    Joe 

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.” - Rick Osborne