473,390 Members | 900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

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 1682
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

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

Similar topics

26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
3
by: iStrain | last post by:
Hiya. I'm _sure_ this is an FAQ, but Googling hasn't produced the answer in a way I can make sense out of. I know I should get this, but so far no way... I'm creating tables and doing queries in...
5
by: Mike MacSween | last post by:
This as the row source for a combo: SELECT qryRole.RoleID, qryRole.Role FROM qryRole WHERE (((qryRole.RoleID) Not In (SELECT RoleID FROM qryRoleEvent INNER JOIN qryEvent ON qryRoleEvent.EventID...
3
by: sathyashrayan | last post by:
The standard confirms that the following initialization of a struct struct node { --- --- } struct node var = {NULL};
102
by: junky_fellow | last post by:
Can 0x0 be a valid virtual address in the address space of an application ? If it is valid, then the location pointed by a NULL pointer is also valid and application should not receive "SIGSEGV"...
29
by: Jason Curl | last post by:
I've been reading this newsgroup for some time and now I am thoroughly confused over what NULL means. I've read a NULL pointer is zero (or zero typecast as a void pointer), others say it's...
5
by: David Sworder | last post by:
Hi, I've created a UserControl-derived class called MyUserControl that is able to persist and subsequently reload its state. It exposes two methods as follows: public void Serialize(Stream...
64
by: yossi.kreinin | last post by:
Hi! There is a system where 0x0 is a valid address, but 0xffffffff isn't. How can null pointers be treated by a compiler (besides the typical "solution" of still using 0x0 for "null")? -...
0
by: Aaron Morton | last post by:
I'm working on a IHttpModule that handles the PreSendRequestHeaders event from the HttpApplication, if the event is raised after EndRequest then HttpContext.Current is null. If it is raised before...
46
by: lovecreatesbea... | last post by:
Do you prefer malloc or calloc? p = malloc(size); Which of the following two is right to get same storage same as the above call? p = calloc(1, size); p = calloc(size, 1);
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.