Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    11

    Unanswered: ASP database problems

    I have a database of clubs and their information and the names populate a drop down box. when a club name is selected the information on that particular club should be displayed. but for some reason from the code listed below what is displayed is just the club name without the other textboxes followed by the error message:

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

    i have checked the names in the db and their all correct in the code so theres no probs there. i have no idea what the problem is! can anyone help??

    <html>

    <head>
    <meta name="GENERATOR" content="Microsoft FrontPage 5.0">
    <meta name="ProgId" content="FrontPage.Editor.Document">
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>Club</title>

    <%
    Dim strclubs

    ' Retrieve Input From Form
    strclubs = Request.Form("clubs")

    ' Open Database Connection
    Set conn = Server.CreateObject("ADODB.Connection")
    connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("intelliGuide.mdb")
    Conn.Open connStr

    strSQLQuery = "SELECT * FROM club WHERE clubName like '%" & strclubs & "'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3
    %>

    <form action="index.asp" method="post" name="mainpage">

    <Select Name="clubs" size="1" style="position:absolute; z-index:1; left: 160; top: 140">

    <%While Not rs.EOF%>

    <option value="<%= rs("clubname") %>"><%= rs("clubname") %></option>

    <%
    rs.MoveNext
    Wend

    rs.close
    set rs = nothing
    %>

    </Select>

    <input type="Submit" name="Search" value="Search" style="position:absolute; z-index:1; left: 400; top: 139">


    <div><font face="Arial" style="position:absolute; z-index:1; left: 160; top: 220">Club Name:</font></div>

    <textarea name="club_name" cols="25" rows="1" style="position:absolute; z-index:10; left: 260; top: 220">
    <%

    strSQLQuery = "SELECT clubname FROM club WHERE clubName like '%" & strclubs & "'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    Do While Not rs.Eof

    For intIndex = 0 to rs.Fields.Count
    Response.Write rs.Fields(intIndex).value & vbcrlf <--ERROR here
    Next

    rs.MoveNext

    Loop

    %>
    </textarea>

    <div><font face="Arial" style="position:absolute; z-index:1; left: 160; top: 280">Street:</font></div>

    <textarea name="street" cols="25" rows="1" style="position:absolute; z-index:10; left: 260; top: 280">
    <%

    strSQLQuery = "SELECT street FROM club WHERE clubName like '%" & strclubs & "'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    Do While Not rs.Eof

    For intIndex = 0 to rs.Fields.Count
    Response.Write rs.Fields(intIndex).value & vbcrlf
    Next

    rs.MoveNext

    Loop

    %>
    </textarea>

    <div><font face="Arial" style="position:absolute; z-index:1; left: 160; top: 340">Postcode:</font></div>
    <textarea name="postcode" cols="25" rows="1" style="position:absolute; z-index:10; left: 260; top: 340">
    <%

    strSQLQuery = "SELECT postcode FROM club WHERE clubName like '%" & strclubs & "'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    Do While Not rs.Eof

    For intIndex = 0 to rs.Fields.Count
    Response.Write rs.Fields(intIndex).value & vbcrlf
    Next

    rs.MoveNext

    Loop

    %>
    </textarea>

    <div><font face="Arial" style="position:absolute; z-index:1; left: 160; top: 400">Tel:</font></div>
    <textarea name="tel" cols="25" rows="1" style="position:absolute; z-index:10; left: 260; top: 400">
    <%

    strSQLQuery = "SELECT telephone FROM club WHERE clubName like '%" & strclubs & "'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    Do While Not rs.Eof

    For intIndex = 0 to rs.Fields.Count
    Response.Write rs.Fields(intIndex).value & vbcrlf
    Next

    rs.MoveNext

    Loop

    %>
    </textarea>

    <div><font face="Arial" style="position:absolute; z-index:1; left: 160; top: 460">Fax:</font></div>
    <textarea name="fax" cols="25" rows="1" style="position:absolute; z-index:10; left: 260; top: 460">
    <%

    strSQLQuery = "SELECT fax FROM club WHERE clubName like '%" & strclubs & "'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    Do While Not rs.Eof

    For intIndex = 0 to rs.Fields.Count
    Response.Write rs.Fields(intIndex).value & vbcrlf
    Next

    rs.MoveNext

    Loop

    %>
    </textarea>

    </form>

    <%
    rs.close
    set rs = Nothing
    %>
    </body>
    </html>

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Your problem is that fieldcount is 1 but the field index starts at zero.

    If you have one field (clubname for example) it is rs.Fields(0), but the field count is 1. So your loop goes from 0 to 1 (inclusive, that is it runs for zero which works and then tried to run for 1 which does not work).

    You loop should be...
    Code:
    For intIndex = 0 to (rs.Fields.Count-1)
      Response.Write rs.Fields(intIndex).value & vbcrlf <--ERROR here
    Next
    Please note though, that for this query you are only selecting one field.... clubname (your select statement is quoted below)
    Code:
    strSQLQuery = "SELECT clubname FROM club WHERE clubName like '%" & strclubs & "'"

  3. #3
    Join Date
    Mar 2004
    Posts
    11
    nice1 that works a treat now!

Posting Permissions

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