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.BeginTransa ction()
Try
'First Sql
cmd = New SqlClient.SqlCo mmand("INSERT INTO table1(col1, cl2, col3, col4) "VALUES('" & col1 & "','" & col2 & "','" & col3 & "','" & col4 & "')", con, trans)
cmd.ExecuteNonQ uery()
'Second Sql
cmd = New SqlClient.SqlCo mmand("update table2 set colbl='" & col1 & "',colcl='" & t & "' where colbl =" & col1 & "", con, trans)
cmd.ExecuteNonQ uery()
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.
con.Open()
trans = con.BeginTransa ction()
Try
'First Sql
cmd = New SqlClient.SqlCo mmand("INSERT INTO table1(col1, cl2, col3, col4) "VALUES('" & col1 & "','" & col2 & "','" & col3 & "','" & col4 & "')", con, trans)
cmd.ExecuteNonQ uery()
'Second Sql
cmd = New SqlClient.SqlCo mmand("update table2 set colbl='" & col1 & "',colcl='" & t & "' where colbl =" & col1 & "", con, trans)
cmd.ExecuteNonQ uery()
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.