Null string query

I have an application with a Drop Down list and a Text box.

The Drop Down has a list of store names and the Text box is for a string query on the Item Name.

Here is my SELECT statement:

SELECT *  FROM [Items] 
 WHERE ((LTRIM(RTRIM(@ItemName)) = '') OR ([ItemName] LIKE '%' + @ItemName + '%'))
 AND ((LTRIM(RTRIM(@StoreName)) = '0') OR ([StoreName] = @StoreName))

I tried to set the intial value to "something" to at least return some results if the end user left the Text box blank so I tried this:

 protected void SearchBtn_Click(object sender, EventArgs e)
        {
            if (SearchTxtBox.Text.Trim() == String.Empty)
            {
                SearchTxtBox.Text = "a";
            }

SearchBtn is my control to submit my string query on Item Name.  My Drop down is my control for the AND, OR statement and my Text box is my control for the first part of my WHERE statement and the OR, LIKE statement.

 

Ultimately what I would like to accomplish is when a Store Name is selected from the Drop down, and the Text box is left empty it will return all the results from that Store Name. From what I have gathered it seems that I would have to Index my Items table to do this.  Is that the only way. I thought maybe there would be an intial value I could set the Text box to that could return all the "Items" in a "Store" and make the intial value not visible to the end user.

 

Thanks in advance.

 

Hi Mike,

You should change your SELECT statement, you should check if its null:

SELECT *  FROM [Items] 
 WHERE ((@ItemName IS NULL) OR ([ItemName] LIKE '%' + @ItemName + '%'))
 AND ((@StoreName IS NULL) OR ([StoreName] = @StoreName))

In this case if TextBox is empty the input parameter will be null, and will be returned all, otherwise will return search criteria results.

Best Regards,

Gjorgji

The same issue is posted in the following thread: http://www.codeasp.net/forums/asp-net-topics/data-access/778/filter-a-sql-db-with-a-drop-down-list/1#2682%29

Please avoid posting duplicate threads and lets finish the conversation in the original one.

Thanks,
Hajan

 

Gjorgji Dimitrov said:

Hi Mike,

You should change your SELECT statement, you should check if its null:

 

SELECT *  FROM [Items] 
 WHERE ((@ItemName IS NULL) OR ([ItemName] LIKE '%' + @ItemName + '%'))
 AND ((@StoreName IS NULL) OR ([StoreName] = @StoreName))

In this case if TextBox is empty the input parameter will be null, and will be returned all, otherwise will return search criteria results.

 

Best Regards,

Gjorgji

This query will work. However, I think when he selects no option in the DropDownList, he sends '' (empty string) instead of null. So, the is null check should be changed to ''.

Check my answer on the other thread: http://www.codeasp.net/forums/asp-net-topics/data-access/778/filter-a-sql-db-with-a-drop-down-list/1#2682%29

I think this is what you meant.  This is how I got it to work:

SELECT *  FROM [Items] WHERE ((coalesce(@ItemName,'') = '') OR ([ItemName] LIKE '%' + @ItemName + '%'))
                                                AND ((coalesce(@StoreName,'') = '') OR ([StoreName] = @StoreName))

again thansk for your help!