469,625 Members | 1,080 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

IS Null

OuTCasT
374 256MB
Expand|Select|Wrap|Line Numbers
  1. 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....???
Mar 3 '08 #1
22 1494
amitpatel66
2,367 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1. 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??
Mar 3 '08 #2
OuTCasT
374 256MB
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....
Mar 3 '08 #3
amitpatel66
2,367 Expert 2GB
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??
Mar 3 '08 #4
OuTCasT
374 256MB
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....
Mar 3 '08 #5
OuTCasT
374 256MB
it must work when i choose all 3 values aswell
but at the same time work if i only choose 2 or 1 value
Mar 3 '08 #6
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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])))
  3.  
  4.  
Mar 3 '08 #7
OuTCasT
374 256MB
Alright, then try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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])))
  3.  
  4.  
not bringing any data back :/
Mar 3 '08 #8
code green
1,726 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. ........ WHERE (([race] = CASE WHEN @race THEN [race] ELSE NULL,
  2. ([province] = CASE WHEN @province THEN [province] ELSE 1,
  3. ([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.
Mar 3 '08 #9
amitpatel66
2,367 Expert 2GB
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?
Mar 3 '08 #10
code green
1,726 Expert 1GB
What am I talking about, that won't work.
Oh dear, sorry.
I was confusing my idea with
Expand|Select|Wrap|Line Numbers
  1. WHERE 1
Mar 3 '08 #11
amitpatel66
2,367 Expert 2GB
Are you sure the input value that you passed for the three list boxes contains the data in the database?
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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))
  3.  
  4.  
Mar 3 '08 #12
code green
1,726 Expert 1GB
Brilliant amitpatel66, I was nearly there!
Surely that must work now OuTCasT
Mar 3 '08 #13
OuTCasT
374 256MB
Thanks guys......

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

:)
Mar 4 '08 #14
OuTCasT
374 256MB
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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))
  3.  
  4.  
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>

???
Mar 6 '08 #15
amitpatel66
2,367 Expert 2GB
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?
Mar 6 '08 #16
OuTCasT
374 256MB
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.
Mar 6 '08 #17
amitpatel66
2,367 Expert 2GB
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?
Mar 6 '08 #18
OuTCasT
374 256MB
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
Mar 6 '08 #19
amitpatel66
2,367 Expert 2GB
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.
Mar 6 '08 #20
OuTCasT
374 256MB
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

Expand|Select|Wrap|Line Numbers
  1. 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...
Mar 19 '08 #21
amitpatel66
2,367 Expert 2GB
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??
Mar 19 '08 #22
OuTCasT
374 256MB
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 ?
Mar 19 '08 #23

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

26 posts views Thread by Agoston Bejo | last post: by
3 posts views Thread by iStrain | last post: by
5 posts views Thread by Mike MacSween | last post: by
3 posts views Thread by sathyashrayan | last post: by
102 posts views Thread by junky_fellow | last post: by
29 posts views Thread by Jason Curl | last post: by
5 posts views Thread by David Sworder | last post: by
64 posts views Thread by yossi.kreinin | last post: by
reply views Thread by Aaron Morton | last post: by
46 posts views Thread by lovecreatesbea... | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.