“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.
using (OleDbConnection con = new OleDbConnection(cs))
OleDbCommand cmd = con.CreateCommand();
cmd.Parameters.Add("@catId", OleDbType.Integer).Value = 1;
OleDbDataReader rdr = cmd.ExecuteReader();
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()
Must declare the scalar variable "@catID".
// 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");
Create View PADD AS select OAD_ODA_ID from PM_ORDER_ADDRESSES GROUP BY OAD_ODA_ID
Select *
Into PiyushAdd
From PM_ORDER_ADDRESSES
Declare @Id int
Declare @Addvar nVarchar(255)
SET @AddVar = ''
While (Select COUNT(*) from PADD) > 0
Begin
Insert Into PM_ORDER_ADDRESSES_T VALUES('72B4DB0618DB41E18618', '')
While (Select Count(*) From PiyushAdd
where OAD_Address_type = 0 and OAD_ODA_ID = '72B4DB0618DB41E18618' ) > 0
SET @Addvar = PiyushAdd.OAD_TEXT
UPDATE PM_ORDER_ADDRESSES_T SET OAD_TEXT = OAD_TEXT + ' ' + @Addvar
WHERE PM_ORDER_ADDRESSES_T.OAD_ODA_ID = '72B4DB0618DB41E18618'
End
END
I am writting this code and getting error
Msg 137, Level 15, State 1, Line 7
Must declare the scalar variable "@Addvar".
Msg 137, Level 15, State 2, Line 8
So what does the final/working code look like? The example above leaves off just before being finished. Not very helpful.