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 (9)

  1. Must declare the scalar variable "@catID". says:

    Must declare the scalar variable "@catID".

  2. Pramod says:

    // 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");

               }

  3. Manpreet says:

    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

    Begin

    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

    Must declare the scalar variable "@Addvar".

  4. blarman says:

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

  5. Bruce says:

    Thanks.  the hint about OleDb needing ? instead of @parameter was just what I needed!

  6. Quan says:

    Working example :

    Public Sub CreateMyOleDbCommand(connection As OleDbConnection, _

      queryString As String, parameters() As OleDbParameter)

       Dim command As New OleDbCommand(queryString, connection)

       command.CommandText = _

          "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?"

       command.Parameters.Add(parameters)

       Dim j As Integer

       For j = 0 To command.Parameters.Count – 1

          command.Parameters.Add(parameters(j))

       Next j

       Dim message As String = ""

       Dim i As Integer

       For i = 0 To command.Parameters.Count – 1

           message += command.Parameters(i).ToString() + ControlChars.Cr

       Next i

       Console.WriteLine(message)

    End Sub

  7. Steve says:

    >When using OleDb the parameters in the SQL are to be set to ? and not @paramname

    GAAHHH! Thank you for saving me time.

  8. Deep says:

    Error:System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Country'. Invalid column name 'PhoneNo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at registration.Button1_Click(Object sender, EventArgs e) in k:registrationregistration.aspx.cs:line 42 ClientConnectionId:a3bb8fd9-38c5-40a1-b838-9d89180f0531 Error Number:207,State:1,Class:16

  9. OMID says:

    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