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
– Petar Ivanov, Commented Jun 30, 2011 at 6:40

openshac · Accepted Answer · 2011-06-30 07:14:16Z

1

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.

answered Jun 30, 2011 at 7:14

openshac

5,1936 gold badges48 silver badges83 bronze badges

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

Comments

Pankaj · Accepted Answer · 2011-06-30 08:14:52Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related