|
| |
|
|
MonsterBudgie
Posts: 2
Joined: 1/9/2004
Status: offline
 |
One submit form to search multiple fields? - 1/9/2004 14:19:34
Is there any FrontPage/ASP guru out there that can help me sort my database results page out? Here is the link:
Article Search
I'm trying to get this page to search several fields of an articles database. I want to search the title, content, and topic fields. I've managed to do this, but FrontPage insists on inserting a search form for each field. I would like to be able have only one form that users could use to submit a search of all three fields... Anybody know how I go about doing this? Thank you in advance for your help...
|
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: One submit form to search multiple fields? - 1/9/2004 14:29:53
Welcome to OutFront.
To do this, open your current page (Make a back-up first), delete two of the text boxes. Then, determine the form field name of the remaining text box, suppose it is "title". Go into HTML view and find your SQL. You will want to change it in the gray code, or, to make it easier diet the code (you can read about the diet here: http://www.outfront.net/spooky/adv_drw_diet.htm)
Anyway, your current SQL probably looks like this:
fp_sQry="SELECT * FROM YourTable WHERE title='::title::' OR content='::content::' OR topic='::topic::'
Since you now only have one form field, title, change the SQL to this:
fp_sQry="SELECT * FROM YourTable WHERE title='::title::' OR content='::title::' OR topic='::title::'
Now you will get results where the search term appears in either the title, topic or content.
Does that make sense?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
MonsterBudgie
Posts: 2
Joined: 1/9/2004
Status: offline
|
RE: One submit form to search multiple fields? - 1/9/2004 15:03:43
How big is your head? You must have a very big brain 'cuz that worked! Thank you so much for your help- I've been agonizing over that page for awhile now......
| |
|
|
|
SharonR
Posts: 3
Joined: 3/21/2002
From: Mason City IA USA
Status: offline
|
RE: One submit form to search multiple fields? - 4/6/2005 11:25:46
Hello, I am jumping in on this thread because I thought I could apply the solution to my search page. However, when I made the changes indicated in the solution, I find that FP won't make any of the changes to the code. I save, and then the new code just disappears and it is back to the original?
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/7/2005 16:04:52
Duane!! This is great! It really is. You've saved me alot of time. One page, one search, one results page....dang!
Now, is it possible to take it a step further? LOL...
How about one search box, but you can put the search term in, and possibly an "and" in there? is this possible?
Say someone wanted to look up all smiths with a zip of 92074...is it possible to do it so its ----smith AND 92074---
Even if not this is a great bit of info thats saving me alot of time.
Thanks again Duane.
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/7/2005 17:19:35
Hmmmm...this is getting interesting...for me..not sure if it is for you..lol.
How could i place a dropdown with all the field names in it, with a searchbox. then someone can pick the field they want to search on, and fill in the box so it searches just for that field? is that possible?
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 9:35:03
Does this sound logical at all? Since i dont know how to really program anything indepth, I usually find a work around that works..lol.
But, if we wanted to do a drop down of fields, with a search box next to it to put in a search term, to search a field we choose from the drop down..does this make sense at all? If so, how can i do it?
1. create a dropdown of field names, this FIELDS.
2. create a search box like i have now from one of the fields.
3. sql would be something like
Where ('::fields::' = (acct_zip, acct_main_phone, acct_state, acct_zip))
I know this isnt right, but you get the idea. The chosen field name would be used for the search term in the search box. How would i do this? It seems logical in some sense.
The selected field would be the field....and it would search the term used in the box.
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 11:03:07
I think I know what you are saying, but am not sure...are you saying a drop down listing all of your table fields (ex, address, zip code, state, first name, last name) and the search box would be search by whatever they put in (ex, drop down would select first name, user would then search for dave, and it would show all the results for dave in first name)
is that right? if it is, I think I can help ya :)
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 11:27:39
Exactly what im talking about. Thanks DZ...
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 12:04:04
Ok, to get a dropdown for your fields (or columns, whichever you wish to call it) you can do it the old fashioned way of <option value="first name">First Name</option> where "first name" is your exact name of the column...or, if there is a lot of them, you can use a quich script as follows:
<%
DIM myDSN, myConn, mySQL, myTempRS, F
connection=request.querystring("Connection")
mySQL = "SELECT * FROM Table"
myDSN = Application("database_ConnectionString")
Response.write("<select size='1' name='FieldName'><option></option>" & VbCrLf)
set myConn=server.createobject("adodb.connection")
myConn.open myDSN
set myTempRS=myConn.execute(mySQL)
For Each F In myTempRS.Fields
Response.write("<option>" & F.Name & "</option>" & VbCrLf)
Next
myTempRS.close
Set myTempRS = nothing
myConn.close
Set myConn = nothing
Response.write("</select>" & VbCrLf)
%>
The select statement will change, basically whatever you put in there to display will be the column headings.
The bolded database is your connection...whatever you called it in your front page.
Your search box will stay the same, for this purpose, lets call the name of it SearchField.
Now, the next page you will want to display your search results...the sql statement will look like this:
<%
FieldName= Replace(Request("FieldName"),"'","''")
If FieldName= "" then FieldName= "First Name" -(this is optional)
fp_sQry="SELECT * FROM Table WHERE (&FieldName&='::SearchField::')"
I am guessing the second sql statement won't work at the &FieldName& because I am unsure of the exact syntax, but give that a whirl :)
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 13:53:42
Think Im getting closer...here's the error i get. The code you gave me grabbed all of my fields. I left the field name as "FIELDNAME". The search box I called searchterm
my sql looks like
<%
FieldName= Replace(Request("FieldName"),"'","''")
fp_sQry="SELECT * FROM unicontacts WHERE (&fieldname& = '::Searchterm::')"
The error I get is
Syntax error (missing operator) in query expression '(&fieldname& = '954')'.
i dont know why its not including the field name. Any help?
Thanks, this is great.
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 14:56:03
yup, thats the place I thought an error might come. Its got something to do with parenthees somewhere (I am so bad with those)...try this though:
fp_sQry="SELECT * FROM unicontacts WHERE ("&fieldname& = '::Searchterm::')
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 15:47:34
quote:
ORIGINAL: dzirkelb1
yup, thats the place I thought an error might come. Its got something to do with parenthees somewhere (I am so bad with those)...try this though:
fp_sQry="SELECT * FROM unicontacts WHERE ("&fieldname& = '::Searchterm::')
quote:
fp_sQry="SELECT * FROM unicontacts WHERE ("&fieldname& = '::Searchterm::')
Yep, it was quote marks. I just needed one before and after, not just before as you had it.
Thanks guy, i really appreciate this, I'm surprised, it even works with date fields...go figure.
Thanks again....I really do appreciate teh help...now all i have to do is see where i messed up my code. I put it on a diet manually and think i got rid of an include somewhere that i shouldnt have because it shows the first page, and when i go to scan to the next results page, i get an error...
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 16:00:54
post the code and we'll see what we can find :)
glad it works! However, this may produce errors when you try to mismatch text fields with numbers and dates and whatnot...meaning, if you choose from the dropdown a zipcode, and search by a number, it is going to try a text to a number field (thats assuming the zip code is set to a number field)
To get rid of that, we can do an if then statement and create a variable...highlighted in bold below is the additions:
FieldName= Replace(Request("FieldName"),"'","''")
If FieldName="ZipCode" then
SearchTerm="::SearchTerm::"
else
If FieldName="DateField" then
SearchTerm="#::SearchTerm::#"
else
SearchTerm="'::SearchTerm::'"
end if
end if
fp_sQry="SELECT * FROM unicontacts WHERE ("&fieldname&" = '"&SearchTerm&")"
That should put the correct syntax in the field depending on what access is stored as.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 16:15:09
actually, they're all text fields. but thanks, im copying the code and putting it away...i may need it by next week..lol.
Also, i found it wasnt the diet that keeps me from going to thenext page of my search results. I think i have to pass the fieldname variable to thenext page because when i click the arrow to go to the next page it says...
Syntax error (missing operator) in query expression '( = 'boca raton')'.
so i guess that means its not passing it to the next page of resulst correct?
Thanks.
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 16:19:38
hrmm, that more than likely is the problem, so lets try to modify it a bit by making that field into a cookie:
Response.Cookies("FieldName")=Replace(Request("FieldName"),"'","''")
fp_sQry="SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::')
see if that works
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 16:34:52
RIGHT ON THE MONEY BUDDY!!
Very cool. Im sorry im such a pain. I do so many things I dont have time to really learn all the ins and outs of asp, etc. Im trying..believe me....lol.
Thanks again, you just made my life much easier and everyone using this.....
Enjoy your weekend DZ....
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 16:36:04
So glad it worked..i went out for a smoke and I thought it wouldn't...test it a couple times and make sure it does...hope it does!
oh ya, going to the hometown ballgame tonight, drink a few beers, play RISK...good old jolly weekend :)
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 16:48:29
Sounds good...enjoy!!
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 16:52:42
Im sorry...but now im thinking..hey, what if i could do an AND and select a second field from a drop down..is that possible or is that something totally different...I dont know how to add the second in the sql.....hope Im not asking too much..if i am..tell me to go away...but im on a roll!! lol..or shoudl i say, you're on a roll.
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 17:00:27
sure, not sure if i'm completely following though...would you like to do something like:
where zip code = 44444 AND 55555
or whoudl you like
Where zip code = 44444 AND first name = dave
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 17:03:17
where zip code = 444 and first name = dave
thats it....thanks in advance..i appreciate it.
mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 17:10:43
copy and paste the same code for the first one so there are two display boxes...lets call the second ones:
FieldName2
SearchTerm2
Response.Cookies("FieldName")=Replace(Request("FieldName"),"'","''")
Response.Cookies("FieldName2")=","&Replace(Request("FieldName"),"'","''")
if trim(request.form("FieldName2"))="" then
sql="""&"SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::')"
else
sql="""&"SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::') OR ("&Request.Cookies("FieldName2")&" = '::Searchterm2::')"
end if
fp_sQry=sql
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 17:17:37
I dont even get an error, I get Page Cannot be displayed. Its ok..i appreciate it..i dont want to take up all of your time on this guy. if you get the chance, great..if not..you've already helped tremendously..I owe you big time...
thanks again..
mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 17:21:02
whats the error of the page can't be displayed? make sure friendly errors are turned off:
tools>internet optoins> advanced > under browsing, uncheck friendly http errors or something like that
also, post he code :)
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 17:26:23
try changing:
fp_sQry=sql
to
fp_sQry="&sql &"
I'm out for the day, i'll try to check it when i get home though
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 18:03:30
Here you go and thanks. Also, one response line says:
Response.Cookies("FieldName2")=","&Replace(Request("FieldName"),"'","''")
Ive done it this way as well:
Response.Cookies("FieldName2")=","&Replace(Request("FieldName2"),"'","''")
The error is:
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/contacts/searchplace3.asp, line 232
sql="""&"SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::')"
---------^
MY COMMENT: (up arrow is under SELECT)
The code is:
<%
Response.Cookies("FieldName")=Replace(Request("FieldName"),"'","''")
Response.Cookies("FieldName2")=","&Replace(Request("FieldName"),"'","''")
if trim(request.form("FieldName2"))="" then
sql="""&"SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::')"
else
sql="""&"SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::') OR ("&Request.Cookies("FieldName2")&" = '::Searchterm2::')"
end if
fp_sQry="&sql &"
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 18:07:24
My bad, try this
Response.Cookies("FieldName")=Replace(Request("FieldName"),"'","''")
Response.Cookies("FieldName2")=Replace(Request("FieldName2"),"'","''")
if trim(request.form("FieldName2"))="" then
sql="""&"SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::')"
else
sql="""&"SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::') OR ("&Request.Cookies("FieldName2")&" = '::Searchterm2::')"
end if
fp_sQry="&sql&"
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/8/2005 19:08:23
Sorry, but same error......IM gonna owe you a case to bring with you to the ball game next time..lol
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
 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 
