473,320 Members | 1,722 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help me understand try-catch

Hi all,

I'm trying to understand the best way to use a try catch with a SQL
transaction. I have a number of sql statements that need to be run, such
as:

'open sqlConnection, get command object, etc.
myTrans.BeginTransaction()
'do a delete
'do a update
'do an insert
myTrans.Commit()
'close connection/dispose of objects

However, I want to be able to roll it back so I do

myTrans.BeginTransaction()
Try
'do a delete
'do a update
'do an insert
catch ex as exception
myTrans.RollBack()
throw new exception("ERROR!",ex)
finally
'close connection/dispose of objects
end try

BUT, what I want to be able to do is have multiple TRYs... one for the
delete, one for the update, one for the insert, (so I can customize the
thrown error message), then within the CATCH for each of those trys, do a
ROLLBACK. What I don't understand though, is that the FINALLY clause of the
TRY/CATCH is ALWAYS executed, so even if no error is encountered, my
sqlconnection would be closed in the first TRY. How is this supposed to be
handled? Should I close the connection/dispose of objects from within the
CATCH in every TRY, or will this be closed/disposed anyway because of the
thrown error?? Does that make sense?

Thanks a bunch,

D

Nov 15 '05 #1
4 3114
If you are calling three distinct types why not create three function one
for Update, Insert, Delete

Friend Function UpdateSQLStuff() as MyReturnResult
'do some sql stuff
End Function
Friend Function InsertSQLStuff() as MyReturnResult
'do some sql stuff
End Function
Friend Function DeleteSQLStuff() as MyReturnResult
'do some sql stuff
End Function

Or - You could add a paramater to your method/function

friend function DoSomeSQLStuff(byval SQLAction as String) as string
Try
'do some stuff
Catch ex as exception
Select Case SQLAction
Case Is = "Upate"
'handle update error
Case Is = "Delete"
'handle delete error
Case Is = "Insert"
'handle insert error
End Select
end try

In any sense, I wouldn't close your database connection without first
finishing your processing, if you know you are going to do an update, insert
and delete in every transaction then close it at the end, otherwise try to
seperate the logic in some way. Select Case? Your choice.
Hope that makes sense.
Jared

"Big D" <a@a.com> wrote in message
news:#9**************@TK2MSFTNGP09.phx.gbl...
Hi all,

I'm trying to understand the best way to use a try catch with a SQL
transaction. I have a number of sql statements that need to be run, such
as:

'open sqlConnection, get command object, etc.
myTrans.BeginTransaction()
'do a delete
'do a update
'do an insert
myTrans.Commit()
'close connection/dispose of objects

However, I want to be able to roll it back so I do

myTrans.BeginTransaction()
Try
'do a delete
'do a update
'do an insert
catch ex as exception
myTrans.RollBack()
throw new exception("ERROR!",ex)
finally
'close connection/dispose of objects
end try

BUT, what I want to be able to do is have multiple TRYs... one for the
delete, one for the update, one for the insert, (so I can customize the
thrown error message), then within the CATCH for each of those trys, do a
ROLLBACK. What I don't understand though, is that the FINALLY clause of the TRY/CATCH is ALWAYS executed, so even if no error is encountered, my
sqlconnection would be closed in the first TRY. How is this supposed to be handled? Should I close the connection/dispose of objects from within the
CATCH in every TRY, or will this be closed/disposed anyway because of the
thrown error?? Does that make sense?

Thanks a bunch,

D

Nov 15 '05 #2
D,

If you are using SQL server then you could create named transactions. This
allow you to basically setup multiple roll back points. For instance in
your example you could roll back to the insert if the insert fails but
maintain the delete. The BOL has more detail.

Dan
Nov 15 '05 #3
"Big D" <a@a.com> wrote:
[...]
what I want to be able to do is have multiple TRYs...
one for the delete, one for the update, one for the
insert, (so I can customize the thrown error message),


You can customise your message even in a single try block.

try
{
strMsg = "error updating";
// ... update ...
strMsg = "error inserting";
// ... insert ...
}
catch
{
// display strMsg
}

P.

--
www.CL4.org
Nov 15 '05 #4
In addition to the conventional technique of nesting Try blocks, if you're
using VB.Net you can make use of the righteously cool 'When' feature in the
Catch block:

Create a state variable called, say Step. Increment Step after each
statement in the Try block, along the lines of the following:

try
step = 0
open the connection
step += 1
run the update
end try

and so on

Then write catch blocks like:

catch (ex as SqlException) When step = 1

catch (ex as Exception) When step = 1

catch (ex as SqlException) When step = 2

catch (ex as Exception) When step = 2

etc.

Between nested Try blocks, the When technique in VB.Net, and your own
understanding of what exception are likely to occur, I'd be surprised if you
couldn't figure out a sequence of steps that would handle the situation.

Regards,
Tom Dacon
Dacon Software Consulting

"Big D" <a@a.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Hi all,

I'm trying to understand the best way to use a try catch with a SQL
transaction. I have a number of sql statements that need to be run, such
as:

'open sqlConnection, get command object, etc.
myTrans.BeginTransaction()
'do a delete
'do a update
'do an insert
myTrans.Commit()
'close connection/dispose of objects

However, I want to be able to roll it back so I do

myTrans.BeginTransaction()
Try
'do a delete
'do a update
'do an insert
catch ex as exception
myTrans.RollBack()
throw new exception("ERROR!",ex)
finally
'close connection/dispose of objects
end try

BUT, what I want to be able to do is have multiple TRYs... one for the
delete, one for the update, one for the insert, (so I can customize the
thrown error message), then within the CATCH for each of those trys, do a
ROLLBACK. What I don't understand though, is that the FINALLY clause of the TRY/CATCH is ALWAYS executed, so even if no error is encountered, my
sqlconnection would be closed in the first TRY. How is this supposed to be handled? Should I close the connection/dispose of objects from within the
CATCH in every TRY, or will this be closed/disposed anyway because of the
thrown error?? Does that make sense?

Thanks a bunch,

D

Nov 15 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

31
by: da Vinci | last post by:
OK, this has got to be a simple one and yet I cannot find the answer in my textbook. How can I get a simple pause after an output line, that simply waits for any key to be pressed to move on? ...
1
by: rami | last post by:
I have some code which does following thing template<class X, unsigned ID = 0> struct SomeStruct { template<class X> static SomeStruct<X, ID + 1>& SomeFunc(); ... ... ... ...
9
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with...
9
by: Daz | last post by:
Hello hello! I'm trying to finish off putting my design into HTML and I've come across a problem that I can't get my head around. I've got divs floating in two columns, but I'm having problems...
3
by: Jesper Denmark | last post by:
Within the following construction switch (expression) { int i; i = GetArgs() //return 2 case constant-expression:
23
by: Jason | last post by:
Hi, I was wondering if any could point me to an example or give me ideas on how to dynamically create a form based on a database table? So, I would have a table designed to tell my application...
1
by: Rahul | last post by:
Hi Everybody I have some problem in my script. please help me. This is script file. I have one *.inq file. I want run this script in XML files. But this script errors shows . If u want i am...
9
by: TF | last post by:
Hello all, I made a ASP.NET 2.0 site that shows possible "recipes" for paint colors stored in an access dbase. Basically, 1000 colors are stored with specific RGB values in separate columns. A...
6
by: CD1 | last post by:
#include <string> This code won't compile. There is no variable called "foo2". :)
7
by: sara | last post by:
I have a friend doing some pro-bono work for a non-profit that does job training for distressed kids under DCSS care. He asked me for code to do the following (he's using A2003). I can't find...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.