Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: INNER JOIN problems...

    I'm trying to put together an ASP page while will allow a user to select the letter of a last name, and then sort the data to only display records with that particular last name. I'm providing information from three different tables and I'm having a problem displaying the data. My query does work b/c I've used it several times. I am using a MSSQL Server 2000 database. Here is my error message:

    Error Type:
    ADODB.Recordset (0x800A0CC1)
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    /alpha.asp, line 63

    Here is my code:

    Code:
    <html>
    <head>
    	<title>Houston County Case Management</title>
    	<link href="../hcda.css" rel="stylesheet" type="text/css">
    </head>
    <body>
    
    <%
    'Variables
    Dim rsBan		'Recordset
    Dim strSQL		'SQL String
    Dim intNum		'Counting numbers to display how many results returned to user
    
    Set conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Driver={SQL Server};" & _
               "Server=XXX.XXX.XXX.XXX;" & _
               "Address=XXX.XXX.XXX.XXX,1433;" & _
               "Network=DBMSSOCN;" & _
               "Database=PDData;" & _
               "Uid=XXXXX;" & _
               "Pwd=XXXXX"
    
    Set rsBan = Server.CreateObject("ADODB.Recordset")
    
    strSQL = "SELECT DefendantCase.CaseNumber, DefendantCase.FirstName, DefendantCase.LastName, StatusDescription.[Description], ProsAttyDescription.[Description], DefendantCase.VBKey FROM DefendantCase JOIN StatusDescription ON DefendantCase.StatusID = StatusDescription.StatusID JOIN ProsAttyDescription ON DefendantCase.ProsAtty = ProsAttyDescription.ProsAttyID WHERE DefendantCase.LastName LIKE '" & Request.QueryString("axs") & "%' ORDER BY DefendantCase.LastName"
    
    rsBan.Open strSQL, conn
    
    	Response.Write("<table width=743 border=0 bordercolor=#CCCCCC cellpadding=0 cellspacing=0 align=center>")
    	Response.Write("  <tr align=left valign=top> ")
    	Response.Write("  <td align=center><a href=index.asp>Home</a> | <a href=search.html>Search</a></td>")
    	Response.Write("</tr>")
    	Response.Write("</table>")
    	Response.Write("<div align=center><img src=head.jpg alt=HCDA></div>")
    	Response.Write("<table width=743 border=1 bordercolor=#992A31 bordercolorlight=#992A31 bordercolorlight=#992A31 cellpadding=5 cellspacing=0 align=center>")
    	Response.Write("  <tr align=left valign=top> ")
    	Response.Write("    <td width=15><font size=2> &nbsp; </font></td>")
    	Response.Write("    <td width=120><b><font size=2>Last Name</font></b></td>")
    	Response.Write("    <td width=16><b><font size=2>First Name</font></b></td>")
    	Response.Write("    <td width=27><b><font size=2>Case Number</font></b></td>")
    	Response.Write("    <td width=46><b><font size=2>Status of Case</font></b></td>")
    	Response.Write("    <td width=95><b><font size=2>Defense Attorney</font></b></td>")
    	Response.Write("    <td width=69><b><font size=2>Prosecuting Attorney</font></b></td>")
    	Response.Write("    <td width=264><b><font size=2>Charges</font></b></td>")
    	Response.Write("    <td width=57><b><font size=2>Sentence</font></b></td>")
    	Response.Write("  </tr>")
    'Loop record set
    Do While not rsBan.EOF
    intNum = intNum + 1
    	Response.Write("  <tr align=left valign=top> ")
    	Response.Write("    <td width=15><font size=2>")
    	Response.Write(intNum)
    	Response.Write("</font></td>")
    	Response.Write("    <td width=120><font size=2>")
    	Response.Write(rsBan("LastName"))
    	Response.Write(", ")
    	Response.Write(rsBan("FirstName"))
    	Response.Write(" ")
    	Response.Write(rsBan("CaseNumber"))
    	Response.Write("</font>	</td>")
    	Response.Write("    <td width=16><font size=2>")
    	Response.Write(rsBan("ProsAttyDescription.Description"))
    	Response.Write("</font></td>")
    	Response.Write("    <td width=27><font size=2>")
    	Response.Write(rsBan("StatusDescription.Description"))
    	Response.Write("</font></td>")
    	Response.Write("    <td width=46><font size=2>")
    	Response.Write(rsBan("FirstName"))
    	Response.Write("</font></td>")
    	Response.Write("</font></td>")
    	Response.Write("    <td width=95><font size=2>")
    	Response.Write(rsBan("CaseNumber"))
    	Response.Write("</font></td>")
    	Response.Write("</font></td>")
    	Response.Write("    <td width=69><font size=2>")
    	Response.Write(rsBan("CaseNumber"))
    	Response.Write("</font></td>")
    	Response.Write("    <td width=264><font size=2>")
    	Response.Write(rsBan("CaseNumber"))
    	Response.Write("</font></td>")
    	Response.Write("    <td width=57><font size=2>")
    	Response.Write(rsBan("CaseNumber"))
    	Response.Write("</font></td>")
    	rsBan.MoveNext
    	
    Loop  
    
    	Response.Write("</tr>")
    	Response.Write("</table>")
    
    'Reset objects
    rsBan.Close
    Set rsBan = Nothing
    Set adoCon = Nothing
    %>
    
    </body>
    </html>

  2. #2
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    Because you have more than one field called Description you should alias them

    Select ... fromonetable.[Description] as t1desc, fromothertable.[Description] as t2desc

    then you refer to them as that in your code
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

Posting Permissions

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