Asp Sql Help!

mada999

hey all I have a problem at the mo with my asp/SQL page!

I have a db called users.mdb and there is a field called song id. I have a page called playlists.asp and it has a form in it with checkboxes. When i check the checkboxes i want to pass the songid's to another page.

On the other page i want to query the database with the songid's and get the paths of the songs and display them in another asp page (playlistcheck.asp)

---

playlist.asp

---

<%

'Variable for the user!
Dim strUserName 'Holds the name of the user

'getting the user name from the previous page!
strUserName = Request.QueryString("Username")

'setting the cookie to the Username
Response.Cookies("uname") = strUserName


set conn=Server.CreateObject("ADODB.Connection")

dsn = "DBQ=" & Server.Mappath("../login\users.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
conn.Open dsn

sql = "select songid,name,artist from tblsongs"
set rs = conn.execute(sql)

%>
<form action="playlistpick.asp" method="get">
<table border="1" width="69%">
<tr>
<% 'Put Headings On The Table of Field Names
for each whatever in rs.fields%>
<td><font color="black" face="verdana" size="-1"><b><%=whatever.name%></B></TD>
<% next %>
</tr>
<% do while not rs.EOF %>
<center>
<tr><td><input type="checkbox" name="songid" value="<%=rs(0)%>"></td>
<td><font face=Arial size=-1 color=black><%=rs(1)%></td>
<td><font face=Arial size=-1 color=black><%=rs(2)%></td>

<%rs.movenext
' step through the votes setting the return value = to their id
' and the displayed text = to their name
loop
%>
</tr>
</table>
<p>
<input type="submit" value="Submit"><input type="reset" value="Reset"></p>
</form>

---

playlistcheck.asp

---

<%

'Variable for the user!
Dim strUserName 'Holds the name of the user

'getting the user name from the previous page!
strUserName = Request.QueryString("Username")

'setting the cookie to the Username
Response.Cookies("uname") = strUserName


set conn=Server.CreateObject("ADODB.Connection")

dsn = "DBQ=" & Server.Mappath("../login\users.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
conn.Open dsn

sql = "select path from tblsongs where songid=" & request.querystring("songid") & ""
set rs = conn.execute(sql)


response.write(rs(0))

%>


when I send the songid's to the db's i get this error...


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'songid=2, 5'.
/madafm/playlists/playlistpick.asp, line 19


Browser Type:
Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)

Page:
GET /madafm/playlists/playlistpick.asp?songid=2&songid=5


Anyone able to help me out ?

Thanks in advance...

tomED

After a quick glance, i'm not sure this is the best way to do what you are trying to do - how and ever.... I will try answer your question.

Basically, as you can see form the erorr, the value you are being given is a comma seperated one. As in "2, 4, 5"etc

What you need to do is split up the values so that you have single values like "2", "4" etc.....

Then you should build your script to add on "OR songid=x" etc etc
something like this:

select path from tblsongs where songid=2 OR where songid=4

JJSolutions

or use

where songid in (" & request.querystring("songid") & ")"

That should do the trick (I think that is supported in Access)

It is basically saying where songid is in the following list