SQL query not working

Question

hi, I'm trying to create a query in an access database for a C# dataset using the query editor but the created method says there is a problem and isn't created correctly.

SELECT Discs.*
FROM Discs
WHERE (Title=@Title OR @Title IS NULL) AND
(Type=@Type OR @Type IS NULL) AND
(ContainerID=@ContainerID OR @ContainerID IS NULL) AND
NOT (@Title IS NULL AND @Type IS NULL AND @ContainerID IS NULL)

the error is:

Generated SELECT statement.
Error in WHERE clause near '@'.
Unable to parse query text.

the generated select method doesn't have any parameters and is unusable. I've tried the exact same SQL statement in the access query and it worked flawlessly, what am I supposed to be doing differently when transferring it to C#?

Answer 1

As far as I can remember (last time I tried, .NET 1.1 was the latest thing, so I'm not sure it's the case), when you are talking to Jet OLEDB provider, you should signify parameters in the query with question marks (parameters are not named and the order matters):

SELECT [Discs].*
FROM [Discs]
WHERE ([Title]=? OR ? IS NULL) AND
([Type]=? OR ? IS NULL) AND
([ContainerID]=? OR ? IS NULL) AND
NOT (? IS NULL AND ? IS NULL AND ? IS NULL)

(I'd wrap the identifiers in brackets so that they don't cause problems should one of them be a keyword, you may try this before changing parameters to question marks)

Considering that your query uses the same parameters repeatedly, you probably have to add it more than once to the Parameters collection of your OleDbCommand instance.

Answer 2

I'm not sure if I understand correctly what you want, but:

  SELECT   *
  FROM     Discs
  WHERE 
           (Title = @Title OR @Title IS NULL) 
           AND (Type = @Type OR @Type IS NULL) 
           AND (ContainerID = @ContainerID OR @ContainerID IS NULL)
           AND (@Title IS NOT NULL OR @Type IS NOT NULL OR @ContainerID IS NOT NULL)

You could also pull the "one of the three variables needs to be not-null" outside the query in an IF statement, which I'd prefer.

Answer 3

Not sure if it is faster or not, but the following:

 Title = @Title OR @Title IS NULL 

Could also be written as

 ISNULL(@Title,Title) = Title

This seems clearer to me, however this may be because it is how I've always seen it done.

It also solves the problem with Mehrdad Afshari's answer of having to list the parameters multiple times. eg:

ISNULL(?,Title) = Title