|
| |
|
|
wlp
Posts: 54
From: None
Status: offline
 |
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
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
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
Posts: 54
From: None
Status: offline
|
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
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
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
Posts: 54
From: None
Status: offline
|
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
Posts: 54
From: None
Status: offline
|
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
Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
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
Posts: 54
From: None
Status: offline
|
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
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 298
From: Louisville KY USA
Status: offline
|
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
^
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
