SQL Update Permission Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mr_wizard - 26 Feb 2005 21:55 GMT

I haven't written ASP code to update an Access DB in years. Having a
permission problem. Been working on it for hours, time to get some help.
Running IIS 5 on an XP Pro system. ASP code to update an Access 2002 table
via ODBC. Receive the following error:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the
provider, or of the selected locktype.

I have set the directory permissions on the directory holding the .mdb to RW
scripts only. Have checked permissions everywhere I can think of.

The code:
set cn=server.CreateObject("ADODB.CONNECTION")
cn.ConnectionString="DSN=Pick3 DBs;UID=;PWD=;DATABASE=il3-digit;APP=ASP
script"
cn.Open
set rs=server.CreateObject("ADODB.RECORDSET")
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "TWP_Polls", cn, , , adCmdTable
rs.Fields("twp_YVotes") = 100
rs.update
rs.close

Sure could use some help on this. Thanks!

Mike

--------------------------------------------------------------------------------

Reply to this Message

MGFoster - 27 Feb 2005 00:26 GMT

> I haven't written ASP code to update an Access DB in years. Having a
> permission problem. Been working on it for hours, time to get some help.
[quoted text clipped - 19 lines]
> rs.update
> rs.close

Is this a .mdb file where the data is stored? ("DSN=Pick3 DBs" makes me
think it is a PICKs db.)

First thought is that you don't have UPDATE permissions to the table
"TWP_Pools." That may be corrected by putting a UID & PWD values in the
parameters & giving that UID permission to UPDATE that table.

Second thought is you might try an adOpenDynamic CursorType.

R/W permission on the file containing the db can be a consideration, but
usually, if the file is an .mdb file, you will be notified, when the
file is opened, that it is Read Only. If the above fail, go ahead &
check the file's properties to see if Read Only is checked.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Reply to this Message

Mike Reed - 27 Feb 2005 13:47 GMT

I <might> have gotten around the permission error by moving the DB to
the IIS directory the ASP page resides in.

A couple more hours trying to get around the next error message:

"Current Recordset does not support updating. This may be a limitation
of the provider, or of the selected locktype."

Re CONNECTION STRING: "Pick3 DBs" is the ODBC name, "il3-digit" is the
DB (.mdb) name (shouldn't be needed w/ODBC), and "TWP_Polls" is the
table.

I've tried opening w/ Administrator UID & Password -- same error. All
files and directories are write-enabled.

Microsoft lists 4 possible causes for this error. The last cause isn't
applicable. I've tried the first three remedies with no success. I even
posted update ASP code from MS site -- same error. This is such a simple
action, but I'm at a loss...

Mike

Reply to this Message

MGFoster - 28 Feb 2005 04:45 GMT

> I <might> have gotten around the permission error by moving the DB to
> the IIS directory the ASP page resides in.
[quoted text clipped - 15 lines]
> posted update ASP code from MS site -- same error. This is such a simple
> action, but I'm at a loss...

Try opening the data file & see if you can change data directly in the
table. If that works, the connection string is probably the problem.
If you can't directly update then table permissions is the problem. If
you can't open the file, then corrupt file or file permissions.
Remember, when using the db as an internet data source the "user" is
some ASP/IIS user. I'm not very proficient in Internet based dbs so you
should look at documentation on which user name tries to get data & see
if you have that user's set up to update the table/have access to the
db.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Reply to this Message

Br@dley - 28 Feb 2005 22:42 GMT

>> I <might> have gotten around the permission error by moving the DB to
>> the IIS directory the ASP page resides in.
[quoted text clipped - 28 lines]
> & see if you have that user's set up to update the table/have access
> to the db.

Or perhaps the ODBC driver only supports read-only? (sorry, didn't pick
up what database you are using).

Br@dley

Reply to this Message