Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Item cannot be found in... (RESOLVED)

    I have a form for a booking system, on the form you enter the date in the format 11/04/2006 and when ready click submit. The process page should lookup the date in the calendar table and insert the corresponding id for that date.

    I have the below code but its throwing up this error:

    Error Type:
    ADODB.Recordset (0x800A0CC1)
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    /add.asp, line 28

    but i know the record is there!! any ideas..?

    Code:
    <%
    DIM objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    Server.MapPath ("bookings.mdb") & ";"
    objConn.Open
    
    DIM mySQL
    Dim dateid
    dateid = Request.Form("date1")
    mySQL = "SELECT * from cal where date = " & dateid & " ;"
    
    DIM objRS
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open mySQL, objConn
    
    'declare your variables
    Dim date2, roombooked1, from1, to1, name1, eventname1, address1, phone1, price1, equipment1, layout1, caretaking1, insurance1, riskass1, other1, start1, end1, invnum1
    Dim sConnString, connection, sSQL
    'Receiving values from Form, assign the values entered to variables
    date2 =objRS("cal.id")
    roombooked1 =Request.Form("roombooked")
    from1 =Request.Form("from")
    to1 =Request.Form("to")
    name1 =Request.Form("name")
    eventname1 =Request.Form("eventname")
    address1 =Request.Form("address")
    phone1 =Request.Form("phone")
    price1 =Request.Form("price")
    equipment1 =Request.Form("equipment")
    layout1 =Request.Form("layout")
    caretaking1 =Request.Form("caretaking")
    insurance1 =Request.Form("insurance")
    riskass1 =Request.Form("riskass")
    other1 =Request.Form("other")
    start1 =Request.Form("start1")
    end1 =Request.Form("end1")
    invnum1 =Request.Form("invnum1")
    
    'declare SQL statement that will query the database 
    sSQL = "INSERT into bookings (date1, roombooked1, name1, eventname1, address1, phone1, price1, equipment1, layout1, caretaking1, insurance1, riskass1, other1, start1, end1, invnum1) values ('" & _
    date2 & "', '" & roombooked1 & "', '" & name1 & "', '" & eventname1 & "', '" & address1 & "', '" & phone1 & "', '" & price1 & "', '" & equipment1 & "', '" & layout1 & "', '" & caretaking1 & "', '" & insurance1 & "', '" & riskass1 & "', '" & other1 & "', '" & start1 & "', '" & end1 & "',  '" & invnum1 & "')" 
    'define the connection string, specify database
    'driver and the location of database
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("bookings.mdb") 
    'create an ADO connection object 
    Set connection = Server.CreateObject("ADODB.Connection")
    
    'Open the connection to the database
    connection.Open(sConnString)
    
    'execute the SQL 
    connection.execute(sSQL)
    
    response.redirect "main.asp"
    'Done. Close the connection object
    connection.Close
    Set connection = Nothing
    %>
    Last edited by robojob; 04-12-2006 at 12:26 PM.

  • #2
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What is line28? Where is the error?

    Code:
    mySQL = "SELECT * from cal where date = " & dateid & " ;"
    Response.Write your SQL to see if it what you expect.
    Run it in Access to see if it queries correctly.

  • #3
    New Coder
    Join Date
    Mar 2005
    Posts
    50
    Thanks
    0
    Thanked 2 Times in 2 Posts

    delimiters maybe

    for access:
    numerical values don't need them
    strings need " or '
    dates need #

    Code:
    mySQL = "SELECT * from cal where date = #" & dateid & "# ;"
    see the munki click- was that so random?

  • #4
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    change this: date2 =objRS("cal.id")
    to this: date2 =objRS("id")

    you shouldn't reference the table

    also, if all you're getting from that query is the ID, it's best to just select the ID and save resources
    mySQL = "SELECT ID from cal where date = " & dateid & " ;"

  • #5
    New to the CF scene
    Join Date
    Apr 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you could probably also A)use the same connection to the database to execute your delete through (why make 2 connections to the same DB? wasteful) and B)close and get rid of the recordset once youve pulled out the info you need from it (which like mehere said, you should probably just be pulling the id instead of *, and dont reference the tablename when you try to look at it through your objRS)

    also, is that all of your code? the error says line 28, but that line is just setting a var to something from the request object when I agree the line setting the date2 variable is likely causing the problem

  • #6
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    my code now looks like this:

    Code:
    <%@ Language="VBScript" %>
    <% Option Explicit %> 
    <html>
    <head>
    <title>Form to database</title>
    </head>
    <body> 
    <%
    DIM objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    Server.MapPath ("bookings.mdb") & ";"
    objConn.Open
    
    DIM mySQL
    Dim dateid
    dateid = Request.Form("date1")
    mySQL = "SELECT id FROM cal WHERE date = " & dateid & " ;"
    
    DIM objRS
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open mySQL, objConn
    
    'declare your variables
    Dim date2, roombooked1, from1, to1, name1, eventname1, address1, phone1, price1, equipment1, layout1, caretaking1, insurance1, riskass1, other1, start1, end1, invnum1
    Dim sConnString, connection, sSQL
    'Receiving values from Form, assign the values entered to variables
    date2 = objRS("id")
    roombooked1 =Request.Form("roombooked")
    from1 =Request.Form("from")
    to1 =Request.Form("to")
    name1 =Request.Form("name")
    eventname1 =Request.Form("eventname")
    address1 =Request.Form("address")
    phone1 =Request.Form("phone")
    price1 =Request.Form("price")
    equipment1 =Request.Form("equipment")
    layout1 =Request.Form("layout")
    caretaking1 =Request.Form("caretaking")
    insurance1 =Request.Form("insurance")
    riskass1 =Request.Form("riskass")
    other1 =Request.Form("other")
    start1 =Request.Form("start1")
    end1 =Request.Form("end1")
    invnum1 =Request.Form("invnum1")
    
    'declare SQL statement that will query the database 
    sSQL = "INSERT into bookings (date1, roombooked1, name1, eventname1, address1, phone1, price1, equipment1, layout1, caretaking1, insurance1, riskass1, other1, start1, end1, invnum1) values ('" & _
    date2 & "', '" & roombooked1 & "', '" & name1 & "', '" & eventname1 & "', '" & address1 & "', '" & phone1 & "', '" & price1 & "', '" & equipment1 & "', '" & layout1 & "', '" & caretaking1 & "', '" & insurance1 & "', '" & riskass1 & "', '" & other1 & "', '" & start1 & "', '" & end1 & "',  '" & invnum1 & "')" 
    'execute the SQL 
    connection.execute(sSQL)
    
    response.redirect "main.asp"
    'Done. Close the connection object
    connection.Close
    Set connection = Nothing
    %>
    </body>
    </html>
    and now i am getting this message:

    Error Type:
    ADODB.Field (0x800A0BCD)
    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
    /testing/add.asp, line 28

  • #7
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    k i fiddled a bit and i got it working now! this is the working code:

    Code:
    <%@ Language="VBScript" %>
    <% Option Explicit %> 
    <html>
    <head>
    <title>Form to database</title>
    </head>
    <body> 
    <%
    DIM objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    Server.MapPath ("bookings.mdb") & ";"
    objConn.Open
    
    DIM mySQL
    Dim dateid
    dateid = Request.Form("date1")
    mySQL = "SELECT id FROM cal WHERE date = CStr('" & dateid & "');"
    
    DIM objRS
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open mySQL, objConn
    
    'declare your variables
    Dim date2, roombooked1, from1, to1, name1, eventname1, address1, phone1, price1, equipment1, layout1, caretaking1, insurance1, riskass1, other1, start1, end1, invnum1
    Dim sConnString, connection, sSQL
    'Receiving values from Form, assign the values entered to variables
    date2 = objRS("id")
    roombooked1 =Request.Form("roombooked")
    from1 =Request.Form("from")
    to1 =Request.Form("to")
    name1 =Request.Form("name")
    eventname1 =Request.Form("eventname")
    address1 =Request.Form("address")
    phone1 =Request.Form("phone")
    price1 =Request.Form("price")
    equipment1 =Request.Form("equipment")
    layout1 =Request.Form("layout")
    caretaking1 =Request.Form("caretaking")
    insurance1 =Request.Form("insurance")
    riskass1 =Request.Form("riskass")
    other1 =Request.Form("other")
    start1 =Request.Form("start1")
    end1 =Request.Form("end1")
    invnum1 =Request.Form("invnum1")
    
    'declare SQL statement that will query the database 
    sSQL = "INSERT into bookings (date1, roombooked1, name1, eventname1, address1, phone1, price1, equipment1, layout1, caretaking1, insurance1, riskass1, other1, start1, end1, invnum1) values ('" & _
    date2 & "', '" & roombooked1 & "', '" & name1 & "', '" & eventname1 & "', '" & address1 & "', '" & phone1 & "', '" & price1 & "', '" & equipment1 & "', '" & layout1 & "', '" & caretaking1 & "', '" & insurance1 & "', '" & riskass1 & "', '" & other1 & "', '" & start1 & "', '" & end1 & "',  '" & invnum1 & "')" 
    
    'execute the SQL 
    objconn.execute(sSQL)
    
    response.redirect "main.asp"
    'Done. Close the connection object
    connection.Close
    Set connection = Nothing
    %>
    </body>
    </html>


  •  

    Posting Permissions

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