Search question?
 

Hello all!

I posted this question on the SQL boards, got some great answers, but I
thought I would also try here, since I'm using asp.net/vb.net.
I'll try to lay this out a simple as possible. I think before I was
approaching it the wrong way.
I have 3 ddl's that have values from my database. ddlSize, ddlColor and
ddlStyle.
If a user wants to search for a product, they select the following, "Small,
Red, Sportsman". So all the products that match this will show up.
But if the user selected "Small, Red, and then 'Any' from ddl style. It
should show all products that are Red and Small, but all Styles. Or if all
three were selected "Any", then it should show everything.
So my question is how can I write my SQL to say "SELECT ProductName AS
FROM ProdTbl
WHERE Color = *"
I know I can't use *, it doen't work. So I just want if "Any" is selected,
it will pick everthing.
Or maybe there is a beter way to do this. I'm thinking about job sites that
have search engines, select any job, or all job, or all locations. Giving the
user the ability to see all the products, or maybe to see all red products.

I hope this make sence, thank for your help!!

Rudy

Re: Search question?
 

I wouldn't use a where condition if 'any' is the selected option. If you
must, you could do something cheesy like Color = blah OR 1 = 1...


"Rudy" <Rudy@discussions.microsoft.com> wrote in message
news:29339EE7-4179-4F11-A23A-D20B25AA233B@microsoft.com...
> Hello all!
>
> I posted this question on the SQL boards, got some great answers, but I
> thought I would also try here, since I'm using asp.net/vb.net.
> I'll try to lay this out a simple as possible. I think before I was
> approaching it the wrong way.
> I have 3 ddl's that have values from my database. ddlSize, ddlColor and
> ddlStyle.
> If a user wants to search for a product, they select the following,
> "Small,
> Red, Sportsman". So all the products that match this will show up.
> But if the user selected "Small, Red, and then 'Any' from ddl style. It
> should show all products that are Red and Small, but all Styles. Or if all
> three were selected "Any", then it should show everything.
> So my question is how can I write my SQL to say "SELECT ProductName AS
> FROM ProdTbl
> WHERE Color = *"
> I know I can't use *, it doen't work. So I just want if "Any" is selected,
> it will pick everthing.
> Or maybe there is a beter way to do this. I'm thinking about job sites
> that
> have search engines, select any job, or all job, or all locations. Giving
> the
> user the ability to see all the products, or maybe to see all red
> products.
>
> I hope this make sence, thank for your help!!
>
> Rudy

RE: Search question?
 

Hi Rudy,

If you want 'any color', it should look like

SELECT ProductName FROM ProdTbl WHERE Color LIKE '%'

HTH

Elton Wang
elton_wang@hotmail.com





"Rudy" wrote:

> Hello all!
>
> I posted this question on the SQL boards, got some great answers, but I
> thought I would also try here, since I'm using asp.net/vb.net.
> I'll try to lay this out a simple as possible. I think before I was
> approaching it the wrong way.
> I have 3 ddl's that have values from my database. ddlSize, ddlColor and
> ddlStyle.
> If a user wants to search for a product, they select the following, "Small,
> Red, Sportsman". So all the products that match this will show up.
> But if the user selected "Small, Red, and then 'Any' from ddl style. It
> should show all products that are Red and Small, but all Styles. Or if all
> three were selected "Any", then it should show everything.
> So my question is how can I write my SQL to say "SELECT ProductName AS
> FROM ProdTbl
> WHERE Color = *"
> I know I can't use *, it doen't work. So I just want if "Any" is selected,
> it will pick everthing.
> Or maybe there is a beter way to do this. I'm thinking about job sites that
> have search engines, select any job, or all job, or all locations. Giving the
> user the ability to see all the products, or maybe to see all red products.
>
> I hope this make sence, thank for your help!!
>
> Rudy