Hi

I'm putting together a protoype intranet for my company, and am currently 
working on an online expenses form. I've managed to get the information 
writing to the Access database, but can't seem to alter it once it's 
there. It's strange, as the code I'm using on the expense_process.asp page 
is almost identical to the code i'm using for a staff list page, which 
works fine.

here's the code:

<%@ LANGUAGE = VBScript %>
<%

' Create objects,  etc.
response.Expires = 0
set dbconnect = server.createobject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
dbconnect.open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & 
server.mappath("../../db/orr_intranet") & ".mdb")



dim expense_ID
expense_ID = request.form("expense_ID")

rs.open = "SELECT * FROM expenses WHERE expense_ID = " & expense_ID
			
if request.form("approval") = "yes" then
	rs.fields("exp_id") = "2"
else
	rs.fields("exp_id") = "3"
end if
	
	
Rs.update
	expense_ID = Rs.fields("expense_ID")
Rs.close



dbconnect.close
response.redirect "staff_list.asp"
	
%>

And the error I get is:

Error Type:
ADODB.Recordset (0x800A0E7D)
The connection cannot be used to perform this operation. It is either 
closed or invalid in this context.
/intranet/content/expenses/expenses_process.asp, line 16

I've seen this error mentioned elsewhere on the site, but cannot seem to 
get it to work. Surely by using rs.open the connection to the database is 
being opened, or is that just opening the recordset?

Thanks in advance

Matt

The default ADO default recordset type is "read-only",
"forward-only". You can't update this type of recordset.  You
have to set the recordset properties.

Larry Woods - MCSD, MCT

> -----Original Message-----
> From: matt gear [mailto:matt_gear@h...]
> Sent: Tuesday, July 16, 2002 3:58 PM
> To: Access ASP
> Subject: [access_asp] connection error
>
>
> Hi
>
> I'm putting together a protoype intranet for my
> company, and am currently
> working on an online expenses form. I've managed to
> get the information
> writing to the Access database, but can't seem to
> alter it once it's
> there. It's strange, as the code I'm using on the
> expense_process.asp page
> is almost identical to the code i'm using for a staff
> list page, which
> works fine.
>
> here's the code:
>
> <%@ LANGUAGE = VBScript %>
> <%
>
> ' Create objects,  etc.
> response.Expires = 0
> set dbconnect = server.createobject("ADODB.Connection")
> Set RS = Server.CreateObject("ADODB.Recordset")
> dbconnect.open ("DRIVER={Microsoft Access Driver
> (*.mdb)};DBQ=" &
> server.mappath("../../db/orr_intranet") & ".mdb")
>
>
>
> dim expense_ID
> expense_ID = request.form("expense_ID")
>
> rs.open = "SELECT * FROM expenses WHERE expense_ID = "
> & expense_ID
>
> if request.form("approval") = "yes" then
> 	rs.fields("exp_id") = "2"
> else
> 	rs.fields("exp_id") = "3"
> end if
>
>
> Rs.update
> 	expense_ID = Rs.fields("expense_ID")
> Rs.close
>
>
>
> dbconnect.close
> response.redirect "staff_list.asp"
>
> %>
>
> And the error I get is:
>
> Error Type:
> ADODB.Recordset (0x800A0E7D)
> The connection cannot be used to perform this
> operation. It is either
> closed or invalid in this context.
> /intranet/content/expenses/expenses_process.asp, line 16
>
> I've seen this error mentioned elsewhere on the site,
> but cannot seem to
> get it to work. Surely by using rs.open the connection
> to the database is
> being opened, or is that just opening the recordset?
>
> Thanks in advance
>
> Matt
>

thanks larry

how do i go about changing the properties?

Matt,

Sorry, but I deleted your message, so I can't refer to your
recordset name, but assuming a recordset name of "rs", set these
parameters:

	rs.CursorType=3  'Static cursor
	rs.LockType=3    ' Optimistic lock

That should do it.  The first property defines a recordset as one
that will allow updates (there are others, but for your purposes
this should be sufficient) and the second allows writing to the
recordset (among other things).

Hope this helps...

Larry Woods - MCSD, MCT

> -----Original Message-----
> From: matt gear [mailto:matt_gear@h...]
> Sent: Wednesday, July 17, 2002 10:07 AM
> To: Access ASP
> Subject: [access_asp] RE: connection error
>
>
> thanks larry
>
> how do i go about changing the properties?
>
>
>