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);
SqlDbType.VarChar, I would recommend to always define a length!! Also: if you define a parameter as justVARCHAR- you get a string of 1 character length! Usually this is not what you want. Explicitly specify lengths - always ...INSERTstored procedure, you should be callingcommand.ExecuteNonQuery();- there's no point in calling.ExecuteReader(), since you're not getting back any data from the execution of that stored procedure anyway.....