MikeHoffman66
asked on
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
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
Daniel Wilson
In your Catch block, is the SqlCommand in scope? If so, you should be able to iterate its Parameters collection.
ASKER
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
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
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?
make sense?
If not, how about posting some code?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok Daniel, thanks for your help.
ASKER
That was pretty much what I thought. Thanks for your time :)