querying bit field with True/False/Null possibilities, with dropdownlist as parameter source

I have a multiple-field selection / filter used to selectively display fields and filter the fields in a gridview. The bit field is causing some problems on the select filter. In my select query, I'm using:
Select....stringField1,stringField2,
ISNULL(dbo.theBitField, 0) AS theBitField
FROM theTable
WHERE
(@stringField1 = '' OR stringField1 LIKE @stringField1+ '%')
AND (@stringField2 = '' OR stringField2 LIKE @stringField2 + '%')
AND (@theBitField = '' OR theBitField LIKE @theBitField + '%')


I'm setting @theBitFIeld to the value of a dropdownList, where "no" maps to 0, "yes" maps to 1.

When I set the dropdownlist to "yes" or "no", it pulls up either the records with theBitField that are either 1 or explicitly set to 0, however, it's not pulling up all the records with <NULL> for theBitField when I ask for the "no" via the ddl. I would have expected the ISNULL(....,0) to have done that. May I assume that there is a different way of making this query and/or setting up the advanced properties of the DDL in order to get the NULLs along with the 0's ?

tnx in advance
This is yielding no results
CurriculumAsked:
Who is Participating?
 
Oliver AmayaConnect With a Mentor EntrepeneurCommented:
Hi, you have to add the ISNULL check on the where clause as well:
Select....stringField1,stringField2,
ISNULL(dbo.theBitField, 0) AS theBitField
FROM theTable
WHERE
(@stringField1 = '' OR stringField1 LIKE @stringField1+ '%')
AND (@stringField2 = '' OR stringField2 LIKE @stringField2 + '%')
AND (@theBitField = '' OR ISNULL(theBitField, 0) LIKE @theBitField + '%')

Open in new window

0
 
CurriculumAuthor Commented:
Thanks, that did the trick. When I select "no" from the ddl, I get all records with theBitField=0 or NULL.

I'll now work on the correct settings between the ddl and the query, so that I can set the ddl to "all" and get all records. I'll open a separate question if I have trouble. Thanks again, well done - I wouldn't have thought of that.
0
All Courses

From novice to tech pro — start learning today.