[2005] ADO.Net Error Handling

[Aspnot]

I have a SQL Server SP that takes 3 parameters. My InsertCommand sets 2 of the 3 parameters and the 3rd one gets "default". When I call SqlDataAdapter.Update(), I don't get an error generated in my code, but the insert fails. If I run the exact same line in Query Analyzer, it returns "Procedure 'spName' expects parameter '@Param3', which was not supplied.

How can I test for this error situation? My application should not just continue to process data and make the user think it worked, when it actually did not.

[techgnome]

Try
-- put your .Update here
Catch sqlEx as SQLException
messagebox.show sqlex.tostring
Catch ex as SystemException
messagebox.show ex.tostring
Finally
--optional stuff here
End Try

-tg

[Aspnot]

I already have that in place. Nothing is caught there.

[dminder]

I have had this happen before as well. I had to actually adjust the stored procedure to handle a return code and a return message. Then in the stored procedure I had it test each parameter and return a 1 if it failed and a 0 (at the end) if it succeeded. Actually I have that on all my stored procedures now. It has not added any measurable amount of processor time and some of my procedures take up to 30 parameters

The other work around is that set the parameter = null in the stored procedure. Then just add parameter to your command setup in your program but don't assign it a value or assign it a default value.

Good Luck,

D

[techgnome]

If it's not catching it, then there's something else wrong... because I *Know* it does catch.... what does your code look like?

-tg

[Aspnot]

If there is an error inside of the procedure (a field is in a select list that doesn't exist in the table), it will catch that. It will not catch the "Procedure 'spName' expects parameter '@Param3', which was not supplied".

[techgnome]

Then by all means.... you go right ahead.... clearly you know what you are doing.

-tg

[Aspnot]

What's with the attitude? I came here asking for help, not an attitude.