CODEWITHSUNDEEP
t-sqlado.netsqldatareadersqlcommand
m0sa
m0sa

Reputation: 10940

Get an error message from TSQL output clause with SqlDataReader

I have the following SQL

INSERT INTO [dbo].[table1] ([val1], [val2]) 
OUTPUT INSERTED.* 
VALUES (@val1, @val2)

I use SqlCommand.ExecuteReader() to get a SqlDataReader that reads the outputed row. It all works fine when the parameters are correct.

The problems begin when the table has a constraint not met by the parameters, for example:

ALTER TABLE [dbo].[table1]
ADD CONSTRAINT [chk_table1_distinct_values] 
CHECK (val1 <> val2)

If I execute the command with the the same value for val1 and val2 the reader fails silently. All I can do is to check the SqlDataReader.HasRows property, that returns false.

So my question is how can I get a more specific error from the returned SqlDataReader?

Upvotes: 3

Views: 1464

Answers (2)

ralf.w.
ralf.w.

Reputation: 1696

Have you inspected the "normal" output like in Capture Stored Procedure print output in .NET

copied from AdaTheDev:

myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    // Do something with e.Message
}

Upvotes: 2

dance2die
dance2die

Reputation: 36905

add begin try..catch and within the catch section, raiserror.

Upvotes: 0

Related Questions