Batch execute Recordset 2

[gus121]

Hi I have been using the following code to update a recorset. Could you show me how I can execute all these records in a batch command outside the Loop. In which I can roll back later in the page if there is a problem with later queries.

Do While Not Products.EOF
'For each sub-categories the child products are displayed for that site added
Dim Product_Mask
Product_Mask = SubCat_Mask_Dif + cint(Products("Mask&quot.value)
Product_ID = Products("Product_ID&quot.value

strSql= "UPDATE tblProducts SET mask = '" & Product_Mask & "' WHERE product_ID = '" & Product_ID & "';"
'objConn.Execute(strsql)

Products.Movenext
Loop
End If

Angus

 

[MrGreed]

Just use a begintrans on the connection object

objConn.begintrans
Do While Not Products.EOF
'For each sub-categories the child products are displayed for that site added
Dim Product_Mask
Product_Mask = SubCat_Mask_Dif + cint(Products("Mask&quot.value)
Product_ID = Products("Product_ID&quot.value

strSql= "UPDATE tblProducts SET mask = '" & Product_Mask & "' WHERE product_ID = '" & Product_ID & "';"
'objConn.Execute(strsql)

Products.Movenext
Loop
End If

Later in your code if all is well use a objConn.committrans other wise were you error use objConn.rollback. Of course error trapping isn't the best in ASP, you'll have to use on error resume next or check a return value, I assume you already have this implemented.

MrGreed

http://www.pastame.com

"did you just say Minkey?, yes that's what I said."

 

[Funka]

in addition to MrGreed's suggestion, i can think of two other ways off the top of my head to do this:

the first involves a "disconnected recordset" --- learn more at

http://www.4guysfromrolla.com/webtech/080101-1.shtml

or from google.

the second might be to delay executing your SQL update string until after you have finished looping, i.e.,
Do While Not .EOF
strSql = strSql & "UPDATE tblProducts SET mask = '" & Product_Mask & "' WHERE product_ID = '" & Product_ID & "';"
' don't execute here, wait until after loop
.MoveNext
Loop
' now execute your massive SQL string
objConn.Execute(strSql)

if the number of loops will not be terribly large (i.e., less than maybe 10,000) this might work well for you.


good luck!
-f!

 

[gus121]

Thanks for both your help. I can now create better more scalable ASP applications! Improve Database data stability. thanks again for your help you have been most helpfull.

Angus

 

[gus121]

Hi just one more question. My web application updates a table. In the same script it queries the same table again. The result should be based on the changes I have made with the previous update query.
Using the method MCGreed highlighted. Would the objConn.committrans have to be commited before the result of the second query could be run to get the right result.
This obviously assumes that you already have executed the first update query with objConn.Execute(strsql).

Thanks for your help

Angus