Gettitng error as Procedure or function expects parameter which was not supplied

Question

I have created a Stored procedure to Insert my data into the table, but I am getting error as

Procedure or function 'AddUserDetails' expects parameter '@UserId', which was not supplied.

Here is my SP

CREATE PROCEDURE AddUserDetails 
@UserId nvarchar(55),
@UserPassword nvarchar(100),
@ConfirmPass nvarchar(100),
@Mobile int,
@Email nvarchar(100),
@BirthDate nvarchar(100)
AS  
BEGIN
    SET NOCOUNT ON;


     Insert into RegisterUser(UserId,UserPassword,ConfirmPass, Mobile, Email,BirthDate)Values (@UserId, @UserPassword, @ConfirmPass, @Mobile, @Email,@BirthDate)
END
GO

Also here is my C# code.

protected void btnSubmit_Click(object sender, EventArgs e)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@UserId", SqlDbType.NVarChar).Value = txtUserId.Text;
            cmd.Parameters.Add("@UserPassword", SqlDbType.NVarChar).Value = txtPassword.Text;
            cmd.Parameters.Add("@ConfirmPassword", SqlDbType.NVarChar).Value = txtConfirmPassword.Text;
            cmd.Parameters.Add("@Mobile", SqlDbType.Int).Value = txtMobile.Text;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = txtEmail.Text;
            cmd.Parameters.Add("@BirthDate", SqlDbType.NVarChar).Value = txtBirth.Text;
            cmd = new SqlCommand("AddUserDetails", con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            Response.Redirect("http://www.google.com");
            con.Close();
        }
        catch (Exception ex)
        {

            throw ex;
        }
    }

Kindly suggest what is the mistake here

score 3 · Accepted Answer · edited May 23 '17 at 12:05

Because you are re-create your command with

cmd = new SqlCommand("AddUserDetails", con);

line and you never add any parameter to that cmd. You try to add old one with created SqlCommand cmd = new SqlCommand(); line.

Delete this SqlCommand cmd = new SqlCommand(); line and move your;

SqlCommand cmd = new SqlCommand("AddUserDetails", con);
cmd.CommandType = CommandType.StoredProcedure;

top of your code. That's it. And you never done anything in your catch part. Just throwed new exception with throw ex; but this resets the stack trace. And consider to use using statement to dispose your connections and commands automatically instead of calling Close() or Dispose() methods manually.

    try
    {
        SqlCommand cmd = new SqlCommand("AddUserDetails", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@UserId", SqlDbType.NVarChar).Value = txtUserId.Text;
        cmd.Parameters.Add("@UserPassword", SqlDbType.NVarChar).Value = txtPassword.Text;
        cmd.Parameters.Add("@ConfirmPassword", SqlDbType.NVarChar).Value = txtConfirmPassword.Text;
        cmd.Parameters.Add("@Mobile", SqlDbType.Int).Value = Convert.ToInt32(txtMobile.Text);
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = txtEmail.Text;
        cmd.Parameters.Add("@BirthDate", SqlDbType.NVarChar).Value = txtBirth.Text;
        con.Open();
        cmd.ExecuteNonQuery();
        Response.Redirect("http://www.google.com");
        con.Close();
    }
    catch (Exception ex)
    {
        // 
    }

@nadeem Just declare it as `SqlCommand cmd = new SqlCommand("AddUserDetails", con)`. — Soner Gönül, May 24 '15 at 10:55
now getting error as `Failed to convert parameter value from a String to a Int32.` — Nad, May 24 '15 at 10:57
@nadeem Since your `Mobile` column is `int`, you can's pass it this `txtMobile.Text` directly. Try to parse it to `int` first like `Convert.ToInt32(txtMobile.Text)` or something. — Soner Gönül, May 24 '15 at 10:59
changed but getting error as `Value was either too large or too small for an Int32.` — Nad, May 24 '15 at 11:00
@nadeem Then you need to change your column type that fits with your values. Read: https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type — Soner Gönül, May 24 '15 at 11:02

Gettitng error as Procedure or function expects parameter which was not supplied

1 Answers1