DBLib to AD.Net (SQLClient) Issue

  • Thread starter Thread starter BuddyWork
  • Start date Start date
B

BuddyWork

Hello,

When using multiple queries in DBLib, DBLib would carry on
if the queries failed where ADO.Net stop all the queries.

For example

declare @THStatus char(1)
select @THStatus = '0'
exec Deal_Insert 'ZD4222', 0, @THStatus OUTPUT, 0
select Status = @THStatus

The above query throws a SQL Error in Deal_Insert Stored
procedure and when using DBLib you get the result of the
last query 'select Status = @THStatus' but in C# you don't

The following line of code throws an SQL exception.
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

Error Stack:
Class 16
Linenumber 94
Message 'Could not fetch box record -1'
Number 99999
Procedure: Deal_Part_Insert
Source .Net SqlClient Data Provider
Server: ""
State 1

How can I archive the same as DBLib?

Thanks
 
BuddyWork said:
Hello,

When using multiple queries in DBLib, DBLib would carry on
if the queries failed where ADO.Net stop all the queries.

For example

declare @THStatus char(1)
select @THStatus = '0'
exec Deal_Insert 'ZD4222', 0, @THStatus OUTPUT, 0
select Status = @THStatus

The above query throws a SQL Error in Deal_Insert Stored
procedure and when using DBLib you get the result of the
last query 'select Status = @THStatus' but in C# you don't
Click to expand...

ADO.NET cannot stop the execution inside the procedure.
It continues as before. You are just not seeing the result.

I suspect that you can't get both a resultset and a error from a Sql Server
stored procedure in ADO.NET.
The error message comes before the resultset, and when the SqlClient gets
the message it throws an exception.

David
 
Is there no way to prevent this from happening?
-----Original Message-----



ADO.NET cannot stop the execution inside the procedure.
It continues as before. You are just not seeing the result.

I suspect that you can't get both a resultset and a error from a Sql Server
stored procedure in ADO.NET.
The error message comes before the resultset, and when the SqlClient gets
the message it throws an exception.

David


.
Click to expand...
 
Yes, make sure the SP exception severity is set to 10 or less (as I
discussed in an earlier thread).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks for your help Bill, I now have an issue with it.

Now that I've changed the severity to 10, from ADO.Net I
cannot tell if the warning message was raised by the
Raiserror function. Is there away I can get this
information when severity is 10.

Thanks,
 
Do you have an InfoMessage Event handler setup?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top