Search one field with mulitple criteria (Full Version)

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



Message

mfalk -> Search one field with mulitple criteria (4/6/2005 11:42:03)

Hi again everyone.

I want to have someone search one field for mulitiple search words and then send to a csv. I created the csv (but its straight sql so i also need an sql statement).

We have an organization where each member has an id. They want to put in as many id's (manually) as they want and have it output to the csv to do a mail merge.

How do i let them enter 1, 10, 100 id's with one field?

And if you could, what is the sql statement i would need on the results page? Im in a bind here..and this would help alot.

Thanks in advance...

Mark



rdouglass -> RE: Search one field with mulitple criteria (4/6/2005 11:46:23)

You using a DRW or just ASP?



mfalk -> RE: Search one field with mulitple criteria (4/6/2005 11:50:30)

on this page, just asp as it is a results page that sends to a csv file.



mfalk -> RE: Search one field with mulitple criteria (4/6/2005 11:52:07)

my current sql statement is
sql = "SELECT * FROM unicontacts WHERE ('"&Replace(Request.Form("up"),",","','") & "')"

but im using a multiple selection drop down that shows every id so they can just choose them....then sends to the above statement.

IM getting an error that says
[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression '('1130',' 1134',' 1137',' 1140',' 1148',' 1152',' 1156',' 1159',' 1165',' 1166',' 11676')'.

so it is choosing the right id's just wont process...



rdouglass -> RE: Search one field with mulitple criteria (4/6/2005 12:36:50)

quote:

sql = "SELECT * FROM unicontacts WHERE ('"&Replace(Request.Form("up"),",","','") & "')"


I don't see any field you're trying to grab. However, if you're searching a number field, try this one:

sql = "SELECT * FROM unicontacts WHERE myNumberField IN ('"&Request.Form("up") & "')"

If it is a text field, try this:

DIM myCriteria
myCriteria="("
IF trim(Request.form("up)&"") > "" THEN
myTempArray = split(trim(Request.form("up)&"")",")
FOR i = 0 TO ubound(myTempArray,1)
myCriteria = myCriteria & "(myTextField = '" & myTempArray(i) & "') OR "
NEXT
myCriteria = left(myCriteria,len(myCriteria)-4) & ")"

Then use your sql line like so:

sql = "SELECT * FROM unicontacts WHERE " & myCriteria

That help any?



mfalk -> RE: Search one field with mulitple criteria (4/6/2005 12:49:08)

Ok, it doesnt work.

Right now, the search is conducted with a multiple selection drop down so they can just choose all of the ID's they want (field name "UP", which is a text field) instead of manually entering each one.

I send to a page that creates a csv for all of the selected ID's (fieldname "UP").

The sql statement:
sql = "SELECT * FROM unicontacts WHERE ("&Request.Form("up") & ")"

Gives the error:
Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression '(1130, 1134, 1137, 1140)'.


The numbers you see, are the ID's that were selected in the multiple choice drop down.

Thanks,a nd sorry if im being difficult.

Mark



rdouglass -> RE: Search one field with mulitple criteria (4/6/2005 13:04:28)

quote:

sql = "SELECT * FROM unicontacts WHERE ("&Request.Form("up") & ")"


What field are you searching? If unicontact is the table name what is the field name you're looking in?

sql = "SELECT * FROM unicontacts WHERE UP IN ("&Request.Form("up") & ")"

If it's text, did you look at my previous post closely?



mfalk -> RE: Search one field with mulitple criteria (4/6/2005 13:15:53)

Yes I did try the one for text and it didnt work.

The field name is UP and it is a text field.

search form is multichoice drop down that is created from field name UP. after choosing the ones they want, they click submit and it goes to the results page. The results page should tell it these are the ID numbers chosen from fieldname UP and then processes the csv.

Mark



rdouglass -> RE: Search one field with mulitple criteria (4/6/2005 13:58:19)

Can you post the code you're using? AFAIK with a text field, you'll have to do some kind of recursive separation of the 'up' field like my example. Post what you have and maybe I can see something different.



mfalk -> RE: Search one field with mulitple criteria (4/6/2005 14:11:54)

This is the search form code: 
<form method="POST" action="uplabels.asp">
                    <nobr>
                    <!--webbot bot="DatabaseRegionStart" s-columnnames="up" s-columntypes="202" s-dataconnection="contacts" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="up" s-menuvalue="up" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="up" s-criteria s-order s-sql="SELECT DISTINCT [up] FROM [unicontacts] ORDER BY [up] ASC" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="9" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Database</span> " startspan b-InForm="TRUE" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="FALSE" --><!--#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 %>
<%
fp_sQry="SELECT DISTINCT [up] FROM [unicontacts] ORDER BY [up] ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="contacts"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="up"
fp_sMenuValue="up"
fp_sColTypes="&up=202&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=9
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="11422" --><select NAME="up" SIZE="10" multiple>
                    <!--webbot bot="AspInclude" clientside u-incfile="../_fpclass/fpdbrgn1.inc" startspan --><!--#include file="../_fpclass/fpdbrgn1.inc"-->
                    <!--webbot bot="AspInclude" endspan i-checksum="8126" -->
                    <option><%=FP_FieldHTML(fp_rs,"up")%></option>
                    <!--webbot bot="AspInclude" clientside u-incfile="../_fpclass/fpdbrgn2.inc" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
                    <!--webbot bot="AspInclude" endspan i-checksum="8190" -->
                    </select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Results</span> " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --><input type="submit" value="Search" name="B2" style="font-family: Tahoma; font-weight: bold; font-size:8pt"></form>
                  <p></td>


This is the results page code: 
<% 
dim accessdb, cn, rs, sql
accessdb="/fpdb/contacts" 

cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)

Set rs = Server.CreateObject("ADODB.Recordset")

sql = "SELECT * FROM unicontacts WHERE ("&Request.Form("up") &")"
 

 
' Execute the sql
rs.Open sql, cn
%>

<% 
Response.ContentType = "application/vnd.ms-excel" %>


thanks, im at my wits end..lol.

Mark



rdouglass -> RE: Search one field with mulitple criteria (4/6/2005 14:30:23)

Can you try this code for the results page? 

<% 
dim accessdb, cn, rs, sql

'be sure of the correct name, I suspect it is .mdb
accessdb="/fpdb/contacts.mdb" 

cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)

Set rs = Server.CreateObject("ADODB.Recordset")

DIM myCriteria 
 
IF trim(Request.form("up")&"") > "" THEN 
myCriteria="WHERE ("
myTempArray = split(trim(Request.form("up")&"")",") 
FOR i = 0 TO ubound(myTempArray,1) 
myCriteria = myCriteria & "(up = '" & trim(myTempArray(i)) & "') OR " 
NEXT 
myCriteria = left(myCriteria,len(myCriteria)-4) & ")" 
END IF


sql = "SELECT * FROM unicontacts " & myCriteria
 

 
' Execute the sql
rs.Open sql, cn
%>

<% 
Response.ContentType = "application/vnd.ms-excel" %>


Please be sure to post any and all error messages. If it does error, you may try on the first page changing this line:

<option><%=FP_FieldHTML(fp_rs,"up")%></option>

to this:

<option><%=FP_Field(fp_rs,"up")%></option>

That any help?



mfalk -> RE: Search one field with mulitple criteria (4/6/2005 15:04:59)

I changed the option by getting rid of the HTML portion, and changed the code on the results page that you gave me...here is the error message.

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/report/uplabels.asp, line 24

myTempArray = split(trim(Request.form("up")&"")",")
-----------------------------------------------^

the pointer is actually pointing to the bold faced quote mark, it just didnt show properly here.



rdouglass -> RE: Search one field with mulitple criteria (4/6/2005 15:15:23)

quote:

myTempArray = split(trim(Request.form("up")&"")",")


Ooops. Can you change that line to:

myTempArray = split(trim(Request.form("up")&""),",")

I forgot a comma.[:(]



mfalk -> RE: Search one field with mulitple criteria (4/6/2005 16:07:36)

Holy Guacomole...very cool rdouglas. Thank you for your patience. And saving my butt!

I have one question. When i create the csv, it works fine, unless i select a few hundred records. If i do, it freezes up the whole process and i have to callt o have the server restarted. Is there a way to keep that from happening (besides giong to mysql or sql...lol)..

Thanks, I really appreciate the help today....Another save by RDOUGLAS.

Mark



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375