Passing parameter to stored procedure in C#

Question

I have a stored procedure that returns a variable @result set to 1 or 0 (datatype bit). I am accessing it in my C# with the following code. Its throwing an error saying too many parameters.

protected void btnRegister_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
    con.Open();

    SqlCommand Cmd = new SqlCommand("usp_CheckEmailMobile", con);
    Cmd.CommandType = CommandType.StoredProcedure;
    Cmd.CommandText = "Registration";
    Cmd.Parameters.AddWithValue("@Name", txtName.Text);
    Cmd.Parameters.AddWithValue("@Email", txtEmailAddress.Text);
    Cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
    Cmd.Parameters.AddWithValue("@CountryCode", ddlCountryCode.Text);
    Cmd.Parameters.AddWithValue("@Mobile", txtMobileNumber.Text);
    //Cmd.Parameters.Add("@Result", DbType.Boolean);
    SqlParameter sqlParam = new SqlParameter("@Result", DbType.Boolean);
    //sqlParam.ParameterName = "@Result";
    //sqlParam.DbType = DbType.Boolean;
    sqlParam.Direction = ParameterDirection.Output;
    Cmd.Parameters.Add(sqlParam);
    Cmd.ExecuteNonQuery();
    con.Close();
    Response.Write(Cmd.Parameters["@Result"].Value); 
}

the stored procedure: (this I think is fine...) And please correct my CS code...

ALTER PROCEDURE [dbo].[usp_CheckEmailMobile](
    @Name VARCHAR(50), 
    @Email NVARCHAR(50), 
    @Password NVARCHAR(50), 
    @CountryCode INT, 
    @Mobile VARCHAR(50), 
    @Result BIT OUTPUT)
    AS 
BEGIN 

IF EXISTS (SELECT COUNT (*) FROM AUser WHERE  [Email] = @Email AND [Mobile] = @Mobile) 
Begin 
    Set @Result=0; --Email &/or Mobile does not exist in database
End
ELSE
Begin
    --Insert the record & register the user 
    INSERT INTO [AUser] ([Name], [Email], [Password], [CountryCode], [Mobile]) VALUES (@Name, @Email, @Password, @CountryCode, @Mobile)  
    Set @Result=1;
End
END

Is it an actual output parameter or a return value in your stored procedure? Please post an abbreviated definition of your stored procedure as well. — J. Steen, Mar 22 '13 at 12:05
Well which is the CommandText ? `usp_CheckEmailMobile` or `Registration`, the current one is looking at `Registration` which is not i thing you meant — V4Vendetta, Mar 22 '13 at 12:08
possible duplicate of [Returning boolean value from a stored procedure using bit](http://stackoverflow.com/q/15568251/50447) — Rowland Shaw, Mar 22 '13 at 12:09
@Rowland Shaw... this question comes after the answers to the post in the link... those have been asked by me! — who-aditya-nawandar, Mar 22 '13 at 12:11
@AdityaNawandar ...and the accepted answer there doesn't match the code you're trying to call it with - hence your error; had you followed the solution you accepted as working, you wouldn't have seen the error you're seeing here. — Rowland Shaw, Mar 22 '13 at 12:16

Mohammad Arshad Alam · Accepted Answer · 2013-03-22T13:46:00.147

26

you can try this code :

bool result=false;
SqlCommand scCommand = new SqlCommand("usp_CheckEmailMobile", sqlCon);
scCommand.CommandType = CommandType.StoredProcedure;
scCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtName.Text;
scCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = txtEmailAddress.Text;
scCommand.Parameters.Add("@Password ", SqlDbType.NVarChar, 50).Value = txtPassword.Text;
scCommand.Parameters.Add("@CountryCode", SqlDbType.VarChar.50).Value =ddlCountryCode.SelectedText;
scCommand.Parameters.Add("@Mobile", SqlDbType.NVarChar, 50).Value = txtMobileNumber.Text;
scCommand.Parameters.Add("@Result ", SqlDbType.Bit).Direction = ParameterDirection.Output;
try
{
    if (scCommand.Connection.State == ConnectionState.Closed)
    {
        scCommand.Connection.Open();
    }
    scCommand.ExecuteNonQuery();
    result = Convert.ToBoolean(scCommand.Parameters["@Result"].Value);


}
catch (Exception)
{

}
finally
{                
    scCommand.Connection.Close();
    Response.Write(result); 
}

edited Mar 22 '13 at 13:46

answered Mar 22 '13 at 12:18

Mohammad Arshad Alam

9,694
6
38
61

i have just called your sp along with parameter, check your database and storeprocedure – Mohammad Arshad Alam Mar 22 '13 at 12:47
means?? btw, still nothing.. I hope you read the code for my sp... it returns a 1 or 0... – who-aditya-nawandar Mar 22 '13 at 12:50
have you tested your sp in sql, as i have said i am only calling it from code – Mohammad Arshad Alam Mar 22 '13 at 12:52
yes I have tested, and the CoountryCode is varchar 50 not int... And (why) your code isnt working... – who-aditya-nawandar Mar 22 '13 at 13:22
The code for my sp is written above in my post.. how do I check it... ? – who-aditya-nawandar Mar 22 '13 at 13:56
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26732/discussion-between-arshad-and-aditya-nawandar) – Mohammad Arshad Alam Mar 22 '13 at 13:59

score 4 · Answer 2 · answered Mar 22 '13 at 12:13

4

Why do you set:

Cmd.CommandText = "Registration";

this will replace your stored procedure name, so it won't call the stored procedure you indicated in:

SqlCommand Cmd = new SqlCommand("usp_CheckEmailMobile", con);

It can be useful to use a SQL profiler to debug that the SQL going "over the wire" is as expected.

answered Mar 22 '13 at 12:13

Rowland Shaw

37,700
14
97
166

i removed the Cmd.CommandText = "Registration"; now it throws "error converting nvarchar to int" – who-aditya-nawandar Mar 22 '13 at 12:36

Passing parameter to stored procedure in C#

2 Answers2

Linked