c# SQL Error - Must declare scalar

Question

I have declared the scalar already but I am still getting the error. My code checks to see if an entry exists, if it does it updates the entry or if it does not exist it creates a new entry:

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

Answer 1

Your usage of parameter is wrong, it should be:

SqlCommand cmdCount = 
new SqlCommand("SELECT count(*) from Agent WHERE ContactPerson = @ContactPerson", cn);

Later you are adding the parameter correctly.

cmdCount.Parameters.AddWithValue("@ContactPerson", contactPersonTextBox.Text);

To get the count use SqlCommand.ExecuteScalar, instead of using DataReader:

int count = (int) cmdCount.ExecuteScalar();

For the other queries, UPDATE and INSERT, you can use a verbatim string, instead of concatenating strings over 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 issues with the code:

• You are concatenating strings to form INSERT query, later you are adding parameters, follow the same convention as UPDATE query and then use the parameters.
• As pointed out in the other answer, you are not adding parameter@id value for UPDATE command
• You are not specifying connection property with your UPDATE and INSERT command:

Specify it like

SqlCommand cm = new SqlCommand(query, cn);

• Consider enclosing Connection and Command object in using statement as it will ensure the proper disposal of unmanaged resources.