CODEWITHSUNDEEP
c#
chert chertopert
chert chertopert

Reputation: 197

Why do I get an error when calling a stored procedure from C#?

I'm new to C# and I have a stored procedure in SQL Server:

DECLARE @return_value int

EXEC    @return_value = [dbo].[sp_getUserInfo]
        @TelNo = N'2537743007'

SELECT  'Return Value' = @return_value

GO

and try call that with this code:

using (SqlConnection con = new SqlConnection("Data Source=SERVICE;Initial Catalog=InvokeADSL;Integrated Security=True"))
{
    using (SqlCommand cmd = new SqlCommand("sp_getUserInfo", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        //cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
        cmd.Parameters.Add("@TelNo", SqlDbType.NVarChar).Value = "253774300";

        con.Open();

        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine(reader["return_value"].ToString());
        }
    }
}

but I get an error:

System.IndexOutOfRangeException: return_value
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at ConsoleApplication1.Program.Main(String[] args)

How can I solve that? Thanks.

This is my stored procedure in SQL Server: link to code

Upvotes: 2

Views: 1337

Answers (2)

Yashar Aliabbasi
Yashar Aliabbasi

Reputation: 2719

If your stored procedure just returns one value then use var res = cmd.ExecuteScalar(); instead of reader = cmd.ExecuteReader();.
But if your stored procedure return table then use SqlDataAdapter with your current command like this SqlDataAdapter DA = new SqlDataAdapter(cmd); and then DA.Fill(dt);

Upvotes: 0

marc_s
marc_s

Reputation: 754598

Well, from the code you're showing as to how to call this stored procedure in T-SQL, it seems that the value is being returned from the stored procedure via a RETURN statement - not as a result set (SELECT ....).

Therefore, you need to read the return value of the stored procedure - not a result set using ExecuteReader():

using (SqlCommand cmd = new SqlCommand("sp_getUserInfo", con))
{
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@TelNo", SqlDbType.NVarChar).Value = "253774300";

    con.Open();

    // get the value from the RETURN statement in the stored procedure
    int returnValue = cmd.ExecuteNonQuery();
    Console.WriteLine("Return value: {0}", returnValue); 
}

Update: after consulting the stored procedure code, the result is in fact being returned by a SELECT (as a result set) - so you do need to use ExecuteReader - but that column just doesn't have a name, you so cannot access it using :

while (reader.Read())
{
    Console.WriteLine(reader["return_value"].ToString());
}

but you need to instead use the numerical index:

while (reader.Read())
{
    Console.WriteLine(reader[0].ToString());
}

Upvotes: 1

Related Questions