Dropdownlist NULL

[OuTCasT]

Please help i have this query that works with drop downlist selections on a page to search for the information selected in the listboxes.
but having a problem when the selection is "null"

otherwise in query analyser it works 100%

[CODE=sql]SELECT * 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 ([field] = CASE WHEN @field IS NOT NULL THEN @field ELSE [field] END) AND ([gender] = CASE WHEN @gender IS NOT NULL THEN @gender ELSE [gender] END))[/CODE]

when on the page i choose only 2 values and leave others blank then it does not bring back any information

[DrBunchman]

If it works 100% in Query Analyser then the problem might lie with the data you are passing to the query. Are you sure that your parameters are being passed as NULL where you would expect them to be?

Dr B

[ck9663]

Please help i have this query that works with drop downlist selections on a page to search for the information selected in the listboxes.
but having a problem when the selection is "null"

otherwise in query analyser it works 100%

[CODE=sql]SELECT * 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 ([field] = CASE WHEN @field IS NOT NULL THEN @field ELSE [field] END) AND ([gender] = CASE WHEN @gender IS NOT NULL THEN @gender ELSE [gender] END))[/CODE]

when on the page i choose only 2 values and leave others blank then it does not bring back any information

Have you tried ISNULL ?

Also, try to display the sqlstring in a messagebox or something then paste it in query analyzer. Another place you want to look at is, if the object on your GUI is returning NULL by default if you're not selecting anything.

-- CK

[OuTCasT]

If it works 100% in Query Analyser then the problem might lie with the data you are passing to the query. Are you sure that your parameters are being passed as NULL where you would expect them to be?

Dr B

here is what the dropdownlist boxes on the web form look like.

example of the first one

[CODE=html]<asp:DropDownLi st ID="drpProvince " AppendDataBound Items=True runat="server" Width="120px" BackColor="Whit eSmoke" Visible="False" >
<asp:ListItem Selected="True" >-Select-</asp:ListItem>
<asp:ListItem>G auteng</asp:ListItem>
<asp:ListItem>F ree State</asp:ListItem>
<asp:ListItem>K waZulu Natal</asp:ListItem>
<asp:ListItem>M pumalanga</asp:ListItem>
<asp:ListItem>L impopo Province</asp:ListItem>
<asp:ListItem>N orthen Cape</asp:ListItem>
<asp:ListItem>E astern Cape</asp:ListItem>
<asp:ListItem>W estern Cape</asp:ListItem>
<asp:ListItem>N orth West</asp:ListItem>
</asp:DropDownLis t>[/CODE]

[OuTCasT]

Have you tried ISNULL ?

Also, try to display the sqlstring in a messagebox or something then paste it in query analyzer. Another place you want to look at is, if the object on your GUI is returning NULL by default if you're not selecting anything.

-- CK

so what i should do is put the string into a variable and then call the variable in a messagebox to see what is being displayed correct. ?

[OuTCasT]

Have you tried ISNULL ?

Also, try to display the sqlstring in a messagebox or something then paste it in query analyzer. Another place you want to look at is, if the object on your GUI is returning NULL by default if you're not selecting anything.

-- CK

ISNULL does not work unfortunately

[DrBunchman]

so what i should do is put the string into a variable and then call the variable in a messagebox to see what is being displayed correct. ?

Yes, once you've got the string you can run it in query analyser and see where the problem is occurring. I would guess the problem to be that you are passing "" instead of NULL values for these parameters but you'll be able to find our for yourself.

Good luck,

Dr B

[OuTCasT]

Yes, once you've got the string you can run it in query analyser and see where the problem is occurring. I would guess the problem to be that you are passing "" instead of NULL values for these parameters but you'll be able to find our for yourself.

Good luck,

Dr B

Hi Dr B.

Yes i agree that the problem is with the drop downs.....they are using " " as a value instead of NULL so nothing is being processed.

Im using a Gridview to display some data and from there they choose to view the specific row etc....
now when i configure the datasource for the gridview with that SQL query i posted earlier, i go to TEST QUERY and i only enter like 1 value and leave the others empty and it works....

Im going to have to find another solution to this problem because the bosses want the search thing upp and running already ..... :'(

[OuTCasT]

Yes, once you've got the string you can run it in query analyser and see where the problem is occurring. I would guess the problem to be that you are passing "" instead of NULL values for these parameters but you'll be able to find our for yourself.

Good luck,

Dr B

Hi again...
When testing the query i noticed that the query is ACTUALLY looking for NULL values in the columns......mo st of the columns do not have NULL values because they are populated with values.
Thats why no information was being returned....
Now i need to change the query so that instead of LOOKING for NULL values it just brings back the values if null is inserted....
i dont knw if that makes any sense.

[DrBunchman]

Sorry OuTCasT, I don't really understand what the problem is....can you try and explain it again?

Are you still using the same sql string? If not can you print the new one.

Ta muchly,

Dr B

[OuTCasT]

Sorry OuTCasT, I don't really understand what the problem is....can you try and explain it again?

Are you still using the same sql string? If not can you print the new one.

Ta muchly,

Dr B

hehe ok here goes.

I have the 4 drop down list boxes on the page.
they are not bound to the db they are static values but equal to what is in the db.
ok here is the query that im using.

[CODE=sql]SELECT * 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 ([field] = CASE WHEN @field IS NOT NULL THEN @field ELSE [field] END) AND ([gender] = CASE WHEN @gender IS NOT NULL THEN @gender ELSE [gender] END))[/CODE]

Ive got a page that lets users load there cv information.
and then i have a page that uses the dropdownlistbox es to search for the information and populates a gridview with that information, the gridview is selectable.

Now the problem is i have to use all 4 dropdownlist boxes in order to get the data to display.
We thought i was the dropdownlist boxes not passing " NULL " to the database.
But when i was checkin the page and searching with the dropdownlist boxes i saw that the SQL QUERY is LOOKING for NULL values in the database.....th ats not wat i want.
because most users enter all criteria on the cv...
im searching for

Gender:
Field of study:
Race:
Location:

and when i was testing i saw if i entered gender, race and location it would bring up 2 names....becaus e those name had null values as there field of study so i came to the conclusion that the query was indeed looking for null values in the database.....an d when i was leaving the dropdownlists values null it was not bring back information because there is no one in the database that has null values for gender, field of study, race and location.

what i want is for example :
say i choose

Gender: Male
Field of Study: Accounting
Race: "null"
Location: Queensland

Then i want all males who do accounting and live in queensland..... ..but i cant seem to get that right...

another example

Gender:"null"
Field of study:"null
Race: "null"
Location:"null"

if i selected something like that then i would like ALL the records in that table to be returned into the gridview...

PLEASE HELP......lol

[DrBunchman]

How do you run your query? Is it in a stored procedure or do you define it as a sql string and run it from your code?

[OuTCasT]

How do you run your query? Is it in a stored procedure or do you define it as a sql string and run it from your code?

hi Dr B

the query is run on the click event of the submit button and the query is binded to the gridview.

[OuTCasT]

How do you run your query? Is it in a stored procedure or do you define it as a sql string and run it from your code?

if i go through the datasource wizard of the gridview and insert my query string just before finishing u can TEST QUERY.... and when i test it in there it works 100%....even with no values.....i dont get it.

[OuTCasT]

hi Dr B

the query is run on the click event of the submit button and the query is binded to the gridview.

Dr B

i found the error.

there was something wrong with the settings for the dropdownlist boxes....CONVER TSTRINGTONULL = true

and i changed the field variable from EMPTY to STRING
and its working 100%

thanks for ur help guy