ASP.net DropDownMenu Wildcard

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
How can I wildcard a drop down menu value that is boudn to a GridView?

For example, by default I would want all values to be shown for the following query:

SELECT Codes.*
FROM Codes

WHERE CodeType = (@CodeType) AND CodeOwner = (@CodeOwner)

At the moment it is attached to two DropDownMenus at the top of the page and only works when values have been selected, how can I wildcard the default DropDownMenu values so all results are shown to begin with?

Thanks guys :)
 
For each of your dropdown lists...

Go to properties, set append data bound items to 'true'.
Select the dropdown lists wizard and choose edit items.
Click the Add button.
Make sure selected equals true.
Add some text e.g. --All--
Set the value to '%' (without the quotes)
 
Assuming the two paramters are integers you can do something like this:

Code: 
SELECT     Codes.*
FROM         Codes

WHERE (CodeType = @CodeType OR @CodeType = 0) AND (CodeOwner = @CodeOwner OR @CodeOwner = 0)

Using this you just pass in 0 for both values unless a specific value is chosen, most easily by adding an 'All' option to your filters with a value of 0. You could do the same with strings by using = '' or any nullable type using IS NULL, but that'd be more tricky to setup using the grid view.


Mick.
 
It's not working using the % for the value. Unfortunately they are both character fields.

Here's my query:

SELECT Codes.*
FROM Codes

WHERE CodeType = (@CodeType) AND CodeStatus = (@CodeStatus)
 
Whoops, you also need to change the filters in your query.

Instead of '= @CodeType', use 'LIKE '%' + @CodeType'.
 
SELECT Codes.*
FROM Codes

WHERE CodeType = LIKE % + @CodeType AND CodeStatus = (@CodeStatus)

Doesn't like the query, any ideas?
 
Well the problem with your query is you've put % instead of '%'. It's not a very nice way of doing it though, you shouldn't be using wildcards where they're not needed.

You'd be much better off modifying the solution I gave to work with strings instead, all you'd have to do is replace the 0 in my example with '' and pass in an empty string from your filter dropdowns.


Mick.
 
Can't get it working, i've tried using %, '' and '' in my drop down menus for the value but it doesn't work.

Here's the query:

SELECT Codes.*
FROM Codes

WHERE (CodeType = @CodeType OR @CodeType = '') AND (CodeOwner = @CodeOwner OR @CodeOwner = '') AND (CodeStatus = @CodeStatus OR @CodeStatus = '')
 
Wardie said:
Can't get it working, i've tried using %, '' and '' in my drop down menus for the value but it doesn't work.
Click to expand...
What exactly are you using as the any option in your dropdowns? If the sql is ='' then the value of the option should be nothing, empty, also, check that you've set the object data source not to convert empty strings to null, or modify your query to be:
Code: 
SELECT Codes.*
FROM Codes

WHERE (CodeType = @CodeType OR @CodeType = '' OR @CodeType IS NULL) AND (CodeOwner = @CodeOwner OR @CodeOwner = '' OR @CodeOwner IS NULL) AND (CodeStatus = @CodeStatus OR @CodeStatus = '' OR @CodeStatus IS NULL)
 
In the drop down menu, for the All field, i've tried leaving the value field empty, setting a value of % or '' and neither work.

ConvertNullToDBNull property for the ObjectDataSource is set to False.
 
The method I posted works, you just didn't have the SQL query correct.

Follow through what I put on post two, and use the following as your SQL query:

Code: 
SELECT Codes.*
FROM Codes
WHERE CodeType = LIKE '%' + @CodeType AND CodeStatus = LIKE '%' + @CodeStatus

I can't quite get my head round Mickey's method.
Surely if you had a null or empty string, then selected a null or empty string it would bring back nothing, rather than all the records in the table.
If it is a better method I would also try it on my applications, but I just don't get it. :confused:
 
Last edited:
Code: 
SELECT     tblCodes.*
FROM         tblCodes

WHERE CodeCategory = LIKE '%' + @CodeCategory AND CodeStatus = LIKE '%' + @CodeStatus

Error in WHERE clause near LIKE

:x
 
Just need to remove the = symbols from the where clause:

Code: 
WHERE CodeCategory LIKE '%' + @CodeCategory AND CodeStatus LIKE '%' + @CodeStatus
 
You must log in or register to reply here.
Back
Top Bottom