Updating Recordsets with LockType and Cursor Type

arimakidd · July 16th, 2005, 10:57 PM

I am using the book, "Beginning ASP Databases" published by WROX. It's going great except for

my present obstacle. I am learning how to update a database using Cursor Types and Lock Types.

However my program is not working. Below is the program:

Code:

<%
Dim objRS
Dim adOpenDynamic
Dim adLockPessimistic
adOpenDynamic = 2
adLockPessimistic = 2

Set objRS = Server.CreateObject("ADODB.recordset")

objRS.Open "boats","DSN=lock",adOpenDynamic,adLockPessimistic


Response.Write objRS("boat") & "<p>"


objRS("boat") = "Lyrics"
objRS.Update

Response.Write objRS("boat") & "<p>"


objRS.Close
Set objRS = Nothing
%>

As you can see based on the program the field value is supposed to change to "Lyrics" But it doesn't. Instead I am getting the following error:

Code:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.
/asp/lock_types/lock_a.asp, line 28

I do not understand why I am getting a ready only problem cause I specified for the database

to be "Write,read,script source access". So it should update. What's wrong? Just in case I

have attached the database in access to this question.

netfed · July 28th, 2005, 06:42 PM

First of all you need to set the connection object into readwrite.
I have done some correction to your code, check it out :-)

Hope this helps
Regards
Ruben jt johansen

<%
dim DataConnection as adodb.connection
Dim objRS as adodb.recordset
Dim adOpenDynamic
Dim adLockPessimistic
adOpenDynamic = 2
adLockPessimistic = 2

set DataConnection = Server.Createobject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.recordset")
DataConnection.mode=3 'same as readwrite

objRS.Open "boats","DSN=lock",adOpenDynamic,adLockPessimi stic

if not objRS.eof then
objRS.movefirst
Response.Write "<p>" & objRS("boat") & "</p>"
objRS("boat") = "Lyrics"
objRS.Update
end if

Response.Write objRS("boat") & "<p>"
objRS.Close
Set objRS = Nothing
dataconnection.close
set dataconnection=nothing
%>

arimakidd · July 28th, 2005, 08:44 PM

Dear Ruben jt johansen

I feel as if you are the only one who can help me, so please figure what's going on with my code. Its still not working. I made your corrections with some minor adjustments. When made the following 2 declarations I got an error, the declarations are:

Code:

dim DataConnection as adodb.connection
Dim objRS as adodb.recordset

I got an error with the 'as *.connection and as *.recordset' so I took off that part. I am not assuming anything here so I'll take the opportunity to asky you, do I have to include the adovbs.inc file? Okay so with those minor adjustments here's the code:

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Lock Types</title>
</head>

<body>
<%
dim DataConnection 
Dim objRS 
Dim adOpenDynamic
Dim adLockPessimistic
adOpenDynamic = 2
adLockPessimistic = 2

set DataConnection = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.recordset")
DataConnection.mode=3  'same as readwrite

objRS.Open "boats","DSN=lock",adOpenDynamic,adLockPessimistic

if not objRS.eof then
 objRS.movefirst
 Response.Write "<p>" & objRS("boat") & "</p>"
 objRS("boat") = "Lyrics"
 objRS.Update
end if

Response.Write objRS("boat") & "<p>"
objRS.Close
Set objRS = Nothing
dataconnection.close
set dataconnection = Nothing
%>
</body>
</html>

And here's the error message I am getting:

Code:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.
/asp/lock_types/lock_a.asp, line 30

By the way, line 30 is the following line:

Code:

objRS.Update

I really really hope you can help me, I am studying asp on my own using the WROX books and I have no tutor to ask anything. Out in the cold here.

arimakidd · July 28th, 2005, 10:26 PM

Ok I tried a slightly different code this time and I am
getting the same exact error. What's going on here?
Here's the code:

Code:

<%
Dim adCmdText 
Dim objRS 
Dim adOpenKeyset
Dim adLockPessimistic
adOpenKeyset = 1
adLockPessimistic = 2
adCmdText = 1


Set objRS = Server.CreateObject("ADODB.recordset")


objRS.Open "SELECT Boat FROM boats;", "DSN=lock", adOpenKeyset, adLockPessimistic, adCmdText

objRS.MoveFirst
Response.Write objRS("Boat") & "<p>"


objRS("Boat") = "Lyrics"
objRS.Update

Response.Write objRS("boat") & "<p>"

objRS.Close
Set objRS = Nothing
%>

I really need some help here. Hope someone has the time.

ChrisScott · July 29th, 2005, 03:45 AM

If you look at the properties for the .mdb file in windows explorer...

- Has the IUSR_[yourMachineName] user got permission write to the database (security tab)?
- Is the database flagged as read-only (general tab)?

HTH,

Chris

arimakidd · July 29th, 2005, 04:12 PM

Hey Chris
Thanks for the advice. However I am running XP Pro and do not have a
security tab. My tabs are:
GENERAL
SHARING
WEB SHARING
CUSTOMIZE

I am having a slight problem in the General Tab. The Read Only is not ticked but the box is coloured green. I select it to ensure its blank but when I go back into it, its coloured green again under 'read only' but its not "Checked", not to sure if this is a problem. But I am logged in XP Pro as an administrator so I don't see it being a problem. What do you think?

ChrisScott · August 1st, 2005, 03:09 AM

To get the security tab to show you may need to adjust your folder settings:

tools -> folder options -> view -> advanced settings

uncheck the "use simple file sharing" and you should now be able to see the security settings

arimakidd · August 1st, 2005, 09:23 PM

AMEN!! THANKS A MIL CHRIS YEAH. YOU ARE THE BOMB.
You really know your stuff. Thanks much, I've been stuck on this chapter
for like a month now. Finally I can move on, Thanks a Mil, have you
published any books recently I'll buy them. Can you give me your opinions on my site? You can check it out at:
http://www.knoxtt.com

I have alot of doing over to do, especially with CSS, everyone tell me I need to add more CSS to it to get ranked in the search engines. But inspite it all be brutally honest and let me know what you think.

ARIMAKIDD