Must declare the scalar variable …

“Must declare the scalar variable …”

Every now and then this error is reported when using parameters in SQL statements.

The two most common reasons for this are:

.1 The parameter is simply misspelled. It is common that when there are many parameters in the parameter list that a misspelled parameter has been missed.

So, for example, running this:

            using (SqlConnection con = new SqlConnection(cs))

            {

      con.Open();

                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = "SELECT * FROM Categories WHERE CategoryID = @catId";

                cmd.Parameters.Add("@catIdd", System.Data.SqlDbType.Int).Value = 1;

        SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    Console.WriteLine("{0} {1}", rdr[0].ToString(), rdr[1].ToString());

                }

                con.Close();

            }

will cause the following exception to be thrown (since the provided parameter name (@catIdd) is misspelled, i.e. not matching the one in the SQL):

System.Data.SqlClient.SqlException: Must declare the scalar variable "@catId".

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   ...

.2 OleDb classes (OleDbConnection/OleDbCommand etc.) are used. When using OleDb the parameters in the SQL are to be set to ? and not @paramname.

So, for example, running this:

            using (OleDbConnection con = new OleDbConnection(cs))

            {

                con.Open();

                OleDbCommand cmd = con.CreateCommand();

                cmd.CommandText = "SELECT * FROM Categories WHERE CategoryID = @catId";

                cmd.Parameters.Add("@catId", OleDbType.Integer).Value = 1;

                OleDbDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

               Console.WriteLine("{0} {1}", rdr[0].ToString(), rdr[1].ToString());

                }

                con.Close();

            }

will cause the following exception to be thrown since the provided parameter name in the SQL String (@catId) is wrong. Replace it with ? (… WHERE CategoryID = ?) and it should be fine.

System.Data.OleDb.OleDbException: Must declare the scalar variable "@catId".

   at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)

   at System.Data.OleDb.OleDbDataReader.NextResult()

   ...

Comments

• Anonymous
  November 22, 2010
  Must declare the scalar variable "@catID".

• Anonymous
  May 24, 2011
  // DateTime dt = DateTime.ParseExact(DtpDateOfBIrth.Text, "dd/MM/yyyy", null);            cmd.Parameters.AddWithValue("@Name", txtName.Text);            cmd.Parameters.AddWithValue("@Address", txtAddress.Text);            cmd.Parameters.AddWithValue("@Age", MskTxtAge.Text);            cmd.Parameters.AddWithValue("@PhoneNo", MskTxtPhoneno.Text);            cmd.Parameters.AddWithValue("@UserId", txtUserid.Text);            cmd.Parameters.AddWithValue("@Password", txtPassword.Text);            cmd.Parameters.AddWithValue("@DOB", dt);            cmd.Parameters.AddWithValue("Gender",cmbGender.Text);            cmd.Connection.Open();            int i = cmd.ExecuteNonQuery();            cmd.Connection.Close();            if (i > 0)            {                MessageBox.Show("Record Saved ");            }            else            {                MessageBox.Show("Record Not Saved");            }

• Anonymous
  February 28, 2012
  The comment has been removed

• Anonymous
  November 21, 2012
  So what does the final/working code look like?  The example above leaves off just before being finished.  Not very helpful.

• Anonymous
  October 23, 2013
  Thanks.  the hint about OleDb needing ? instead of @parameter was just what I needed!

• Anonymous
  December 05, 2013
  The comment has been removed

• Anonymous
  September 02, 2014
  >When using OleDb the parameters in the SQL are to be set to ? and not @paramname GAAHHH! Thank you for saving me time.

• Anonymous
  August 08, 2015
  The comment has been removed

• Anonymous
  August 14, 2015
  And it's a nice solution and i didn't lost the time but how it is detecting the order of parameteres that's us? or there are a automatic mechanisme