IS Null

[OuTCasT]

SELECT   [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [field], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = @race) AND ([province] = @province) AND ([education] = @education))

This query that i have brings back data from db according to the selection in the 3 dropdown listboxes....this is only done if all 3 dropdown listboxes are selected.
how do i make the query work if only 1 dropdownlistbox is chosen and the other 2 are left blank????
but all dropdownlistboxes must be able to work independently and all together at once....???

[amitpatel66]

SELECT   [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [field], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = @race) AND ([province] = @province) AND ([education] = @education))

This query that i have brings back data from db according to the selection in the 3 dropdown listboxes....this is only done if all 3 dropdown listboxes are selected.
how do i make the query work if only 1 dropdownlistbox is chosen and the other 2 are left blank????
but all dropdownlistboxes must be able to work independently and all together at once....???

Does that mean when you mention the value for first list box and NULL for other two, then the query fetches data and if you mention the value for all the three list boxes, query does not fetch any value??

[OuTCasT]

i have the 3 drp boxes

Race :
Gender:
Education:
(for example)

if i choose
Race: White
Gender : NULL
Education: NULL

then it doesnt bring any data back because nthing was chosen in gender and education.

if i make all 3 selections it does bring the data correctly back but the users must be able to select only 1....

[amitpatel66]

i have the 3 drp boxes

Race :
Gender:
Education:
(for example)

if i choose
Race: White
Gender : NULL
Education: NULL

then it doesnt bring any data back because nthing was chosen in gender and education.

if i make all 3 selections it does bring the data correctly back but the users must be able to select only 1....

If my understaning is correct, do you mean that user must be able to select the value for only one list box? Say If I selec a value for Race list box, then other two should be disabled or something??

[OuTCasT]

most users would choose a value in all 3 listboxes...... but some users would only choose 2 values and leave 1 value = NULL

it works when i select all 3 values

i want it to work when i only select like 1 or 2 values....

[OuTCasT]

it must work when i choose all 3 values aswell
but at the same time work if i only choose 2 or 1 value

[amitpatel66]

it must work when i choose all 3 values aswell
but at the same time work if i only choose 2 or 1 value

Alright, then try this:

 
SELECT   [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [FIELD], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = ISNULL(@race,[race])) AND ([province] = ISNULL(@province,[province])) AND ([education] = ISNULL(@education,[education])))
 
 

[OuTCasT]

Alright, then try this:

 
SELECT   [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [FIELD], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = ISNULL(@race,[race])) AND ([province] = ISNULL(@province,[province])) AND ([education] = ISNULL(@education,[education])))
 
 

not bringing any data back :/

[code green]

it must work when i choose all 3 values aswell
but at the same time work if i only choose 2 or 1 value

So the filter condition demands that the record fields must match
the relevant list items chosen and ignore the ones not chosen?
If that is correct then a field not chosen needs to be left out of the WHERE clause,
and not tested IS NULL otherwise that field MUST be NULL.
What about a CASE. Not sure about this but something like

........ WHERE (([race] = CASE WHEN @race THEN [race] ELSE NULL,
([province] = CASE WHEN @province THEN [province] ELSE 1,
([education] = CASE WHEN @education THEN [education] ELSE 1])))

This is only a rough draft and not tested but the idea is,
if the list box item is empty or not chosen it will test false so a TRUE is returned regardless and not the field.
If the list entry is chosen the field value is returned.

[amitpatel66]

not bringing any data back :/

Are you sure the input value that you passed for the three list boxes contains the data in the database?

[code green]

What am I talking about, that won't work.
Oh dear, sorry.
I was confusing my idea with

WHERE 1

[amitpatel66]

Are you sure the input value that you passed for the three list boxes contains the data in the database?

Try this:

 
SELECT   [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [FIELD], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = CASE WHEN @race IS NOT NULL THEN @race ELSE [race] END) AND ([province] = CASE WHEN @province IS NOT NULL THEN @province ELSE [province] END) AND ([education] = CASE WHEN @education IS NOT NULL THEN @education ELSE [education] END))
 
 

