ADODB.Recordset error '800a0cc1'

The 800a0cc1 error returned by the ADO Recordset Object is usually accompanied by the following text:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

The error occurs in one of two situations:

• You try to access a field in the fields collection that is not in the Recordset
• You try to access a field using an ambiguous name.

Scenario 1 - Missing field
Consider the following code:

<%
strSQL = _
   "SELECT field1, field2 " & _
   "FROM table1 " & _
   "WHERE field1 = 'foobar'"

Set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
   .Source = strSQL
   .ActiveConnection = objConn
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
End With

objRS.Open ,,,,adCmdText

Response.Write(objRS("field3"))
%>

An error will be generated by the last line of code because an attempt it being made to access a field that is not present in the recordset. Looking at the first line of code we can see that the SQL string that we are creating selects only field1 and field2 from the database - and our resulting recordset will contain only those two fields, not field3.

Scenario 2 - Ambiguous Field Name
Consider the following code:

<%
strSQL = _
   "SELECT table1.ID, table2.ID " & _
   "FROM table1, table2 " & _
   "WHERE table1.ID = 1"

Set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
   .Source = strSQL
   .ActiveConnection = objConn
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
End With

objRS.Open ,,,,adCmdText

Response.Write(objRS("ID"))
%>

In this case an error will be generated because the ADO Recordset Object does not know which ID field you are attempting to write to the screen. To solve this problem either:

• rename your fields so that they don't have the same name
• use the SQL "AS" keyword
• use ordinals

Probably the best of the above alternatives involves renaming your fields. For example, if table1 and table2 above where instead called "Customers" and "Orders" then instead of calling the fields simply "ID" an alternative naming scheme would have called the two fields "CustomerID" and "OrderID" respectively.

If renaming the fields is out of the question, then another alternative involves using the "AS" keyword, as shown in the following code (the code to open the recordset is omitted):

<%
strSQL = _
   "SELECT Customers.ID AS CustomerID, " & _
   "Orders.ID AS OrderID " & _
   "FROM Customers, Orders " & _
   "WHERE Customers.ID = 1"
'...
Response.Write( _
   objRS("CustomerID") & "<br>" & vbCrLf &_
   objRS("OrderID") & "<br>" & vbCrLf)
%>

The last alternative involves using ordinals instead. By using an ordinal reference we avoid any confusion over which ID field we are attempting to reference:

<%
strSQL = _
   "SELECT table1.ID, table2.ID " & _
   "FROM table1, table2 " & _
   "WHERE table1.ID = 1"
'...
Response.Write( _
   objRS.Fields(0).Value & "<br>" & vbCrLf &_
   objRS.Fields(1).Value & "<br>" & vbCrLf)
%>