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

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



Message

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



BeTheBall -> 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.



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





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



BeTheBall -> 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")%>">



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



BeTheBall -> RE: One submit form to search multiple fields? (4/11/2005 12:06:18)

Are your search form and results on the same page?



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



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



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



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



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



BeTheBall -> RE: One submit form to search multiple fields? (4/11/2005 13:02:33)

Can you post your current code?



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



BeTheBall -> 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"



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



Page: <<   < prev  1 [2]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125