|
| |
|
|
dzirkelb1
Posts: 1296
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
 |
RE: One submit form to search multiple fields? - 4/10/2005 18:37:43
try this, I wish to see what it is being put into the variable sql, I think we're missing some " or something:
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
response.write(sql)
fp_sQry="SELECT & FROM unicontacts"
|
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 9:27:46
Still the same error:
sql="""&"SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::')"
---------^
"Once again, the up arrow indicator is pointing to SELECT (in case it doesnt show properly on this message)"
Thanks again.
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 9:58:32
Should be:
sql="SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" = '::Searchterm::') OR ("&Request.Cookies("FieldName2")&" = '::Searchterm2::')"
Also, I would assume you are adding a second set of criteria to narrow the search, so you will probably want AND not OR.
_____________________________
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.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 10:23:43
Duane, that worked, and the fp_sQryhad to be =sql
Here's the whole thing if anyone needs it. this was a great feature, and thanks DZ for all your hard work and Duane thanks for getting it completed. I really appreciate all the efforts.
When it was UNDIETED i could sort by column using SORTCOLUMN in my hyperlink, It doesnt work now, but is there a way to make the columns stills sort?
Here's the code for two drop down search.
You need two search drop downs with fieldnames included as you would use for your normal search (in this example called FIELDNAME and FIELDNAME2) and two search boxes, (searchterm and searchterm2).
<%
Response.Cookies("FieldName")=Replace(Request("FieldName"),"'","''")
Response.Cookies("FieldName2")=Replace(Request("FieldName2"),"'","''")
if trim(request.form("FieldName2"))="" then
sql="SELECT * FROM TABLENAME WHERE ("&Request.Cookies("FieldName")&" LIKE '::Searchterm::%')"
else
sql="SELECT * FROM TABLENAME WHERE ("&Request.Cookies("FieldName")&" LIKE '::Searchterm::%') AND ("&Request.Cookies("FieldName2")&" LIKE '::Searchterm2::%')"
end if
response.write(sql)
fp_sQry=sql
_____________________________
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/11/2005 11:34:31
For the sort problem, I used a hyperlink at the top of each column that goes to my results page, and used sortcolumn as the fieldname and searchterm and searchterm2 as my other fields. in the paramaters, it looks like this for sorting last name
<a href="searchplace3.asp?sortcolumn=cont_last_name&searchterm=<%=Request("searchterm")%>&searchterm2=<%=Request("searchterm2")%>">
I added "ORDER BY ::SORTCOLUMN:: ASC" to the end of the sql statements.
The results are this....if you're searchterm or searchterm2 are also the sortcolumn, it returns ALL records of that searched field.....such as, if i did a search for SMITH and NY, i get all the smiths in NY, but when i click on my sort hyperlink it gives me ALL SMITHS.
If i do the same to other fields, I get no records returned.
any ideas?
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 11:58:30
When you click the link to sort you are essentially reloading the page. As a result, you will need to resend all your form fields.
<a href="searchplace3.asp?sortcolumn=cont_last_name&searchterm=<%=Request("searchterm")%>&searchterm2=<%=Request("searchterm2")%>&FIELDNAME=<%=Request("FIELDNAME")%>&FIELDNAME2=<%=Request("FIELDNAME2")%>">
_____________________________
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.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 12:04:35
quote:
<a href="searchplace3.asp?sortcolumn=cont_last_name&searchterm=<%=Request("searchterm")%>&searchterm2=<%=Request("searchterm2")%>&FIELDNAME=<%=Request("FIELDNAME")%>&FIELDNAME2=<%=Request("FIELDNAME2")%>">
Duane it does the same thing, it gives me all the ones with the last name...here is what it gives me:
searchplace3.asp?sortcolumn=cont_last_name&searchterm=smith&searchterm2=ny&FIELDNAME=cont_Last_Name&FIELDNAME2=acct_State
hope that helps. I did that before and it didnt work either..hmmmm....
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 12:06:18
Are your search form and results on the same page?
_____________________________
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.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 12:09:57
no, my search form is on one, and my results on another.
One difference now is, Even though I get all of the smiths when i do the sort (not just the NY ones), I added the same code to another column, which now gives me ALL the smiths but with the correct sorting for that field,but it doesnt maintain the original SEARCHTERM2...or so it looks like.
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/11/2005 12:14:21
so you know, i just verified, when sorting, it doesnt maintain searchterm2
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 12:16:37
What happens if you don't use cookies? Meaning:
sql="SELECT * FROM TABLENAME WHERE (::FieldName:: LIKE '::Searchterm::%')"
else
sql="SELECT * FROM TABLENAME WHERE (::FieldName:: LIKE '::Searchterm::%') AND (::FieldName2:: LIKE '::Searchterm2::%')"
end if
_____________________________
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.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 12:28:11
I get the same thing.
Now, what i have been doing is adding Order by ::sortcolumn:: ASC to the end of the SQL statements...it looks like this, is this correct?
sql="SELECT * FROM unicontacts WHERE (::FieldName:: LIKE '::Searchterm::%') order by ::sortcolumn:: ASC"
else
sql="SELECT * FROM unicontacts WHERE (::FieldName:: LIKE '::Searchterm::%') AND (::FieldName2:: LIKE '::Searchterm2::%') Order by ::sortcolumn:: ASC"
_____________________________
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/11/2005 12:58:10
My apologies, I just realized I should have gotten rid of my cookies before i tried what you gave me...here is the error when you try to sort without cookies
Syntax error (missing operator) in query expression '( LIKE 'smith%')'.
This is the first searchterm (cont_last_name)
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 13:02:33
Can you post your current code?
_____________________________
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.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 13:05:20
Here you go
<%
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")&" LIKE '::Searchterm::%') order by ::sortcolumn:: ASC"
else
sql="SELECT * FROM unicontacts WHERE ("&Request.Cookies("FieldName")&" LIKE '::Searchterm::%') AND ("&Request.Cookies("FieldName2")&" LIKE '::Searchterm2::%')order by ::sortcolumn:: ASC"
end if
fp_sQry=sql
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 13:23:40
And if you replace the above with simply:
fp_sQry="SELECT * FROM unicontacts WHERE ::FieldName:: LIKE '::Searchterm::%' AND ::FieldName2:: LIKE '::Searchterm2::%' order by ::sortcolumn:: ASC"
_____________________________
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.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: One submit form to search multiple fields? - 4/11/2005 14:43:39
Excellent!!
Ok, i replaced what i had with what you gave me.....but it didnt work, then i added the response.cookie lines of fieldname and fieldname2 and used both searchterms and both fieldnames in the sort query and BOOM!! It works great.
Here's what i used for the sql:
<%
Response.Cookies("fieldname")=Replace(Request("fieldname"),"'","''")
Response.Cookies("fieldname2")=Replace(Request("fieldname2"),"'","''")
fp_sQry="SELECT * FROM unicontacts WHERE ::FieldName:: LIKE '::Searchterm::%' AND ::FieldName2:: LIKE '::Searchterm2::%' order by ::sortcolumn:: ASC"
Thanks guys, I really appreciate the effort and I promise to let this one die now!! 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 
