C# Sql Data Reader error

Question

I have a web form with 11 text boxes and 1 radio button list (Containing two LI's).

txtCustID - Required (Generated by getting the max CustID number from database and incrementing value)

txtFirstname - Requried
txtSurname - Required
rdbGender -> Male, Female - Required
txtAge - Required
txtAddress1 - Requried
txtAddress2 - Not required
txtCity - Required
txtPhone - Required
txtMobile - Not required
txtEmail1 - Required
txtEmail2 - Required (Used to confirm Email1, not passed to DB)

There's a button, btnAdd which passes the text values to a stored procedure "AddRecord" in the database.

It is returning an error "Procedure or function 'AddRecord' expects parameter '@Surname', which was not supplied."

I can't find the issue, as @Surname is supplied?

Here's the btnClick event code:

protected void btnAdd_Click(object sender, EventArgs e)
{
        SqlCommand command = new SqlCommand();

        command.Connection = conn;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "AddRecord";
        command.Connection.Open();

        SqlParameter pCustID = new SqlParameter();
        pCustID.ParameterName = "@CustID";
        pCustID.SqlDbType = SqlDbType.Int;
        pCustID.Direction = ParameterDirection.Input;
        pCustID.Value = txtCustID.Text;
        command.Parameters.Add(pCustID);

        SqlParameter pFirst = new SqlParameter();
        pFirst.ParameterName = "@First";
        pFirst.SqlDbType = SqlDbType.VarChar;
        pFirst.Direction = ParameterDirection.Input;
        pFirst.Value = txtFirstName.Text;
        command.Parameters.Add(pFirst);

        SqlParameter pSurn = new SqlParameter();
        pSurn.ParameterName = "@Surname";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtSurname.Text;
        command.Parameters.Add(pSurn);

        SqlParameter pGen = new SqlParameter();
        pSurn.ParameterName = "@Gender";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = rblGender.SelectedValue;
        command.Parameters.Add(pGen);

        SqlParameter pAge = new SqlParameter();
        pSurn.ParameterName = "@Surname";
        pSurn.SqlDbType = SqlDbType.Int;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtAge.Text;
        command.Parameters.Add(pAge);

        SqlParameter pAdd1 = new SqlParameter();
        pSurn.ParameterName = "@Address1";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtAddress1.Text;
        command.Parameters.Add(pAdd1);

        SqlParameter pAdd2 = new SqlParameter();
        pSurn.ParameterName = "@Address2";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtAddress2.Text;
        command.Parameters.Add(pAdd2);

        SqlParameter pCity = new SqlParameter();
        pSurn.ParameterName = "@City";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtCity.Text;
        command.Parameters.Add(pCity);

        SqlParameter pPhone = new SqlParameter();
        pSurn.ParameterName = "@Phone";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtPhone.Text;
        command.Parameters.Add(pPhone);

        SqlParameter pMobile = new SqlParameter();
        pSurn.ParameterName = "@Mobile";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtMobile.Text;
        command.Parameters.Add(pMobile);

        SqlParameter pEmail = new SqlParameter();
        pSurn.ParameterName = "@Email";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtEmail1.Text;
        command.Parameters.Add(pEmail);

        SqlDataReader reader = command.ExecuteReader();

        reader.Dispose();
        command.Dispose();
        conn.Dispose();
    }
}

Here's the stored procedure code:

CREATE PROCEDURE AddRecord
    @CustID INT,
    @First VARCHAR,
    @Surname VARCHAR,
    @Gender VARCHAR,
    @Age INT, 
    @Address1 VARCHAR,
    @Address2 VARCHAR,
    @City VARCHAR,
    @Phone VARCHAR,
    @Mobile VARCHAR,
    @Email VARCHAR
AS
    INSERT INTO Customer
    VALUES
      (@CustID, @First, @Surname, @Gender, @Age, @Address1,  @Address2,
       @City, @Phone, @Mobile, @Email);

Answer 1

try this

protected void btnAdd_Click(object sender, EventArgs e)
    {
        SqlCommand command = new SqlCommand();

        command.Connection = conn;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "AddRecord";
        command.Connection.Open();

        SqlParameter pCustID = new SqlParameter();
        pCustID.ParameterName = "@CustID";
        pCustID.SqlDbType = SqlDbType.Int;
        pCustID.Direction = ParameterDirection.Input;
        pCustID.Value = txtCustID.Text;
        command.Parameters.Add(pCustID);

        SqlParameter pFirst = new SqlParameter();
        pFirst.ParameterName = "@First";
        pFirst.SqlDbType = SqlDbType.VarChar;
        pFirst.Direction = ParameterDirection.Input;
        pFirst.Value = txtFirstName.Text;
        command.Parameters.Add(pFirst);

        SqlParameter pSurn = new SqlParameter();
        pSurn.ParameterName = "@Surname";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtSurname.Text;
        command.Parameters.Add(pSurn);

        SqlParameter pGen = new SqlParameter();
        pSurn.ParameterName = "@Gender";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = rblGender.SelectedValue;
        command.Parameters.Add(pGen);

        SqlParameter pAge = new SqlParameter();
        pSurn.ParameterName = "@Age";
        pSurn.SqlDbType = SqlDbType.Int;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtAge.Text;
        command.Parameters.Add(pAge);

        SqlParameter pAdd1 = new SqlParameter();
        pSurn.ParameterName = "@Address1";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtAddress1.Text;
        command.Parameters.Add(pAdd1);

        SqlParameter pAdd2 = new SqlParameter();
        pSurn.ParameterName = "@Address2";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtAddress2.Text;
        command.Parameters.Add(pAdd2);

        SqlParameter pCity = new SqlParameter();
        pSurn.ParameterName = "@City";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtCity.Text;
        command.Parameters.Add(pCity);

        SqlParameter pPhone = new SqlParameter();
        pSurn.ParameterName = "@Phone";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtPhone.Text;
        command.Parameters.Add(pPhone);

        SqlParameter pMobile = new SqlParameter();
        pSurn.ParameterName = "@Mobile";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtMobile.Text;
        command.Parameters.Add(pMobile);

        SqlParameter pEmail = new SqlParameter();
        pSurn.ParameterName = "@Email";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtEmail1.Text;
        command.Parameters.Add(pEmail);

        SqlDataReader reader = command.ExecuteReader();

        reader.Dispose();
        command.Dispose();
        conn.Dispose();
    }
}

