[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

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

0
MikeHoffman66
Asked:
MikeHoffman66
  • 4
  • 3
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now