search on multiple fields (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

wlp -> search on multiple fields (3/27/2002 13:14:46)

Seems like a simple question but I can't firgure it out. I have in a database fields first_Name; last_name. I wish to, using a search from generated from DRW, search text in both of these fields using one field to enter the search query? How can I do this?

Thanks

John Stafford
WaterLine Productions
Where Sound Decsions Are Made



rdouglass -> RE: search on multiple fields (3/27/2002 14:33:36)

If you're using the DRW, set up the results with criteria for both first_name and last_name. Then you'd first need to put it (the code) on a diet:

http://www.outfront.net/spooky/adv_drw_diet.htm

Then you would change the query from something like:

fp_sQry="SELECT * FROM Table1 WHERE (First_Name = '::First_Name::' OR Last_Name = '::Last_Name::')"

to something like:

fp_sQry="SELECT * FROM Table1 WHERE (First_Name LIKE '%::Name::%' OR Last_name LIKE '%::Name::%')"

Then on your search form, delete the Last_Name textbox and re-name First_Name box to Name. This would search both fields for text contained in the Name textbox. This is actually a "contains" query. If you want an exact name match, your query would look something like:

fp_sQry="SELECT * FROM Table1 WHERE (First_Name = '::Name::' OR Last_Name = '::Name::')"

Does this make sense at all??

 



wlp -> RE: search on multiple fields (3/27/2002 14:42:50)

This is great advise thanks. I haven't been following the Spooky diet stuff very closely. You wouldn't happen to have a reply to my previous question would you? Re: searching using unique ID and the challenges with that?

Thanks

John Stafford
WaterLine Productions
Where Sound Decsions Are Made



rdouglass -> RE: search on multiple fields (3/27/2002 15:21:38)

Would you actually need to SEARCH on the ID number or just reference it? If you HAD to use 1 text box to search either ID, FirstName, or LastName, I would first test the text box for numerical data (to decide whether to use ID search or First/Last Name search. Using the example above and expanding on it put something like:

<% DIM strSQL
IF IsNumeric(Name) THEN
strSQL = "(ID = ::Name::)"
ELSE
strSQL ' "(First_Name LIKE '%::Name::%' OR Last_name LIKE '%::Name::%')"
END IF%>

then the query would look something like:

fp_sQry="SELECT * FROM Table1 WHERE " & strSQL

Haven't tested this but I have done similar type things and it seems to work OK. Hope it helps...

 



wlp -> RE: search on multiple fields (3/27/2002 21:40:53)

When I talk about searching for the ID I mean that I am udsing the IDin hyperlink from another page and posting it the new page where I had previouly setup search criteria for first_name, last_name, because I was searching for names from another page as well. I am sorry the site is not online I will try to explain a little better in a short while. maybe the question to ask is can I when using a hyperlink to search on an ASP page, configure a range of numbers fir the number field i.e.

music.asp?composer=1....1000



John Stafford
WaterLine Productions
Where Sound Decsions Are Made



wlp -> RE: search on multiple fields (3/27/2002 22:44:34)

I think I am getting closer just using the DRW. I have been putting values in the default portion of the DRW in database result properties / more options. I think I want to put a value of >=1 for the default so that if there are no valus being passed it will default to that which will display all of the records. Alternativly I would be arriveing at the ASP page from a hypewrlink with a specific value for the same field .As mentioned above. Can you suggest how I can put >=1 in the DRW default portion of the DRW in database results properties.

Make sense to you?

John Stafford
WaterLine Productions
Where Sound Decsions Are Made



Spooky -> RE: search on multiple fields (3/28/2002 16:42:32)

When using "LIKE" you will be able to use the default wildcard '%' to show all records, rather than use ID

§þððk¥
Database / DRW Q & A
VP-ASP Shopping cart
Spooky Login



wlp -> RE: search on multiple fields (3/29/2002 15:59:34)

Thanks all, I have not tried the LIKE using the ID field. I did however introduce an if statement so that if an ID is found it will use it if not it will look for the values inthe name fields. Also I have a small problem with the diet. When I remove the instructions FP issues to write the ASP code for the database connection and search parameters things are fine when I go deeperand remove the Webot stuff which writes the results I loose all of the database fields. So what I have done for m=bow is just eliminate the first part of hte FP code for the connection and search instructions . Displaying the results I have not touched. I will search the forum listings for the answer I am sure I will find the answer.

Thanks again.

J.

John Stafford
WaterLine Productions
Where Sound Decsions Are Made



jonance -> RE: search on multiple fields (9/27/2003 19:59:14)

Thanks for your help and commitment...here's the latest error.
http://axiom.nt3.npsis.com/searchtest.asp



Microsoft VBScript compilation error '800a0401'

Expected end of statement

/searchtest.asp, line 175

myQuerystring = myQuerystring & " LIKE '%::search::%' OR subcategory" myQuerystring = myQuerystring & " LIKE '%::search::%')"
----------------------------------------------------------------------^



jonance -> RE: search on multiple fields (9/27/2003 20:01:11)

A different error with the "neater" code....hope this helps

Microsoft VBScript compilation error '800a0400'

Expected statement

/searchtest.asp, line 180

end if
^



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625