3

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?

m0sa
  • 10,712
  • 4
  • 44
  • 91

2 Answers2

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
}
Community
  • 1
  • 1
ralf.w.
  • 1,676
  • 1
  • 24
  • 34
0

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

dance2die
  • 35,807
  • 39
  • 131
  • 194
  • Can this be executed as SqlCommand.CommandText = "TRY ... CATCH .." and ExecuteReader()? Smells more like a stored procedure... – m0sa Jan 20 '11 at 16:04
  • No. what I meant was to implement using T-SQL and raiserror from sproc and you can get an error message from C# exception. – dance2die Jan 20 '11 at 16:59