How to bind two sql with commit in vb.net

[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.

[jmcilhinney]

The system is not ignoring the update at all. The problem is that your WHERE clause is specifying a condition that doesn't match any records, therefore no records get updated. Look at your code:

cmd = New SqlClient.SqlCommand("update table2 set colbl='" & col1 & "',colcl='" & t & "' where colbl =" & col1 & "", con, trans)

I think that it's safe to say that if you're setting the balance to col1 then it isn't col1 already. You need to specify the correct original value in your WHERE clause in order to identify the correct row(s) to update.

Having said that, there is absolutely no way that you should be setting the value of the column you're using to identify the row. That may be legitimate in certain rare circumstances but this is certainly not one of them. Your table should have a primary key column and it should be that column you use to identify the rows. You then know that that column value will NEVER change so you will ALWAYS refer to the same row with the same value.

On a different note, using string concatenation to build SQL statements is an abomination. NEVER, EVER do it if it can possibly be avoided, which it very much can in this case. Follow the Data Access link in my signature to see code examples of using parameters to insert values into SQL statements, which is the "proper" way to do it.

[vsc33]

Hi,

Thanks so much for taking so much interest in reading and replying to my query. Your thred on data access was a big help.

Well, as advised by you I have asked my team to start working with paramaterized queries and sooner we will update it.

In the mean time what is churning me that why the Rollback doesnt take place. My application runs three modules. Each runs two sql statement; one Insert and one Update.

Insert is running fine in all the three modules. Update is running fine in two modules.

But in one problamatic module, the sql insert command is generated but the consequent update field is not getting updated.

Its like that say if Rs. 100 is added to an account in say Table1 than Table2 which holds all the balances of the accounts in Table1 needs to be updated.

Because its not getting updated, it shows a wrong balance.

I have tested this commit part in my server by giving some wrong sql query and the rollback takes place. But while its live its giving no solution to us.

Can you please help with this commit part. Weather my process of commit is right. As such the transaction is very simple.

Thanks again in advance.

[jmcilhinney]

The transaction is only going to get rolled back if an exception occurs. I can only conclude that an exception is not occurring.

[vsc33]

Well, here is my present code. Do you think it would be wise if I shift to parameterized query. I have also give the field type below.

And yes what is string concatenation.

con.Open()
trans = con.BeginTransaction()
Try
cmd = New SqlClient.SqlCommand("INSERT INTO issuec(cen, cses, ctdate, cttime, caction, camt, cde, cdch, crt, crr, cda, uc, cty) " & _
"VALUES('" & cen & "','" & frm.txtcses.Text.Trim & "','" & s_date & "','" & s_time & "','" & temp & "','" & a11 & "','" & sdepo & "','" & b & "','" & type & "','" & reason & "','" & damage & "'," & ccde & ",'" & cs & "')", con, trans)
cmd.ExecuteNonQuery()

cmd = New SqlClient.SqlCommand("update master set ccb='" & newbalance & "', cact='" & t & "',cldate='" & s_date & "', cl='" & lc & "', cde=" & cnt & ", cty='" & cs & "' where cen=" & cen & "", con, trans)
cmd.ExecuteNonQuery()

trans.Commit()
Catch ex As Exception
trans.Rollback()
End Try
con.Close()

Field type:
Sql1:
------
cen: numeric
cses: int
ctdate: datetime
cttime: char
caction : char
camt: numeric
cde : numeric
cdch : numeric
crt : char
crr : varchar
cda : char
uc : int
cty : char

Sql2:
------
ccb : numeric
cact : char
cldate : datetime
cl : cahr
cde : int
cty : char
cen : numeric

Regards

[jmcilhinney]

Do you think it would be wise if I shift to parameterized query.

On a different note, using string concatenation to build SQL statements is an abomination. NEVER, EVER do it if it can possibly be avoided, which it very much can in this case.

What do you think I think?

[vsc33]

Well well I know very well what u think... ... and thanks so much for taking so much interest.

But if you can just tell me or better educate me the difference between String Concatenation and Paramaterized Queries, it would help me to convince others too in my team.

Regards.

[jmcilhinney]

String concatenation is harder to read, more error prone and less secure. It opens you up to SQL injection attack, which you can read about on MSDN or the Web at large. It also means that some data requires extra processing, like strings with single quotes in it. You also have to fiddle with formats for dates. All of this unpleasantness simply goes away when you use parameters.

[vsc33]

Well, my team is working with parameters. In the mean time I wanted to clear that in input I get from my users are just numeric inputs. The user doesnt have much of rights. They can just select, click on the output shown and what they input is just numerics from 0 to 9.

This way I can say that my application is not prone to sql injection. Its still wondering in my mind that why the exception is not happening. As for the where clause in my update statement, the same variable is used in the update statement also and there it it working ok.

There has to be some logic behind that.

Regards.