My application runs on a network of 15 to 16 clients on a SQL Server 2000. Some of the modules perform transaction, i.e. they run more than one sql. I have tied the different sql statemels under the following procedure
con.Open()
trans = con.BeginTransaction()
Try
‘First Sql cmd = New SqlClient.SqlCommand("INSERT INTO table1(col1, cl2, col3, col4) "VALUES('" & col1 & "','" & col2 & "','" & col3 & "','" & col4 & "')", con, trans) cmd.ExecuteNonQuery() ‘Second Sql
cmd = New SqlClient.SqlCommand("update table2 set colbl='" & col1 & "',colcl='" & t & "' where colbl =" & col1 & "", con, trans)
cmd.executeNonQuery() trans.Commit() Catch ex As Exception trans.Rollback() End Try
con.Close()
Out of 700-800 inserts and updates, the system ignores the update statement. The value is inseted through the first statement, but it is not updated. So it shows a wrong balance. I am not able to diagonise the actual cause. The network is very good.
My question is :
1. Even if the network fails, why does the rollback doesn’t happen.2. Is my above code a right way for a commit procedure. Please help. Thanks in advance.