inner join trouble [Archive] - CodingForums.com

Speedy

11-12-2003, 09:54 PM

Hi,

I have an inner join statement looking like this:

rs.open "SELECT * FROM competition1 INNER JOIN competition2 ON competition1.ID=competition2.ID WHERE competition1.ID=" & Request("ID")

I get this error message:

It's not possible to use this connection for this task. It's closed
or invalid in this case.
/showplayer.asp line 14

showplayer.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("db\2003.mdb")
set rs=Server.CreateObject("ADODB.recordset")

rs.open "SELECT * FROM competition1 INNER JOIN competition2 ON competition1.ID=competition2.ID WHERE competition1.ID=" & Request("ID")
if rs.eof then
response.write "<hr><center>Databasen är tom</center><hr>"
end if
%>

<center>
<table border="1" style="border-collapse: collapse" bordercolor="#000000" cellspacing="0" cellpadding="2">
<tr bgcolor="#000000">
<td width="50" align="center">Pos</td>
<td width="140" align="left">Namn</td>
<td width="50" align="center">Hcp</td>
<td width="50" align="center">V1</td>
<td width="50" align="center">V2</td>
<td width="50" align="center">Tot</td>
<td width="50" align="center">Thcp</td>
<td width="50" align="center">P</td>
</tr>
<%
dim background
background="#cccccc"
%>
<% do while not rs.eof %>
<%
if background = "#ffffff" then
background = "#cccccc"
else
background = "#ffffff"
end if
%>
<tr bgcolor="<%=background%>">
<td align="center"><%=rs.Fields("pos").Value%></td>
<td align="left"><a href="showplayer.asp?ID=<%=Request.QueryString("ID")%>"><%=rs.Fields("Fname").Value%> <%=rs.Fields("Lname").Value%></a></td>
<td align="center"><%=rs.Fields("hcp").Value%></td>
<td align="center"><%=rs.Fields("r1").Value%></td>
<td align="center"><%=rs.Fields("r2").Value%></td>
<td align="center"><%=rs.Fields("total").Value%></td>
<td align="center"><%=rs.Fields("Thcp").Value%></td>
<td align="center"><%=rs.Fields("Points").Value%></td>
</tr>
<%
rs.movenext
Loop
%>
</table>
</center>
</body>
</html>
<%
set rs = nothing
%>

What I'm trying to do here is to list all info about the player
I click on in a list where the link looks like this

<td align="left"><a href="showplayer.asp?ID=<%=Request.QueryString("ID")%>" target="results"><%=rs.Fields("Fname").Value%> <%=rs.Fields("Lname").Value%></a></td>

Any idea what's wrong?
/Speedy

raf

11-12-2003, 10:40 PM

Well, the problems is see are that you don't use the connection when you open the recordset (no conn on that line) and the * also isn't realy OK. You should always explicitely specify the variables that you use, even if you indeed would use all variables from both tables. (which is quite unlikely since the join-variables wount be used double. But even if you would use them all --> chances are you'll add columns later on which will the be include in yur recordset without you using them)

There are some other problems (well, bad practices realy). Always put <% option explicit %> on top of your page, always dim your variables, always name your recordsets, choose a harder to guess name for your db (mdb files are automatically downloaded by IE ...), put your connectionstring in a server side include with an ASP extension, you need to first close the recordset before you set it to nothing (rs.Close), you also need to close the connection and set it to nothing, you always need to specify the collection (so not request("ID") but request.form("ID") or request.querystring ...) and certainly check the values from the querystring before you use them, because now, i can easely hack into it with a querystring-variable

Maybe try it like

'should be in an SSI
dim conn
set conn=server.CreateObject("adodb.connection")
conn.Open("provider=microsoft.jet.oledb.4.0;data source="& Request.ServerVariables("APPL_PHYSICAL_PATH") & "db/2003.mdb")

dim rs
set rs = server.CreateObject("adodb.recordset")

dim sql
' * should be replaced by wildcards
sql="SELECT competition1.*, compatition2.* FROM competition1 INNER JOIN competition2 ON competition1.ID=competition2.ID WHERE competition1.ID=" & Request("ID")

rs.Open sql, conn