IS Null

[OuTCasT]

[CODE=sql]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))[/CODE]

This query that i have brings back data from db according to the selection in the 3 dropdown listboxes....th is 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 dropdownlistbox es must be able to work independently and all together at once....???

[amitpatel66]

[CODE=sql]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))[/CODE]

This query that i have brings back data from db according to the selection in the 3 dropdown listboxes....th is 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 dropdownlistbox es 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:

[code=sql]

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(@provinc e,[province])) AND ([education] = ISNULL(@educati on,[education])))

[/code]

[OuTCasT]

Alright, then try this:

[code=sql]

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(@provinc e,[province])) AND ([education] = ISNULL(@educati on,[education])))

[/code]

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:

[code=sql]

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]

[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:

[code=sql]

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]

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 dropdownlistbox es then nothing happens, only when i make all 3 choices the info pops up....
why does it do that>

???