Why am I getting an exception telling me that I need to declare a scalar variable here (referencing a parameter in a query)?

Question

I've got a parameterized query which I build up based on selection criteria the user chooses on a web page. For example, here is an example query string (completeQuery) that is built:

SELECT M.MovieTitle, M.IMDBRating, M.MPAARating, M.YearReleased, M.Minutes 
FROM dbo.MOVIES_MAIN M 
LEFT JOIN GENRES_MOVIES_M2M G ON M.MovieId = G.MovieId 
WHERE M.IMDBRating >= @IMDBMinRating 
AND M.YearReleased BETWEEN @EarliestYear AND @LatestYear 
AND G.GENRES IN (Biography, Documentary, Drama, History, Music, Mystery, Western ) 
AND M.MPAARating IN (PG ) 
ORDER BY M.IMDBRating DESC, M.YearReleased DESC

I then attempt to assign the result of the query (contained in the "completeQuery" string) to a GridView like so:

try
{
    using (SqlConnection connection = new SqlConnection(connStr))
    {
        using (SqlCommand cmd = new SqlCommand(completeQuery, connection))
        {
            cmd.Parameters.AddWithValue("@IMDMinRating", _imdbThreshold);
            cmd.Parameters.AddWithValue("@EarliestYear", _yearBegin);
            cmd.Parameters.AddWithValue("@LatestYear", _yearEnd);

            SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            dAdapter.Fill(ds);
            GridView1.DataSource = ds.Tables[0];
            connection.Close();
        }
    }    
}
catch (Exception ex)
{
    string s = ex.Message;
}

An exception is thrown on the following line:

GridView1.DataSource = ds.Tables[0];

The exception message is Must declare the scalar variable "@IMDBMinRating".

Since that is the first parameter added, I assume it would also complain about the other two parameters.

Why is it not seeing/accepting @IMDMinRating?

Move the `AND G.GENRES IN (` condition from WHERE to ON if you want a true LEFT JOIN result. — jarlh, Oct 05 '20 at 15:56
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Oct 05 '20 at 15:58
Or, alternatively [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/). — Thom A, Oct 05 '20 at 15:59

Caius Jard · Accepted Answer · 2020-10-05T16:03:34.023

Your SQL query says IMDB and your C# parameter name says IMD

WHERE M.IMDBRating >= @IMDBMinRating 
                          ^


cmd.Parameters.AddWithValue("@IMDMinRating", _imdbThreshold);
                                 ^

I assume it would also complain about the other two parameters.

The other two parameters don't have typos.. And of course if the error had been "No value supplied for @IMDBMinRating. Query doesn't use supplied parameter named '@IMDMinRating'" you probably would have realized immediately!

It's one of those things you just have to chalk up to experience and double check next time, when SQLServer says a parameter in your query doesn't have a value it's usually the case (and easy to miss)..

Unrelated to your issue, but you should read the advice in Joel's blog about AddWithValue

Why am I getting an exception telling me that I need to declare a scalar variable here (referencing a parameter in a query)?

1 Answers1

Linked