search with multiple values in one text field (Full Version)

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



Message

hessfirm -> search with multiple values in one text field (4/7/2005 0:21:16)

I've seen an old thread on this, but no solution was developed that i saw.

I am using a single text field to search a SQL table via the DRW. I want to allow users to input multiple values in the text field separated by commas...and the values are not numeric...

the sql in the DRW is county IN ::county::

But how do i get the single quotes around each word? Most people will just type eg. sample1, sample2....in the text field. This will produce no records returned. It needs to be 'sample1', 'sample2'....But users don't know to type in the single quotes.

So how do i automate the surrounding single quotes? HELP!!!



rdouglass -> RE: search with multiple values in one text field (4/7/2005 8:38:56)

If you're sure they're being separated by commas, try using a REPLACE function:

<%
DIM myVar
myVar = "'" & Replace(Request.form("country"),",","','") & "'"
%>

Then in a dieted DRW use:

...country IN " & myVar & "...

However, there are more "accurate" ways by putting your words into an array and being more specific in your requests. If you're interested, let me know and I'll see if I can find a similar post. That's been done many times and it's actually quite straightforward IMO.

Anyways, hope it helps.



rdouglass -> RE: search with multiple values in one text field (4/7/2005 8:42:02)

Actually, here's a post with a very similar situation I answered earlier:

http://www.frontpagewebmaster.com/m-262072/tm.htm#262128



BeTheBall -> RE: search with multiple values in one text field (4/7/2005 9:46:50)

I may be wrong, but I believe IN only works with numeric fields. You will likely have to diet the code as shown here:

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

Once that is complete, go into the red code and replace the line that begins with:

fp_sQry =

with the following lines of code:

If trim(request.form("nameofsearchformfield"))&"" <> "" Then
varString = trim(replace(request.form("nameofsearchformfield"), "'","''"))
varString = Replace(varString, " ", ",")
arrString = Split(varString, ",")
mySQL = "SELECT * FROM tablebeingsearched WHERE fieldbeingsearched LIKE '%"&arrString(0)&"%' "
If Ubound(arrString)> 0 Then
For i = 1 to Ubound(arrString)
mySQL = mySQL & " AND fieldbeingsearched LIKE '%"&arrString(i)&"%' "
Next
End If
Else
mySQL = "SELECT * FROM tablebeingsearched"
End If
fp_sQry = mySQL

Replace the bold items with the actual names from your database and search form. The above will also replace spaces with a comma to cover users who may forget to add one.



rdouglass -> RE: search with multiple values in one text field (4/7/2005 10:14:10)

Duane,

That's essentially what my second post referred to. We ought to turn this post into an FAQ (at least if it solves the problem that is[;)]). It may already be one but it's a common question.

Whadya' think?



BeTheBall -> RE: search with multiple values in one text field (4/7/2005 10:42:10)

Agreed.



hessfirm -> RE: search with multiple values in one text field (4/8/2005 2:43:46)

the diet is working nicely. thanks!

The code, though, to insert single quotes isn't...Here is the DRW code after the diet and new code you gave. I am sure I am missing something.
The search form url is http://atlantanewhomesdirectory.com/TestPages/diettrialsearchform.htm
The results page is http://atlantanewhomesdirectory.com/TestPages/diettrial.asp

Also, if i am using multiple search form fields that query different fields in the table, how do i add them into this formula as well....For example, in the query below, i am searching using only 'builderbroker'...but what if i allow additional search form fields for different columns?

And does is matter that we use SQl versus mySQL?


<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
If trim(request.form("builderbroker"))&"" <> "" Then
varString = trim(replace(request.form("builderbroker"), "'","''"))
varString = Replace(varString, " ", ",")
arrString = Split(varString, ",")
mySQL = "SELECT * FROM Administration WHERE builderbrokerLIKE '%"&arrString(0)&"%' "
If Ubound(arrString)> 0 Then
For i = 1 to Ubound(arrString)
mySQL = mySQL & " AND builderbrokerLIKE '%"&arrString(i)&"%' "
Next
End If
Else
mySQL = "SELECT * FROM administration"
End If
fp_sQry =mySQL fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="DedServer"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td><%=FP_FieldVal(fp_rs,"ID")%></td>
<td><%=FP_FieldVal(fp_rs,"Bookmark")%></td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.046875