I have a search that I am trying to add wildcards to, and when I run it I get this error.

Error:
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/clearance/postsearch.asp, line 25


When I run it with no wildcards (ie: WHERE exp1 = exp2) it works, but this doesn't seem to be working.


This is the code:


<%@ Language=VBScript %>
<% Option Explicit %>



<%
Dim strtemp, strsearchbox, sql
  Dim objRS
  Dim intnumb

  strsearchbox = Request.Form("searchbox")
  strtemp = Request.Form("searchmenu")
  sql = "SELECT lastname AS Expr1, firstname AS Expr2, alias AS Expr3 FROM Room WHERE " & strtemp & " LIKE '" & strsearchbox & "%'"
  If sql <> "" then
  Set objRS = Server.CreateObject ("ADODB.Recordset")
  objRS.Open sql, objConn
  End If

    Response.Flush

intnumb = 1
If objRS.State = 1 then
    Do while not (objRS.EOF)
        Response.Write "<form method='post' action='addnewPOP.asp' name=alias" & intnumb & ">"
        Response.Write "<input name=hidform type=hidden value='" & objRS("alias") & "'>"
        Response.Write objRS("lastname") & ", " & objRS("firstname") & "(" & objRS("alias") & ") " & "<input type=submit value='Modify' id=submit1 name=submit1></form>"
        Response.Write "<BR>"
        intnumb = intnumb + 1
        objRS.MoveNext
    Loop
End If
    objRS.Close
  %>

It's working one way because that way is not returning any records so you aren't hitting the lines that are really causing the problem.

Your SQL select statement:
    SELECT lastname AS Expr1, firstname AS Expr2, alias AS Expr3 ...

Your code:
    objRS("alias")
    objRS("lastname")
    objRS("firstname")

The error is telling you that "alias", "lastname", and "firstname" don't exist in the query results. They don't because you are aliasing them with the "AS Exprx". Remove "AS Expr1", "AS Expr2" and "AS Expr3" and it should work.

Looks like you have been bitten by the Access query sql generator. :)

Peter
------------------------------------------------------
Work smarter, not harder.