One submit form to search multiple fields? (Full Version)

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



Message

MonsterBudgie -> 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 -> 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?



MonsterBudgie -> 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 -> 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?




rdouglass -> RE: One submit form to search multiple fields? (4/6/2005 11:29:31)

You'll need to put the DRW on a Spooky Diet:

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



mfalk -> 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



mfalk -> 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



mfalk -> 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



dzirkelb1 -> 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 -> RE: One submit form to search multiple fields? (4/8/2005 11:27:39)

Exactly what im talking about. Thanks DZ...

Mark



dzirkelb1 -> 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 -> 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




dzirkelb1 -> 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 -> 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



dzirkelb1 -> 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 -> 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



dzirkelb1 -> 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 -> 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



dzirkelb1 -> 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 -> RE: One submit form to search multiple fields? (4/8/2005 16:48:29)

Sounds good...enjoy!!

Mark



mfalk -> 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



dzirkelb1 -> 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 -> 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



dzirkelb1 -> 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 -> 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



dzirkelb1 -> 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 -> 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 -> 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 &"



dzirkelb1 -> 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 -> 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



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375