T
Taylor
I have a search feature that sends the selections and search text from
a form to a stored procedure to query based on the variables. The
problem i run into is with the multiple checkboxes. On this form i
have 5 checkboxes. These 5 checkboxes will send a 1 or 0 depending on
whether they are selected or not. When i create my queries in the sp
i need to evaluate what checkboxes were selected and then use those
values to bring back the proper data results. However, there are far
too many combinations i can have with 5 checkboxes
(Ex. if checkbox1 = 1 and checkbox2 = 1
begin
sql statment
end
else if checkbox1 = 1 and checkbox3 = 1
begin
sql statement
end
....and so on)
I know that writing all of those possiblities with if then statements
is far to much and not efficient. I need a way where i can create the
sql query dynamically based on what checkboxes the user selects. This
way, my sql query is no bigger then it has to be.
Currently i'm just using this format
Select *
From table
Where ((TableValue1 = 1 and Checkbox1 = 1) OR
(TableValue2 = 1 and Checkbox2 = 1) OR
(TableValue3 = 1 and Checkbox3 = 1))
This statment will work fine for one checkbox by bringing back data
when a checkbox = 1 and the tablevalue = 1 as well. When i try more
then one though it just adds on the data. Like...
Query:
Select *
From table
Where ((TableValue1 = 1 and Checkbox1 = 1) OR
(TableValue2 = 1 and Checkbox2 = 1) OR
(TableValue3 = 1 and Checkbox3 = 1))
Data brought back:
id tablevalue1 tablevalue2 tablevalue3
1 1 0 1
2 1 1 0
3 1 1 0
Thats because of the OR operator in the query. However if i were to
use AND it would only work when those two, three, or however many
checkboxes where all selected. The problem above with that result set
is that i only want data brought back that the user told the search to
search exactly for. If the user wants all data brought back that has
the first and second table values equaling one then BOTH of the table
values have to equal one. My current query, because of the OR
operator, is bringing back data as long as the row has tablevalue1
equaling a 1 or tablevalue2 equaling a 1, it doesn't care if they both
do or not.
So if someone can tell me how to build a query based on what boxes
were selected without having to create a ton of if then statements to
get the job done, that's be great. I hope after that long winded
explanation someone can help me, point me in the right direction, or
tell me if a post like this is already out there. I would greatly
appreciate any help or information any can offer.
Thanks,
Taylor
a form to a stored procedure to query based on the variables. The
problem i run into is with the multiple checkboxes. On this form i
have 5 checkboxes. These 5 checkboxes will send a 1 or 0 depending on
whether they are selected or not. When i create my queries in the sp
i need to evaluate what checkboxes were selected and then use those
values to bring back the proper data results. However, there are far
too many combinations i can have with 5 checkboxes
(Ex. if checkbox1 = 1 and checkbox2 = 1
begin
sql statment
end
else if checkbox1 = 1 and checkbox3 = 1
begin
sql statement
end
....and so on)
I know that writing all of those possiblities with if then statements
is far to much and not efficient. I need a way where i can create the
sql query dynamically based on what checkboxes the user selects. This
way, my sql query is no bigger then it has to be.
Currently i'm just using this format
Select *
From table
Where ((TableValue1 = 1 and Checkbox1 = 1) OR
(TableValue2 = 1 and Checkbox2 = 1) OR
(TableValue3 = 1 and Checkbox3 = 1))
This statment will work fine for one checkbox by bringing back data
when a checkbox = 1 and the tablevalue = 1 as well. When i try more
then one though it just adds on the data. Like...
Query:
Select *
From table
Where ((TableValue1 = 1 and Checkbox1 = 1) OR
(TableValue2 = 1 and Checkbox2 = 1) OR
(TableValue3 = 1 and Checkbox3 = 1))
Data brought back:
id tablevalue1 tablevalue2 tablevalue3
1 1 0 1
2 1 1 0
3 1 1 0
Thats because of the OR operator in the query. However if i were to
use AND it would only work when those two, three, or however many
checkboxes where all selected. The problem above with that result set
is that i only want data brought back that the user told the search to
search exactly for. If the user wants all data brought back that has
the first and second table values equaling one then BOTH of the table
values have to equal one. My current query, because of the OR
operator, is bringing back data as long as the row has tablevalue1
equaling a 1 or tablevalue2 equaling a 1, it doesn't care if they both
do or not.
So if someone can tell me how to build a query based on what boxes
were selected without having to create a ton of if then statements to
get the job done, that's be great. I hope after that long winded
explanation someone can help me, point me in the right direction, or
tell me if a post like this is already out there. I would greatly
appreciate any help or information any can offer.
Thanks,
Taylor