VB.Net SqlException - Details of failing command's parameters

Hi,

Is there any way of getting details of the command's parameters that caused a SQLException?  Obviously the stack trace shows the failed command but I would like to know the Command's parameters as well.

In good old ADO, the Command was passed as a paramter in the ExecuteComplete event, so you could easily find not only the command text, but also all the paramter values in the failed SQL.

I've just taken over some code which is a mess (lots of random errors) and a generic means of getting details of failed SQL would be more than useful.

Many thanks

Mike

MikeHoffman66Asked:
Who is Participating?
 
Daniel WilsonConnect With a Mentor Commented:
>>I don't know if you ever used the old (pre .Net) ADO,

Oh, yes, I just don't usually admit how LONG I programmed in VB6!

>>I was hoping I could get simliar information from the SQLException.

I don't think so.

If the app you've inherited has that much code w/o exception handling, it NEEDS the exception handling.  I would start in the areas with the worst pain and work out from there.
0
 
Daniel WilsonCommented:
In your Catch block, is the SqlCommand in scope?  If so, you should be able to iterate its Parameters collection.
0
 
MikeHoffman66Author Commented:
Hi Daniel, thanks for the reply :-)

No, in a lot of cases there is no SQLCommand, the updates are done via adapter updates so there's no application-based SQLCommand.

I was hoping there might be a more generic way, in a SQL exception, that details other than just the failed command (i.e. the paramaters that made it fail) would be available.  Obviously in a lot of cases (e.g. integrity problems) the command itself is fine, it's just the data provided that is the problem.

Best wishes

Mike
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Daniel WilsonCommented:
A data adapter is actually using SqlCommands, if I'm not mistaken.  Can you tell whether, for example, it's an Insert or Update that failed?  The adapter's InsertCommand or UpdateCommand is a SqlCommand ... at whose parameter collection I think you should be able to get.

make sense?

If not, how about posting some code?
0
 
MikeHoffman66Author Commented:
Hi Daniel

It's not a single update that's the problem, it's a whole load of them.  At a guess, the software I've been landed with is about 150K lines of application code (that's without the generated form and dataset code etc), so there are inserts and updates aplenty and putting exception code around each update would take quite a while - understatement :-)

I don't know if you ever used the old (pre .Net) ADO, but it had an event (ExecuteComplete) which would fire even if the execution failed.  One of the parameters was the Command object, which you could parse to get the parameters from (even if it failed).  I was hoping I could get simliar information from the SQLException.  This would mean I could use a try..except around large amounts of code and get not just the stack trace, but also the parameters at the time of failure.

Regards

Mike

0
 
MikeHoffman66Author Commented:
Ok Daniel, thanks for your help.
0
 
MikeHoffman66Author Commented:
That was pretty much what I thought.  Thanks for your time :)
0
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.