What is wrong with this query in asp.net?

I wrote a whole tutorial on this in my code blog at code.scottshipp.com. A full download of the code in that tutorial is available. It shows how to set it up as a stored procedure but also goes into detail on how to construct the where clause in your query. So it covers both instances. It may be easier for you to go the Stored Procedure route though, so you may want to give it a read.

The thing to keep in mind is that there are various methods to do this but there are some "gotcha's" also that will make your code seem to work in some situations and not others if you use something like the ISNULL function.

Instead, in general, I would recommend the following:

Add the following snippet to any part of your where clause you expect to sometimes be blank:

    OR SomeParam IS NULL

Also, it is good for readability to put parentheses around the entire thing. Do this for each parameter that you think may at some point be blank. This means for instance, that your select statement will become:

SELECT  *
FROM    [Items]
WHERE   ( ( ( [Title] LIKE '%' + @Title + '%' OR [Title] IS NULL )
        AND ( [ItemType] = @ItemType OR [ItemType] IS NULL )
      )
      OR ( ( [AuthorName] LIKE '%' + @AuthorName + '%' OR [AuthorName] IS NULL)
           AND ( [ItemType] = @ItemType OR [ItemType] IS NULL )
         )
    )

Please try this

  SELECT  *
FROM    [Items]
WHERE   ( ( ( [Title] LIKE CASE WHEN ISNULL(@Title,'')='' THEN [Title] ELSE '%' + @Title + '%' END )
            AND ( [ItemType] = @ItemType )
          )
          OR ( ( [AuthorName] LIKE CASE ISNULL(@AuthorName,'')='' THEN [AuthorName] ELSE '%' + @AuthorName + '%' END)
               AND ( [ItemType] = @ItemType )
             )
        )

This will help. This will run even is you dont pass Title or Authorname.

Try something like this:

SELECT  *
FROM    [Items]
WHERE   ([ItemType] = @ItemType)
        AND ([Title] LIKE '%' + @Title + '%' OR @Title IS NULL)
        AND ([AuthorName] LIKE '%' + @AuthorName + '%' OR @AuthorName IS NULL)

Just be sure to pass in NULL values (System.DBNull.Value) when AuthorName or Title is empty.

UPDATE:

Assuming AuthorName and Title are passed to your code-behind as empty strings when they are "not selected", you can do something like this (where authorName and title are Strings passed to your code-behind):

VB:

cmd.Parameters.Add("AuthorName", SqlDbType.VarChar, 50).Value = IIf(authorName.length > 0, authorName, System.DBNull.Value)
cmd.Parameters.Add("Title", SqlDbType.VarChar, 50).Value = IIf(title.length > 0, title, System.DBNull.Value)

C#:

cmd.Parameters.Add("AuthorName", SqlDbType.VarChar, 50).Value = (authorName.length > 0 ? authorName : System.DBNull.Value);
cmd.Parameters.Add("Title", SqlDbType.VarChar, 50).Value = (title.length > 0 ? title : System.DBNull.Value);