Must declare the scalar variable "@"

Question

I am doing simple delete operation... In SQL Server Profiler this generated query is shown:

DELETE FROM OCRDecibel 
WHERE ConsumerLocation = @ConsumerLocation, Level = @Level,
MaxdbLevelObserved = @MaxdbLevelObserved, City = @City',
N'@ConsumerLocation nvarchar(40), @Level nvarchar(7), @MaxdbLevelObserved nvarchar(2), @City nvarchar(8)',
@ConsumerLocation = N'Walk near Majestic(Railway station area)',
@Level = N'Level 2', @MaxdbLevelObserved = N'84', @City = N'BANGLORE'

This is throwing 2 errors :

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@ConsumerLocation".
Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark after the character string ''.

I have gone through the code ..and run the query on SQL . still same Issue ..I could not understand where I am missing..

This is my DAL code :

private const string DEL_HW =
            @"DELETE FROM OCRDecibel WHERE ConsumerLocation=@ConsumerLocation,Level=@Level,MaxdbLevelObserved=@MaxdbLevelObserved,City=@City";

****************

 using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = DEL_HW;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = Configuration.TimeOutSeconds;
                    cmd.Parameters.AddWithValue("@ConsumerLocation", consumerLocation);
                    cmd.Parameters.AddWithValue("@Level", level);
                    cmd.Parameters.AddWithValue("@MaxdbLevelObserved", maxdbLevelObserved);
                    cmd.Parameters.AddWithValue("@City", city);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    conn.Close();
                }
            }
            return returnval;
        }

Any Suggestion would be Helpful ...

Adriaan Stander Adriaan Stander · Accepted Answer · 2014-06-23T04:32:35

You probably want to change your SQL to use AND

Something like

DELETE FROM OCRDecibel 
WHERE ConsumerLocation=@ConsumerLocation
AND Level=@Level
AND MaxdbLevelObserved=@MaxdbLevelObserved
AND City=@City

Also, I think the call to cmd.Dispose(); as you are using a using block

Must declare the scalar variable "@"

1 Answers