How to bind two sql with commit

[vsc33]

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.

 

[cjard]

Its not a great way to do data access, period.. Take a read of the PQ link in my signature, then a read of DW2, Creating a Simple Data App, Saving Data, and lastly, Saving Data in a Transaction