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..?

<%
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
%>

What is line28? Where is the error?


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.

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


mySQL = "SELECT * from cal where date = #" & dateid & "# ;"

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 & " ;"

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

my code now looks like this:

<%@ 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

k i fiddled a bit and i got it working now! this is the working 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>