Hi...thanks in advance.

I am making a ASP based shopping cart to learn ASP and this is my problem 
(this is going to be hard to explain). My navigation is a drop down box 
being populated by the database. It works fine on the first page. But when 
you click on it to go to the displayProducts page (this data also being 
populated by the database) I get this error (line of error is shown below 
with arrow) 'Item cannot be found in the collection corresponding to the 
requested name or ordinal.' 


Here is the call to the database on the first page:

<%@ LANGUAGE="VBSCRIPT" %>
<%

      dim rs
      dim sqlString 
      dim connectionString, databaseName, physicalPath 

      physicalPath = Server.MapPath(".")
      databaseName = "/data/store.mdb" 
   connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & physicalPath & "\" & databaseName
      
      sqlString = "select * FROM tblCategories" 
   
                
      set rs = Server.CreateObject("ADODB.Recordset")
      rs.Open sqlString, connectionString
%>

Here is the code for the drop down box (the code is the same for both 
pages):

<%
      
      rs.MoveFirst
      
      response.write "<SELECT NAME=selProduct>"
      
      while not rs.EOF
      
          
 ***ERROR on display page not on first page------>    
response.write "<OPTION value=" & rs("categoryCode") & ">" & rs
("categoryDescription")
           
      rs.MoveNext
      
      wend
      
      response.write "</select>" 

%>

Here is the call to the database on the second page (the page that 
displays the products being populated from the database and the page that 
is getting the error):

<%@ LANGUAGE="VBSCRIPT" %>

<%  

      dim rs
      dim sqlString 
      dim ID, name, CCode, cDesc, model, price, description     
      dim connectionString, databaseName, path
      dim pageTitle
      
      path = Server.MapPath(".")
      databaseName = "../data/store.mdb" 
      connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & path & "\" & databaseName

      CCode = Request.Form("selProduct")
      
      sqlString = "Select * from tblCategories, tblInventory"
      sqlString = sqlString & " Where 
tblCategories.categoryCode=tblInventory.categoryCode"

      If CCode <> "" Then
      	sqlString = sqlString & " AND tblCategories.categoryCode='" & 
CCode & "'"
      End If
      
      if CCode = "" then
      
        pageTitle = "All Categories"
        
      else
      
        pageTitle = CCode
      
      end if
        
      set rs = Server.CreateObject("ADODB.Recordset")
      rs.Open sqlString, connectionString
      
%>



Also, as a side note, when do I close the database?

thanks
Lori

That error usually means either:

-- you have a typo somewhere, and a fieldname in your code is not exactly 
the same as the corresponding fieldname in the database, so they don't 
match.

-- the query is not really pulling the fields you expect.  Insert a 
temporary statement to print all the fieldnames to see what is really 
there.

-- the fieldname you are trying to access is in more than one table, and 
is qualified by the tablename in the recordset.

> That error usually means either:

> -- you have a typo somewhere, and a fieldname in your code is not 
exactly 
t> he same as the corresponding fieldname in the database, so they don't 
m> atch.

> -- the query is not really pulling the fields you expect.  Insert a 
t> emporary statement to print all the fieldnames to see what is really 
t> here.

> -- the fieldname you are trying to access is in more than one table, and 
i> s qualified by the tablename in the recordset.


The fieldname is in more than one table...please explain further what you 
mean by "qualified by the tablename in the recordset"?

http://www.adopenstatic.com/faq/800a0cc1.asp
shows you how to avoid the problem given your current SQL statement.

HOWEVER, I would strongly suggest not using SELECT *, which is the real
cause of the problem. If you just select the fields you need individually,
then you won't have two fields with the same name in the recordset.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Lori Bannon" <lori@s...>
Subject: [access_asp] Re: Item cannot be found in the collection
corresponding to the requested name or ordinal.


: > That error usually means either:
:
: > -- you have a typo somewhere, and a fieldname in your code is not
: exactly
: t> he same as the corresponding fieldname in the database, so they don't
: m> atch.
:
: > -- the query is not really pulling the fields you expect.  Insert a
: t> emporary statement to print all the fieldnames to see what is really
: t> here.
:
: > -- the fieldname you are trying to access is in more than one table, and
: i> s qualified by the tablename in the recordset.
:
:
: The fieldname is in more than one table...please explain further what you
: mean by "qualified by the tablename in the recordset"?