in your age field you are using @Surname .change that and try again

Answer 2

There are a mass amounts of items here that are suspect.

1. Never do the auto incrementing yourself. All databases that I am aware of have the ability to do this for you.
2. Change your variable name for each parameter. Looks as though you got to pSurn and did some copying and pasting. This code just really needs clean up work more than anything.

This is really not much of an answer but I hope this moves you in the right direction.

Answer 3

You have referenced same varible after initialization of pSurn to rest of all Parameters and also give parameter name to '@Surname' for Age parameter...
Try below

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        SqlCommand command = new SqlCommand();

        command.Connection = conn;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "AddRecord";
        command.Connection.Open();

        SqlParameter pCustID = new SqlParameter();
        pCustID.ParameterName = "@CustID";
        pCustID.SqlDbType = SqlDbType.Int;
        pCustID.Direction = ParameterDirection.Input;
        pCustID.Value = txtCustID.Text;
        command.Parameters.Add(pCustID);

        SqlParameter pFirst = new SqlParameter();
        pFirst.ParameterName = "@First";
        pFirst.SqlDbType = SqlDbType.VarChar;
        pFirst.Direction = ParameterDirection.Input;
        pFirst.Value = txtFirstName.Text;
        command.Parameters.Add(pFirst);

        SqlParameter pSurn = new SqlParameter();
        pSurn.ParameterName = "@Surname";
        pSurn.SqlDbType = SqlDbType.VarChar;
        pSurn.Direction = ParameterDirection.Input;
        pSurn.Value = txtSurname.Text;
        command.Parameters.Add(pSurn);

        SqlParameter pGen = new SqlParameter();
        pGen.ParameterName = "@Gender";
        pGen.SqlDbType = SqlDbType.VarChar;
        pGen.Direction = ParameterDirection.Input;
        pGen.Value = rblGender.SelectedValue;
        command.Parameters.Add(pGen);

        SqlParameter pAge = new SqlParameter();
        pAge.ParameterName = "@Age";
        pAge.SqlDbType = SqlDbType.Int;
        pAge.Direction = ParameterDirection.Input;
        pAge.Value = txtAge.Text;
        command.Parameters.Add(pAge);

        SqlParameter pAdd1 = new SqlParameter();
        pAdd1.ParameterName = "@Address1";
        pAdd1.SqlDbType = SqlDbType.VarChar;
        pAdd1.Direction = ParameterDirection.Input;
        pAdd1.Value = txtAddress1.Text;
        command.Parameters.Add(pAdd1);

        SqlParameter pAdd2 = new SqlParameter();
        pAdd2.ParameterName = "@Address2";
        pAdd2.SqlDbType = SqlDbType.VarChar;
        pAdd2.Direction = ParameterDirection.Input;
        pAdd2.Value = txtAddress2.Text;
        command.Parameters.Add(pAdd2);

        SqlParameter pCity = new SqlParameter();
        pCity.ParameterName = "@City";
        pCity.SqlDbType = SqlDbType.VarChar;
        pCity.Direction = ParameterDirection.Input;
        pCity.Value = txtCity.Text;
        command.Parameters.Add(pCity);

        SqlParameter pPhone = new SqlParameter();
        pPhone.ParameterName = "@Phone";
        pPhone.SqlDbType = SqlDbType.VarChar;
        pPhone.Direction = ParameterDirection.Input;
        pPhone.Value = txtPhone.Text;
        command.Parameters.Add(pPhone);

        SqlParameter pMobile = new SqlParameter();
        pMobile.ParameterName = "@Mobile";
        pMobile.SqlDbType = SqlDbType.VarChar;
        pMobile.Direction = ParameterDirection.Input;
        pMobile.Value = txtMobile.Text;
        command.Parameters.Add(pMobile);

        SqlParameter pEmail = new SqlParameter();
        pEmail.ParameterName = "@Email";
        pEmail.SqlDbType = SqlDbType.VarChar;
        pEmail.Direction = ParameterDirection.Input;
        pEmail.Value = txtEmail1.Text;
        command.Parameters.Add(pEmail);

        SqlDataReader reader = command.ExecuteReader();

        reader.Dispose();
        command.Dispose();
        conn.Dispose();
    }
}