sqlsqlitesql-likedynamic-sqldb-browser-sqlite

Why are no rows returned in my app, whereas the same query returns rows in DBBrowser?


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:

enter image description here

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'):

enter image description here

Yet, with the same query I do get records returned in DBBrowser:

enter image description here

What is going on here? Why does the same query return nothing in my app, but gobs of stuff from DBBrowser?

UPDATE

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"

UPDATE 2

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:

  1. "AND (genres = @genre1 OR genres = @genre2) "

  2. "AND ((genres = @genre1) OR (genres = @genre2)) "

  3. "AND ((genres LIKE '%' || @genre1 || '%') OR (genres LIKE '%' || @genre2 || '%')) "

  4. AND (genres LIKE '%'+@genre1+'%' OR genres LIKE '%'+@genre2+'%')

  5. 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()));

UPDATE 3

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:

enter image description here

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

Solution

  • 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)