ADO Transactions with SQLSERVER(??)

[amolt]

Hi szlamany,
You are correct to use action querys in simple updates.
But its my need to rollback the transactions too.
Also if I use simple updates the record which is updated and for which the local process is still running, have been seen by the client and thus lead to misbehaviour.

Regds,

[szlamany]

Then there is an answer to that issue as well.

Most people don't want to think set-based - it's so much easier to program in an iterative fashion.

But to truly utilize SQL you must always be set-based.

The simple set-based solution to your problem is to load a temp table with the new data first. MS SQL offer bulk insert options - both at command line and also part of the SQL Client - to get that data loaded in one shot.

Once you get the data into a temp table you can do a single UPDATE statement to process all rows.

We do this in literally hundreds of places - loading all kinds of data in these ways.

Expecting to use transactions to manage errors and then do rollbacks is not appropriate.

You should be creating temp tables.

Then you could delete the rows that aren't good to load.

Then you could do the UPDATE all in one step - syntax would be something like this.

Update MyTable Set Column1=TT.Column1, Column2=TT.Column2
   From #TempTable TT
   Left Join MyTable MT on MT...=TT...
   Where {some great condition statement to make sure the UPDATE will not fail}

Taking data into VB to be processed one row at a time in a multi-user environment is a poor choice when you have other set-based "single" shot ACTION QUERY abilities that fully respect set-based logic.

If you want assistance with doing this in a set-based fashion step back and tell us what you are trying to actually achieve. What the source of the data is - a couple of columns of sample data - and you will be amazed at how easy this can be done without looping through rows in VB and causing deadlock issues with your database