Search with drop down list

[Guest]

Hello!

I have 4 diffrent drop downlist. I want a user to select a value from a
drop down list, and place it in a SELECT statement. How would I put that
value in the select statement. And if the user selects two items, one item
from two diffrent drop down list, how would I do that? Any suggestions would
be great!

TIA!!

Rudy

 

[Tarren]

Look up "sql injection attacks" and take necessary precautions in your code
to protect.

in C#
---------
string sPetType;
sPetType = ddPetTypes.SelectedValue;

string sSQL = " SELECT * FROM tblPets WHERE pettype = '" + sPetType + "'";


and in VB .NET
-------------
dim sPetType as string
sPetType = ddPetTypes.SelectedValue

dim sSQL as string = " SELECT * FROM tblPets WHERE pettype = '" & sPetType &
"'"

Note the single qotes since in this case I am assuming that sPetType is a
string and would need the single quotes in the SQL

 

[Guest]

Hi Tarren!
Thank you for the info, worked out great! I'm working in VB.net, appreciate
both versions. I plan to use all store procedures in my code before I go
live. For now, it's easier doing it this way instead of always changeing the
SP around.

I do have one more quick question, If I run ths select statement, how can
have the results show up on another page. For now, I have the seach fields
on the same page as the results, but I want to move my search panel to a
diffrent page.

Thank you for your help!!!

Rudy

 

[Yunus Emre ALPÖZEN [MCAD.NET]]

Use a sql trick something like this in a stroed procedure

@selectedNumber as int
@selectedText as nvarchar(20)

select * from table_name
where (@selectedNumber=-1 OR numericColumn=@selectedNumber)
and (@selectedText='' or textColumn=@selectedText)

 

[Tarren]

Rudy:

What I have done is have the drop downs fire a response.redirect. So it
would look something like this

VB .NET

PAGE WITH SEARCH PANEL


dim sQueryString as string
sQueryString = "?pettype=" & Server.URLEncode(ddPetTypes.SelectedValue)

Response.Redirect("otherpage.aspx" & sQueryString)

THEN IN THE OTHER PAGE YOU CAN PUT IN PAGE_LOAD METHOD

if Request.QueryString("pettype").Length > 0 then 'check to make sure this
page was called with the querystring
dim sSQL as string = " SELECT * FROM tblPets WHERE pettype = '" &
Server.URLDecode(Request.QueryString("pettype") & "'"
end if


You need the URLEncode and URLDecode so you can pass spaces and non
alphanumeric characters through the querystring and then turn them back into
usable values for a string match in the SQL

Also, make sure the querystring element is present, which is why I put the
if block. In VB .NET you can check for length, and it'll come back as zero
if the element isn't there.

In C# you have to check for != null since you cannot check the length of an
element that does not exist and C# is more strict on that point.

For some of the apps I write, I make the Search bar a usercontrol and then
handle the response.redirect from within the user control so I only write
the querystring prep once and have it Response.Redirect, from anywhere in my
site.

Hope this helps!