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?
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