|
| |
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
 |
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
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
|
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Search one field with mulitple criteria - 4/6/2005 11:46:23
You using a DRW or just ASP?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
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.
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
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...
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
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
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
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
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
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
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
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.
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
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
_____________________________
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 
