Mulitiple selections to SQL
Hi,
could someone point me in the right direction with a query I'm having a problem with.
this page displays a dropdown list box where I can select multiple numbers (PhotoID's) to display records in a table, this fails.
the list is literally
001
002
003
004
005
.
.
.
etc
If I click on one selection from the dropdown box it works ok just not multiples.
The query in question is the 2nd one on the page and I think the problem is this bit of it
"HAVING (((SSC2Photo.PhotoID)=("&request.form("PID")&")));"
the message I get back is
Code:
Syntax error (comma) in query expression '(((SSC2Photo.PhotoID)=(100, 101, 102)))'.
photoid.asp, line 123
Code:
<%@ Language = VBscript %> <% Option Explicit %> <% Response.Buffer = True %>
<html>
<head>
<title>Database Search</title>
<link href="css/rwm_styles.css"
rel="stylesheet"
type="text/css">
</head>
<body>
<center>
<table border="0" width="80%">
<tr>
<form method="POST" action="photoid.asp" name="Form1">
<td width="33%">
<%
DIM IDI
DIM THSrc
DIM IMSrc
IDI = "PI000"
THSrc = "thumbs/"
IMSrc = "images/"
Dim objconn,objRS1,strSQL1,objRS2,strSQL2
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("gen_dib_data.mdb")
objconn.Open
Set objRS1 = Server.CreateObject("ADODB.Recordset")
strSQL1 = "SELECT Photo.PhotoID, Photo.PDesc " & _
"FROM (tCat INNER JOIN tSCat ON tCat.CatID = tSCat.CatID) " & _
"INNER JOIN (tSSCat INNER JOIN (Photo INNER JOIN SSC2Photo " & _
"ON Photo.PhotoID = SSC2Photo.PhotoID) " & _
"ON tSSCat.SSCatID = SSC2Photo.SSCatID) " & _
"ON tSCat.SCatID = tSSCat.SCatID " & _
"GROUP BY Photo.PhotoID, tCat.Secure, Photo.PDesc " & _
"HAVING (((tCat.Secure)='y'));"
objRS1.Open strSQL1, objconn
%><select name="PID" size="10" class="Forms" multiple style="width: 334; height: 136">
<option>Select a Photo ID</option>
<%
Do While Not objRS1.EOF
%>
<option value="<%=objRS1("PhotoID")%>" <%if request.form("PID") = objRS1("PhotoID")then response.write "selected"%>>
<%=IDI%><%=objRS1("PhotoID")%> . . . . . . . . . . . . <%=objRS1("PDesc")%>
</option>
<%
objRS1.MoveNext
Loop
%>
</select>
</td>
<td width="33%" class="F_DG_10L" valign="top">
<p class="F_DG_12L"><b>Admin Page</b></p>
<p><u>Search for Photo/s from the ID number</u></p>
<p>to select multiple ID's just hold down the ctrl key and click
on all the ones you need. You can also group by holding down
shift and combination selections with use of shift and ctrl
keys.</td>
</tr>
<tr>
<td width="33%"> </td>
<td width="33%"> </td>
</tr>
<tr>
<td width="33%">
<input class="Forms" type="submit" value="Submit" name="B1">
<input class="Forms" type="reset" value="Reset" name="B2">
</td>
<td width="33%"> </td>
</form>
</tr>
<tr>
<td width="99%" colspan="2"> </td>
</tr>
<tr>
<td width="99%" colspan="2"> </td>
</tr>
</table>
<%
IF Request.Form("PID") = "" Then
Else
%>
<table border="0" width="80%">
<tr>
<td style="border-bottom: 1px solid #C0C0C0" valign="top" class="F_DG_10L"><b>Photo ID</b></td>
<td style="border-bottom: 1px solid #C0C0C0" valign="top" class="F_DG_10L"><b>Category</b></td>
<td style="border-bottom: 1px solid #C0C0C0" valign="top" class="F_DG_10L"><b>Collection</b></td>
<td style="border-bottom: 1px solid #C0C0C0" valign="top" class="F_DG_10L"><b>Photo Description</b></td>
<td style="border-bottom: 1px solid #C0C0C0" valign="top" class="F_DG_10L"><b>Photographer</b></td>
<td style="border-bottom: 1px solid #C0C0C0" valign="top" class="F_DG_10L"><b>File Name</b></td>
<td style="border-bottom: 1px solid #C0C0C0" valign="top" class="F_DG_10L"><b>Thumbnail</b></td>
</tr>
<%
Set objRS2 = Server.CreateObject("ADODB.Recordset")
strSQL2 = "SELECT DISTINCT SSC2Photo.PhotoID, tSCat.SCatDesc, tSSCat.SSCatDesc, " & _
"Photo.PDesc, Photographer.Fullname, Photo.Filename " & _
"FROM (tCat INNER JOIN tSCat ON tCat.CatID = tSCat.CatID) " & _
"INNER JOIN ((Photo INNER JOIN Photographer " & _
"ON Photo.PhotogID = Photographer.photogID) " & _
"INNER JOIN (tSSCat INNER JOIN SSC2Photo " & _
"ON tSSCat.SSCatID = SSC2Photo.SSCatID) " & _
"ON photo.PhotoID = SSC2Photo.PhotoID) " & _
"ON tSCat.SCatID = tSSCat.SCatID " & _
"GROUP BY SSC2Photo.PhotoID, tSCat.SCatDesc, tSSCat.SSCatDesc, Photo.PDesc, Photographer.Fullname, Photo.Filename " & _
"HAVING (((SSC2Photo.PhotoID)=("&request.form("PID")&")));"
objRS2.Open strSQL2, objconn
%>
<tr>
<%
Do While Not objRS2.EOF
%>
<td class="F_DG_85L"><%=IDI%><%=objRS2("PhotoID")%></td>
<td class="F_DG_85L"><%=objRS2("SCatDesc")%></td>
<td class="F_DG_85L"><%=objRS2("SSCatDesc")%></td>
<td class="F_DG_85L"><%=objRS2("PDesc")%></td>
<td class="F_DG_85L"><%=objRS2("FullName")%></td>
<td class="F_DG_85L"><a class="link1" target="_self" href="<%=IMSrc%><%=objRS2("Filename")%>">
<img border="0" src="site_images/arrow.gif"> <%=objRS2("FileName")%></a>
</td>
<td style="text-align: center"><a target="_self" href="<%=IMSrc%><%=objRS2("Filename")%>">
<img border="0" src="<%=THSrc%><%=objRS2("Filename")%>"></a>
</td>
</tr>
<%
objRS2.MoveNext
Loop
%>
</table>
</center>
<%
objRS1.Close
objRS2.Close
objconn.Close
%>
<% END IF %>
</body>
</html>
any help will be greatly appreciated,
Cheers Rob.
