Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    inner join trouble

    Hi,

    I have an inner join statement looking like this:

    Code:
    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
    Code:
    <%@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"><b><font color="#FFFFFF">Pos</font></b></td>
    <td width="140" align="left"><b><font color="#FFFFFF">Namn</font></b></td>
    <td width="50" align="center"><b><font color="#FFFFFF">Hcp</font></b></td>
    <td width="50" align="center"><b><font color="#FFFFFF">V1</font></b></td>
    <td width="50" align="center"><b><font color="#FFFFFF">V2</font></b></td>
    <td width="50" align="center"><b><font color="#FFFFFF">Tot</font></b></td>
    <td width="50" align="center"><b><font color="#FFFFFF">Thcp</font></b></td>
    <td width="50" align="center"><b><font color="#FFFFFF">P</font></b></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%>&nbsp;<%=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

    Code:
    <td align="left"><a href="showplayer.asp?ID=<%=Request.QueryString("ID")%>" target="results"><%=rs.Fields("Fname").Value%>&nbsp;<%=rs.Fields("Lname").Value%></a></td>
    Any idea what's wrong?
    /Speedy

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    '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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •