sqlsql-servergridviewsqlcommandparameterized-query

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


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?


Solution

  • 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