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);

• Damon Drake: You have the @Surname in there twice. Look at your pAge.
• Damon Drake: Actually look at all your variable names. Copy and Paste hurt you bad on this one.
• marc_s: Side-note: for all the parameter with SqlDbType.VarChar, I would recommend to always define a length!! Also: if you define a parameter as just VARCHAR - you get a string of 1 character length! Usually this is not what you want. Explicitly specify lengths - always ...
• marc_s: Also: since this is an INSERT stored procedure, you should be calling command.ExecuteNonQuery(); - there's no point in calling .ExecuteReader(), since you're not getting back any data from the execution of that stored procedure anyway.....
• Alexander: Please read up on the DRY concept and come up with a function for adding a parameter.

Answer 1

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();
 }
}

• Damon Drake: you did way more work than I was prepared to do. Kudos!

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

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