user3442765 user3442765 - 4 months ago 51
SQL Question

error with addwithvalue sql parameter

I've been advised to replace my code with

SqlParameter
to avoid SQL injection as a security issue but with my limited understanding. I tried implementing it but I'm faced with an error:


Must Declare Scalar Variable


I've tried the other suggestions of other threads about implementing a new parameter for every insertion instead of replacing the value of parameter for every entry.

String query = "INSERT INTO EmpInfo(EmpYear, EmpStatus, LName, FName, JobTitle, EmpPay, EmpDoB, EmpSex, EmpAddr, EmpCity, EmpState, EmpZIP, EmpCountry, EmpEAddr, EmpTelNo, EmpMobileNo, EmpDate) " +
"VALUES('"+EmpYear+"', @EmpStatus, @LName, @FName, @JobTitle, @EmpPay, @EmpDoB, @EmpSex, @EmpAddr, @EmpCity, @EmpState, @EmpZIP, @EmpCountry, @EmpEAddr, @EmpTelNo, @EmpMobileNo, getdate())";
String query2 = "INSERT INTO AccountInfo(LName, FName, EmpTemplate, AccountType, EmpStatus, EmpDate) " +
"VALUES (@LName, @FName, @EmpTemplate, @AccountType, @EmpStatus, GetDate())";

using (SqlConnection connection = new SqlConnection("Data Source=RB-DESKTOP;Initial Catalog=TimeDB;Persist Security Info=True;User ID=sa;Password=bautista7"))
{
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Connection = conn;

conn.Open();
cmd.CommandText = "SELECT MAX(EmpID) FROM EmpInfo";

SqlDataReader rdr = cmd.ExecuteReader();
rdr.Close();

SqlCommand command = new SqlCommand(query, cmd.Connection);
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpYear", Value = EmpYear });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpStatus", Value = "Active" });
command.Parameters.Add(new SqlParameter() { ParameterName = "@LName", Value = regLname_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@FName", Value = regFname_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@JobTitle", Value = "NULL" });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpPay", Value = PayType_cb.SelectedItem.ToString() });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpDoB", Value = regDob_dtp.Value.Date });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpSex", Value = gender });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpAddr", Value = regAddr_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpCity", Value = regCity_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpState", Value = regState_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpZIP", Value = regZip_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpCountry", Value = regCountry_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpEAddr", Value = regEmail_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpTelNo", Value = regTel_text.Text });
command.Parameters.Add(new SqlParameter() { ParameterName = "@EmpMobileNo", Value = regMob_text.Text });

command.ExecuteNonQuery();
command.Parameters.Clear();

SqlCommand command2 = new SqlCommand(query2, cmd.Connection);
command.Parameters.AddWithValue("@LName", regLname_text.Text);
command.Parameters.AddWithValue("@FName", regFname_text.Text);
command.Parameters.AddWithValue("@EmpTemplate", template);
command.Parameters.AddWithValue("@AccountType", AcctType_cb.SelectedItem.ToString());
command.Parameters.AddWithValue("@EmpStatus", "Active");

command.ExecuteNonQuery();

command.Parameters.Clear();

Answer

You are using the command variable twice instead of command2 after you create command2. You get the error because you cleared command of all parameters, then add parameters (which do not match the existing query) and then execute ExecuteNonQuery which then throws the error.

Change 2nd execution statement / command like so, notice that after the creation of command2 it is now also used instead of reusing command.

SqlCommand command2 = new SqlCommand(query2, cmd.Connection);
command2.Parameters.AddWithValue("@LName", regLname_text.Text);
command2.Parameters.AddWithValue("@FName", regFname_text.Text);
command2.Parameters.AddWithValue("@EmpTemplate", template);
command2.Parameters.AddWithValue("@AccountType", AcctType_cb.SelectedItem.ToString());
command2.Parameters.AddWithValue("@EmpStatus", "Active");
var numberOfRecordsInserted = command2.ExecuteNonQuery();

// value of numberOfRecordsInserted should be 1

Also when you are done using a SqlCommand you can dispose of it, there is no need to call SqlCommand.Parameters.Clear() unless you plan on reusing the exact same SqlCommand instance which you are not (at least not in the posted code).

Comments