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> </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>
dBforums
>
INNER JOIN problems...
02-22-05, 12:52
Linear Mode
