CODEWITHSUNDEEP
asp.netdatabase
yrazlik
yrazlik

Reputation: 10777

What is wrong with this query in asp.net?

I am trying to make a query that does the following: I have columns in my Items database, and these columns are called Title, AuthorName and ItemType. I want to do the following in my query: User enters AuthorName, Title and selects ItemType from the dropdown list. User may leave AuthorName or Title boxes empty. Even if he for example does not fill AuthorName part, if an item is found with specified ItemType and specified Title i show it in results. Similarly Title may be left empty, in this case the item with the specified AuthorName is listed in results list. Here is my query:

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

But when AuthorName or Title is left empty, no results are found. Can anyone help?

Thanks

Upvotes: 0

Views: 87

Answers (4)

pete
pete

Reputation: 25081

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

Upvotes: 1

Thomas
Thomas

Reputation: 64635

Select ...
From Items
Where ( @Title Is Null Or Title Like '%' + @Title + '%' )
    And ( @AuthorName Is Null Or AuthorName Like '%' + @AuthorName + '%' )
    And ItemType = @ItemType

In ASP.NET, you should be checking your form values against string.IsNullOrEmpty and if true, sending DBNull.Value to your parametrized query instead of an empty string.

If you insist on passing an empty string for your parameters (which isn't preferred) then the query needs to check for empty strings:

Select ...
From Items
Where ( Len(@Title) = 0 Or Title Like '%' + @Title + '%' )
    And ( Len(@AuthorName) = 0 Or AuthorName Like '%' + @AuthorName + '%' )
    And ItemType = @ItemType

In SQL Server, the Len function will ignore spaces. If you really wanted to go crazy and do all your checking in the query (which again isn't recommended) then you would want to account for your parameters being empty strings, all spaces or nulls:

Select ...
From Items
Where ( Len(Coalesce(@Title,'')) = 0 Or Title Like '%' + @Title + '%' )
    And ( Len(Coalesce(@AuthorName,'')) = 0 Or AuthorName Like '%' + @AuthorName + '%' )
    And ItemType = @ItemType

Upvotes: 2

Kevin Shah
Kevin Shah

Reputation: 1617

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.

Upvotes: 1

Scott Shipp
Scott Shipp

Reputation: 2301

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

Upvotes: 1

Related Questions