C # SQL error - must declare scalar
I have already declared a scalar, but I am still getting the error. My code checks to see if a record exists, if it updates a record, or if it doesn't exist, it creates a new record:
try
{
string server = Properties.Settings.Default.SQLServer;
string connection = "Data Source=" + server + ";Initial Catalog=Propsys;Persist Security Info=True;User ID=sa;Password=0925greg";
using (SqlConnection cn = new SqlConnection(connection))
{
cn.Open();
SqlCommand cmdCount = new SqlCommand("SELECT count(*) from Agent WHERE ContactPerson = @" + this.contactPersonTextBox.Text, cn);
cmdCount.Parameters.AddWithValue("@ContactPerson", contactPersonTextBox.Text);
SqlDataReader myReader;
myReader = cmdCount.ExecuteReader();
int count = 0;
while (myReader.Read())
{
count = count + 1;
}
if (count > 0)
{
string query = "UPDATE _1Agent SET DealID = @DealID, \n" +
"ContactPerson = @ContactPerson, \n" +
"Address = @Address, \n" +
"TaxVatNo = @TaxVatNo, \n" +
"Comm = @Comm, \n" +
"WorkTel = @WorkTel, \n" +
"Cell = @Cell, \n" +
"Fax = @Fax, \n" +
"Email = @Email, \n" +
"Web = @Web, \n" +
"CreateDate = @CreateDate, \n" +
"Notes = @Notes WHERE id = @id";
SqlCommand cm = new SqlCommand(query);
string Contact = contactPersonTextBox.Text;
cm.Parameters.AddWithValue("@DealID", txtDealNo.Text);
cm.Parameters.AddWithValue("@ContactPerson", contactPersonTextBox.Text);
cm.Parameters.AddWithValue("@Address", addressTextBox.Text);
cm.Parameters.AddWithValue("@TaxVatNo", taxVatNoTextBox.Text);
cm.Parameters.AddWithValue("@Comm", commTextBox.Text);
cm.Parameters.AddWithValue("@WorkTel", workTelTextBox.Text);
cm.Parameters.AddWithValue("@Cell", cellTextBox.Text);
cm.Parameters.AddWithValue("@Fax", faxTextBox.Text);
cm.Parameters.AddWithValue("@Email", emailTextBox.Text);
cm.Parameters.AddWithValue("@CreateDate", DateTime.Now);
cm.Parameters.AddWithValue("@Notes", notesTextBox.Text);
cm.CommandText = query;
cm.ExecuteNonQuery();
cn.Close();
MessageBox.Show("Saved...", "Data Saved", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
else
{
string query1 = "INSERT INTO _1Agent (DealID, \n" +
"ContactPerson, \n" +
"Address, \n" +
"TaxVatNo, \n" +
"Comm, \n" +
"WorkTel, \n" +
"Cell, \n" +
"Fax, \n" +
"Email, \n" +
"CreateDate, \n" +
"Notes) VALUES ('" + txtDealNo.Text + "',\n" +
"'" + contactPersonTextBox.Text + "',\n" +
"'" + addressTextBox.Text + "',\n" +
"'" + taxVatNoTextBox.Text + "',\n" +
"'" + commTextBox.Text + "',\n" +
"'" + workTelTextBox.Text + "',\n" +
"'" + cellTextBox.Text + "',\n" +
"'" + faxTextBox.Text + "',\n" +
"'" + emailTextBox.Text + "',\n" +
"'" + notesTextBox.Text + "',\n" +
"'" + DateTime.Now + "')";
SqlCommand cm = new SqlCommand(query1);
string Contact = contactPersonTextBox.Text;
cm.Parameters.AddWithValue("@DealID", txtDealNo.Text);
cm.Parameters.AddWithValue("@ContactPerson", contactPersonTextBox.Text);
cm.Parameters.AddWithValue("@Address", addressTextBox.Text);
cm.Parameters.AddWithValue("@TaxVatNo", taxVatNoTextBox.Text);
cm.Parameters.AddWithValue("@Comm", commTextBox.Text);
cm.Parameters.AddWithValue("@WorkTel", workTelTextBox.Text);
cm.Parameters.AddWithValue("@Cell", cellTextBox.Text);
cm.Parameters.AddWithValue("@Fax", faxTextBox.Text);
cm.Parameters.AddWithValue("@Email", emailTextBox.Text);
cm.Parameters.AddWithValue("@CreateDate", DateTime.Now);
cm.Parameters.AddWithValue("@Notes", notesTextBox.Text);
cm.CommandText = query1;
cm.ExecuteNonQuery();
cn.Close();
MessageBox.Show("Saved...", "Data Saved", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
source to share
Your use of the parameter is incorrect, it should be:
SqlCommand cmdCount =
new SqlCommand("SELECT count(*) from Agent WHERE ContactPerson = @ContactPerson", cn);
Later, you add the parameter correctly.
cmdCount.Parameters.AddWithValue("@ContactPerson", contactPersonTextBox.Text);
To use a counter SqlCommand.ExecuteScalar
, instead of using DataReader
:
int count = (int) cmdCount.ExecuteScalar();
For the rest of the queries UPDATE
and INSERT
you can use a shorthand string instead of string concatenation across multiple lines.
string query = @"UPDATE _1Agent SET DealID = @DealID,
ContactPerson = @ContactPerson,
Address = @Address,
TaxVatNo = @TaxVatNo,
Comm = @Comm,
WorkTel = @WorkTel,
Cell = @Cell,
Fax = @Fax,
Email = @Email,
Web = @Web,
CreateDate = @CreateDate,
Notes = @Notes WHERE id = @id";
Other problems with the code:
- You concatenate strings to form a query
INSERT
, then add parameters, follow the same convention as the queryUPDATE
, and then use parameters. - As pointed out in another answer, you are not adding a parameter value
@id
for the commandUPDATE
- You are not specifying the connection property with the command
UPDATE
andINSERT
:
Specify it as
SqlCommand cm = new SqlCommand(query, cn);
- Consider the object
Connection
andCommand
inusing
as it will ensure the proper management of unmanaged resources.
source to share
I see several things;
Do not use signed string concatenation @
for parameters. This is a misuse. Use it like:
"SELECT count(*) from Agent WHERE ContactPerson = @ContactPerson"
and
cmdCount.Parameters.AddWithValue("@ContactPerson", contactPersonTextBox.Text);
and use ExecuteScalar
to get the first column of the first row. This reader is not required.
The request UPDATE
requires a value @id
as you declare it in your command as:
cm.Parameters.AddWithValue("@id", yourIDvalue);
In a request, INSERT
you never declare your parameters in your command. You simply unite them with your values. And use verbatim string literal to generate multi-line strings instead of using \n
.
You are welcome
Read more about parameterized queries and how to use them.
source to share
You forget to mention the parameter name in the selected query
SqlCommand cmdCount = new SqlCommand("SELECT count(*) from Agent WHERE ContactPerson = @ContactPerson", cn);
cmdCount.Parameters.AddWithValue("@ContactPerson", contactPersonTextBox.Text);
There are some wrong things. So you can refer to @Soner Gönül and @habib answers
And change your insert request. Because you declared parameters, but you did not define. So change as follows
string query1 = "INSERT INTO _1Agent (DealID,ContactPerson,Address,TaxVatNo,
Comm, WorkTel, Cell, Fax, Email,Notes,CreateDate)
VALUES ( @DealID , @ContactPerson,@Address ,@TaxVatNo ,
@Comm,@WorkTel , @Cell,@Fax,@Email,@Notes,@CreateDate)";
source to share