Run multiple SQL Queries through 1 asp command

I'm not sure how to properly phrase what I want to do.  I have multiple SQL inserts and updates that run through an asp page and because our servers and bandwidth suck, often times the page will time out before completing all of the sql statements.  I've been told that there is a way to wrap all of these sql statements into a "transaction" where they will all run or all fail.  Does anyone have more information about this?
Who is Participating?
sybeConnect With a Mentor Commented:
That's a lot of stuff for a single page.

Isn't it easier to transfer the logic to a stored procedure, because I think it's is easier to have the transaction functionality there. And you need to only once pass the data to the stored procedure. Also a stored procedure in the database works faster then pulling data into an ASP page and then sending them back to the database.

I have never coded a transaction in an ASP page, but a google search turns up pages like this:
If a page times out, it will still be executed. So that would not make any difference.
Multiple inserts and updates in a page can be very quick, although it depends on what is done exactly.

I suspect that the slowness is rather caused by posting lots of data, that is where bandwidth plays a role. There's no bandwidth involved in inserts and updates (unless it is done to a database outside the network of the webserver, but that seems unlikely).

rondreAuthor Commented:
The db is in house so that shouldn't be the problem.  I'm not sure what the problem is here as normally everything works great, but out of roughly 2000 times this page i wrote has been used, there have been roughly 10 times that we've found where only part of the code has executed.  Is it not true that creating multiple recordsets (as is done on this page) doesn't really slow things down?
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

> Is it not true that creating multiple recordsets (as is done on this page) doesn't really slow things down?

Well, yes. But for updates and inserts you don't need recordsets.

connection.Execute("INSERT INTO <table> (<fieldnames>) values (<values>)"), , 128
connection.Exectute("UPDATE <table> SET <fieldname> = <value>, <fieldname2> = <value2" WHERE <id> = <value>"), , 128

Not sure what you are doing, but if you pull a whole table from the database in order to insert or update one record, things will be quite slow.

rondreAuthor Commented:
I have about 8 insert statements and 10 update statements.  All of them are using the connection.execute method, however, i have other recordsets that are using select statements to pull data into vars for use in the update and insert statements.  

Since there are occasions where only part of the statements will execute, I'm assuming that from your post that this must mean that the server is actually going down where transmission is getting cut off somehow because there are definite times where only part of the code is getting executed.  In that case, would putting all of the statements into say 1 function/transaction/whatever it's called - would that still cause only part to get posted or would it do an all or nothing as I would want it to, and if so how does one package them up for use w/ asp?

Thanks for all your help.
rondreAuthor Commented:
Thanks Sybe, I don't have much experience with stored procedures, but in reviewing these links, I'm sure this is what I'm looking for,  Thanks for all your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.