[code green]

Brilliant amitpatel66, I was nearly there!
Surely that must work now OuTCasT

[OuTCasT]

Thanks guys......

That is working 100%
i knew that i had to use NULL but not like that...

:)

[OuTCasT]

Try this:

 
SELECT   [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [FIELD], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = CASE WHEN @race IS NOT NULL THEN @race ELSE [race] END) AND ([province] = CASE WHEN @province IS NOT NULL THEN @province ELSE [province] END) AND ([education] = CASE WHEN @education IS NOT NULL THEN @education ELSE [education] END))
 
 

hey there.
this code works perfectly in query analyzer... but as soon as its on the page and ive only selected one value from the 3 dropdownlistboxes then nothing happens, only when i make all 3 choices the info pops up....
why does it do that>

???

[amitpatel66]

hey there.
this code works perfectly in query analyzer... but as soon as its on the page and ive only selected one value from the 3 dropdownlistboxes then nothing happens, only when i make all 3 choices the info pops up....
why does it do that>

???

I think this issue is related to front end part, where you need to select atleast NULL for the other two list box if you dont want to select any other value. Do you have a page with submit button or something, which you click after selecting a value form the three list boxes?

[OuTCasT]

I think this issue is related to front end part, where you need to select atleast NULL for the other two list box if you dont want to select any other value. Do you have a page with submit button or something, which you click after selecting a value form the three list boxes?

yes i have a button that i click to submit.

in the 3 dropdownboxes i used static values equal to the database instead of binding them.

the first listitem in each is
<asp:ListItem></asp:ListItem>

maybe its the null value that is not going through for the other selections.

[amitpatel66]

yes i have a button that i click to submit.

in the 3 dropdownboxes i used static values equal to the database instead of binding them.

the first listitem in each is
<asp:ListItem></asp:ListItem>

maybe its the null value that is not going through for the other selections.

NULL is not equal to 'Does not exist'

Set the DEFAULT for all the three list boxes to NULL and then test. Check if it works that way?

[OuTCasT]

NULL is not equal to 'Does not exist'

Set the DEFAULT for all the three list boxes to NULL and then test. Check if it works that way?

no default setting in the properties

[amitpatel66]

no default setting in the properties

If that is the case then why dont you CODE it on click of submit button. You can set the three variable values to NULL in your source code. Then on click of submit button, if you do not select any value from any of the list box, then the corresponding variable will be NULL. So This way NULL will be passed to the Query.

[OuTCasT]

If that is the case then why dont you CODE it on click of submit button. You can set the three variable values to NULL in your source code. Then on click of submit button, if you do not select any value from any of the list box, then the corresponding variable will be NULL. So This way NULL will be passed to the Query.

This query that u helped me out with

SELECT   [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [FIELD], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = CASE WHEN @race IS NOT NULL THEN @race ELSE [race] END) AND ([province] = CASE WHEN @province IS NOT NULL THEN @province ELSE [province] END) AND ([education] = CASE WHEN @education IS NOT NULL THEN @education ELSE [education] END))

I found the problem, it looks for NULL values in the db...and most of the rows have not got any null values...so no data was being returnd.
What i want is to leave them blank and not search the database for the NULL values just for the values that are selected ..???/
so if i did leave like 2 selections out it would still give me the data...

[amitpatel66]

Can you check what is the value being passed for all the three parameters. After selecting the values from all the three listboxes, pint the variables to see what value it holds, and if you do not pass a value does it hold word 'NULL' or nothing??

[OuTCasT]

Can you check what is the value being passed for all the three parameters. After selecting the values from all the three listboxes, pint the variables to see what value it holds, and if you do not pass a value does it hold word 'NULL' or nothing??

The selections are searching for NULL if nothing is selected
but for those selections like

Gender:
Race:
Field of study:
Location:

when the user saves this information its not null, he insert information and what your query does is search those fields for null values...then it brings nothing back because there are no null values in those fields

What i want is even though i leave certain selection null and only choose the other 2 selections to bring back those ppl information ....is that possible ?