With this simple query against my SQLite database:
SELECT MovieTitle, MPAARating, IMDBRating, DurationInMinutes, YearReleased, genres, actors, directors, screenwriters
FROM MOVIES_SINGLETABLE
WHERE IMDBRating >= @IMDBMinRating
ORDER BY IMDBRating DESC, YearReleased DESC LIMIT 1000
...I get records:
But with more filtering:
SELECT MovieTitle, MPAARating, IMDBRating, DurationInMinutes, YearReleased, genres, actors, directors, screenwriters
FROM MOVIES_SINGLETABLE
WHERE IMDBRating >= @IMDBMinRating
AND (YearReleased BETWEEN @EarliestYear AND @LatestYear)
AND (genres LIKE '%@genre1%' OR genres LIKE '%@genre2%')
AND (MPAARating = '@mpaaRating1')
ORDER BY IMDBRating DESC, YearReleased DESC LIMIT 1000
...I get nothing (and yes, there are appropriate values being assigned to all those parameters, namely: 5.0, '1958', '2019', 'Drama', 'Western', and 'PG'):
Yet, with the same query I do get records returned in DBBrowser:
What is going on here? Why does the same query return nothing in my app, but gobs of stuff from DBBrowser?
By popular demand, here is how the parameters are assigned to (genresSelected is a List of string):
if (GenresFiltered())
{
if (genresSelected.Count > 0)
{
cmd.Parameters.AddWithValue("@genre1", genresSelected[0].ToString());
}
if (genresSelected.Count > 1)
{
cmd.Parameters.AddWithValue("@genre2", genresSelected[1].ToString());
}
. . .
...and the query string, prior to the parameters being assigned to, looks like so:
"SELECT MovieTitle, MPAARating, IMDBRating, DurationInMinutes, YearReleased, genres, actors, directors, screenwriters FROM MOVIES_SINGLETABLE WHERE IMDBRating >= @IMDBMinRating AND YearReleased BETWEEN @EarliestYear AND @LatestYear AND (genres LIKE '%@genre1%' OR genres LIKE '%@genre2%' OR genres LIKE '%@genre3%' OR genres LIKE '%@genre4%' OR genres LIKE '%@genre5%' OR genres LIKE '%@genre6%' OR genres LIKE '%@genre7%' OR genres LIKE '%@genre8%' OR genres LIKE '%@genre9%') AND (MPAARating LIKE '%@mpaaRating1%') ORDER BY IMDBRating DESC, YearReleased DESC LIMIT 1000"
The final query query string is now (single quotes removed):
SELECT MovieTitle, MPAARating, IMDBRating, DurationInMinutes, YearReleased, genres, actors, directors, screenwriters FROM MOVIES_SINGLETABLE WHERE IMDBRating >= @IMDBMinRating AND (YearReleased BETWEEN @EarliestYear AND @LatestYear) AND (genres LIKE @genre1 OR genres LIKE @genre2) AND (MPAARating = @mpaaRating1) ORDER BY IMDBRating DESC, YearReleased DESC LIMIT 1000
I tried all of the following permutations, and none of them return any data:
"AND (genres = @genre1 OR genres = @genre2) "
"AND ((genres = @genre1) OR (genres = @genre2)) "
"AND ((genres LIKE '%' || @genre1 || '%') OR (genres LIKE '%' || @genre2 || '%')) "
AND (genres LIKE '%'+@genre1+'%' OR genres LIKE '%'+@genre2+'%')
AND (genres LIKE @genre1 OR genres LIKE @genre2)
-with:
cmd.Parameters.AddWithValue("@genre1",string.Format("%{0}%", genresSelected[0].ToString())); cmd.Parameters.AddWithValue("@genre2",string.Format("%{0}%", genresSelected1.ToString()));
Incorporating the idea from forpas as best I could, I am able to get records returned in DBBrowser, but not yet in the actual app. Both the commented out line and the existing one below for filtering genres works. Without the single quotes encasing the value, I got an err msg about "Western" not being a column name, but with the single quotes around it it works:
So I have tried all of the following permutations, and none of them return any records:
// @genre1 and @genre2 not surrounded by single quotes
return "AND ((genres LIKE '%' || @genre1 || '%') OR (genres LIKE '%' || @genre2 || '%')) ";
// @genre1 and @genre2 surrounded by single quotes
return "AND ((genres LIKE '%' || '@genre1' || '%') OR (genres LIKE '%' || '@genre2' || '%')) ";
// @genre1 and @genre2 not surrounded by single quotes, string prepended with "@"
return @"AND ((genres LIKE '%' || @genre1 || '%') OR (genres LIKE '%' || @genre2 || '%')) ";
// @genre1 and @genre2 surrounded by single quotes, string prepended with "@"
return @"AND ((genres LIKE '%' || '@genre1' || '%') OR (genres LIKE '%' || '@genre2' || '%')) ";
The question is then, I guess: How do I produce, in C#, the same bit of SQL that does work in DBBrowser, namely this line:
AND ((genres LIKE '%' || 'Western' || '%') OR (genres LIKE '%' || 'Drama' || '%'))
Note: This also returns nothing:
AND ((genres LIKE @genre1) OR (genres LIKE @genre2))
In the sql statement, the parameters that you pass must not be enclosed inside single quotes.
Also let SQLite concat the '%'
wildcard to the parameter with the ||
operator.
So write your code like this:
WHERE IMDBRating >= @IMDBMinRating
AND (YearReleased BETWEEN @EarliestYear AND @LatestYear)
AND ((genres LIKE '%' || @genre1 || '%') OR (genres LIKE '%' || @genre2 || '%'))
AND (MPAARating = @mpaaRating1)
Update:
I see that you add the parameter values for genres like this:
cmd.Parameters.AddWithValue("@genre1",string.Format("%{0}%", genresSelected[0].ToString()));
so you include the wildcards inside the string.
This means that there is no need to concatenate them again in the sql statement.
So use the operator LIKE
with the parameters as they are:
WHERE IMDBRating >= @IMDBMinRating
AND (YearReleased BETWEEN @EarliestYear AND @LatestYear)
AND ((genres LIKE @genre1) OR (genres LIKE @genre2))
AND (MPAARating = @mpaaRating1)