How to check return value for executequery in c# in sql server 2005?

Question

I am trying to call a stored procedure in my asp.net site in c#. I am using a SqlCommand class and call ExecuteQuery(). The return value is -1?

The stored procedure works, i tested it in SQL Management Studio. How can i get a more detailed error description?

System.Data.SqlClient.SqlCommand has no ExecuteQuery method... — Petar Ivanov, Jun 30 '11 at 06:40
Take a look at http://stackoverflow.com/questions/6210027/c-calling-sql-server-stored-procedure-with-return-value/6210055#6210055 — Alex Aza, Jun 30 '11 at 06:38
Probably you mean [`SqlCommand.ExecuteNonQuery()`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx) — abatishchev, Jun 30 '11 at 07:18

score 1 · Answer 1 · answered Jun 30 '11 at 07:14

My first port of call would be to run SQL Server Profiler in SQL Server Management Studio so you can see exactly what SQL is being executed against the database. When you have this run the SQL in SQL Server Management Studio and you'll be able to determine if it is a SQL error or not. Post back with you findings.

Pankaj · Answer 2 · 2011-06-30T08:14:52.890

Your Stored procedure should be like below. See for Error_Message and Error_Number

Create Proc Testing
As

Set NoCount On
Set XACT_ABORT ON
Begin Try
    Begin Tran
        "Your Insert/Update Statement"
        Select '' as 'Message', 0 as 'Number'
    Commit Tran
End Try
Begin Catch
    Select Error_Message() as 'Message', Error_Number() as 'Number'
    Rollback Tran
End Catch

Your code should be like below

string str;
int number;
using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) { 
    con.Open(); 
    SqlCommand cmd = new SqlCommand(); 
    string expression = "Parameter value"; 
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.CommandText = "Your Stored Procedure"; 
    cmd.Parameters.Add("Your Parameter Name", 
                SqlDbType.VarChar).Value = expression;    
    cmd.Connection = con; 
    using (IDataReader dr = cmd.ExecuteReader()) 
    { 
        if (dr.Read()) 
        {
             str = dr["Message"].ToString();
             number = Convert.ToInt16(dr["Number"]);
        } 
    } 
}

In this way you can capture the Error Message/ Error Number from stored procedure if it returns any

How to check return value for executequery in c# in sql server 2005?

2 Answers2