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

On which line exactly? What is the value of `this.contactPersonTextBox.Text`? — Soner Gönül, Jun 30 '15 at 13:00
The code fails at this point: myReader = cmdCount.ExecuteReader(); — user2542289, Jun 30 '15 at 13:03
You should use `ExecuteScalar` to read your `count(*)` result, there is no need to loop a reader; it always returns a single value. Use `if exists` if you dont actually care about the count value. — Alex K., Jun 30 '15 at 13:04

Habib · Answer 1 · 2015-06-30T13:33:36.580

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.

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

3

I see a few things;

Don't use string concatenation with @ sign for parameters. That's wrong usage. Use it like;

"SELECT count(*) from Agent WHERE ContactPerson = @ContactPerson"

and

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

and use ExecuteScalar to get first column of the first row. Using a reader is unnecessary for this command.

Your UPDATE query requires @id value since you declare it in your command as;

cm.Parameters.AddWithValue("@id", yourIDvalue);

Your INSERT query, you never declare your parameters in your command. You just concatenate them with their values. And use verbatim string literal to generate multiline strings instead of using \n.

Please

Read more carefully about parameterized queries and how you can use them.

Give me parameterized SQL, or give me death

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 30 '15 at 13:04

Soner Gönül

97,193
102
206
364

1

There could be so many errors for "Must declare Scalar" in the OP, +1 for scrolling down and taking a look at the INSERT statement as well, – Habib Jun 30 '15 at 13:11
Okay, its working well with the existing entry location and then deciding to update or insert. But now I am getting this error: ExecuteNonQuery: Connection property has not been initialized. When should I open the connection and then close? – user2542289 Jun 30 '15 at 13:27
@user2542289, that is because with your `INSERT` and `UPDATE` commands, you are not specifying the connection `SqlCommand cm = new SqlCommand(query);`, Just do `SqlCommand cm = new SqlCommand(query, cn);` – Habib Jun 30 '15 at 13:29

Nagaraj S · Answer 3 · 2015-06-30T13:22:49.287

You forget to mention parameter name in your select 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 @Soner Gönül and @habib answers

And change your insert query.Since you have declared paramertes but you didn't 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)";

c# SQL Error - Must declare scalar

3 Answers3

Please