Get an error message from TSQL output clause with SqlDataReader

Question

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?

Community · Accepted Answer · 2017-05-23 12:14:27Z

2

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
}

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Jan 20, 2011 at 20:03

ralf.w.

1,6941 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dance2die · Accepted Answer · 2011-01-20 15:50:54Z

0

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

answered Jan 20, 2011 at 15:50

dance2die

37.2k39 gold badges137 silver badges199 bronze badges

2 Comments

m0sa Over a year ago

Can this be executed as SqlCommand.CommandText = "TRY ... CATCH .." and ExecuteReader()? Smells more like a stored procedure...

dance2die Over a year ago

No. what I meant was to implement using T-SQL and raiserror from sproc and you can get an error message from C# exception.

Collectives™ on Stack Overflow

Get an error message from TSQL output clause with SqlDataReader

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related