...

View Full Version : Cannot update Error



Squall Leonhart
12-22-2003, 07:16 AM
Hi, guys.:)

I want to add the items to the access database.
I run the site on IIS in XP pro.
I have written the following code. Please have a look.



update.asp


<%
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
Set adoCon = Server.CreateObject("ADODB.Connection")
Set rsAddComments = Server.CreateObject("ADODB.Recordset")
adoCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("game.mdb")

Select Case Request.Querystring("state")

Case "add"
strSQL = "SELECT * FROM tblGame;"
rsAddComments.CursorType = 2
rsAddComments.LockType = 3
rsAddComments.Open strSQL, adoCon
rsAddComments.AddNew
rsAddComments.Fields("title") = Request.Form("title")
rsAddComments.Fields("genre") = Request.Form("genre")
rsAddComments.Fields("Content") = Request.Form("content")
rsAddComments.Update
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
Response.Redirect "post.asp"
End select
%>


When the this page loads, error message

'Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.'

appears. Do you guys know what would cause this kind of error?
Thanks.

raf
12-22-2003, 10:00 AM
There were a few thread about this error and possible reasons... Run a search for more info.
I also don't understand your code, and don't see why you don't use a simple insert. You should also replace single quotes by two single quotes (see the sticky) and values of string-variables need to be enclosed in quotes.
You best also check on the number of inserted rows before redirecting. And connectionstring realy need to be inside an SSI (with asp-extension and placed above the webroot)


' all this should be inside a serverside include>>>>>
Dim adoCon 'Holds the Database Connection Object
Dim strSQL 'Holds the SQL query for the database
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("game.mdb")
'<<<<
Select Case Request.Querystring("state")

Case "add"
strSQL = "INSERT INTO tblGame (title, genre, content) VALUES('thetitle', 'thegenre', 'thecontent')"
strSQL=replace(strSQL, "thetitle", replace(Request.Form("title"), "'","''"))
strSQL=replace(strSQL, "thegenre", replace(Request.Form("genre"), "'","''"))
strSQL=replace(strSQL, "content", replace(Request.Form("content"), "'","''"))
adoCon.Execute strSQL, numinserted
if numinserted = 1 then
Response.Redirect "post.asp"
else
Response.write('Record not inserted. Possible databaseproblem')
end if
End select

Squall Leonhart
12-23-2003, 02:39 AM
Thanks for reply, raf.:)

The code above generates error

Microsoft VBScript runtime (0x800A01A8)
Object required: 'DRIVER={Microsoft Ac'


Do you have any idea?

raf
12-23-2003, 09:06 AM
try it with these connectionstrings (what i always used):


Dim strSQL
Dim adoCon
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open("provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("./game.mdb"))


with the server.MapPath("./game.mdb") i am assuming that the db is placed in the same folder as your script.

Squall Leonhart
12-23-2003, 11:54 AM
Thanks for reply, raf.:)

But this time following error message appears.



Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query
line 15




<%
Dim strSQL
Dim adoCon
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open("provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("./game.mdb"))

Select Case Request.Querystring("state")

Case "add"
strSQL = "INSERT INTO tblGame (title, genre, content) VALUES('thetitle', 'thegenre', 'thecontent')"
strSQL=replace(strSQL, "thetitle", replace(Request.Form("title"), "'","''"))
strSQL=replace(strSQL, "thegenre", replace(Request.Form("genre"), "'","''"))
strSQL=replace(strSQL, "content", replace(Request.Form("content"), "'","''"))
adoCon.Execute strSQL, numinserted
if numinserted = 1 then
Response.Redirect "post.asp"
else
'Response.write('Record not inserted. Possible databaseproblem')
end if
End select

%>

raf
12-23-2003, 12:36 PM
Well, look at the first line of my first post.;)
Possible reasons:
- you have the table open in designview in access,
- the file is readonly (for the web)
- the file is placed in a readonly folder (--> if your site is placed on a (free) webhost, then you'll probably need to move the accessfile to the 'write-folder' --> in most cases, this is the '/db' folder.
The path in your connectionstring then becomes
../db/game.mdb
- the "security and copy-bug" (look up the threads)

Squall Leonhart
12-23-2003, 05:41 PM
mmm.......I am hosting this on IIS in XP pro not free hosting.

I didn't have this problem in Windowk 2k professional....

Oakendin
12-23-2003, 07:09 PM
If you search google with this string:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)

You'd probably would've found out that it was some sort of permissions problems.

If you're using a free host, you wouldn't be able to do this, but if you host on your local box, this link may do it for you:
http://support.codecharge.com/kb_article.asp?article_id=4

If you're using a free host, make sure they allow you to use DBs and find the folder for it.

If your host is paid for, write tech support and tell them that you want to make a DB executable but not downloadable. Assuming you can't do this via your site admin screen.

If your permissions are correct, you can use ADO to make your recordset or choose to use SQL, whichever one suits your fancy.

Squall Leonhart
12-23-2003, 11:48 PM
Again....
mmm.......I am hosting this on IIS in XP pro not free hosting.

I didn't have this problem in Windowk 2k professional....

raf
12-24-2003, 12:00 AM
Well,

Since you don't supplie more info then
"Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query",
i don't think we can say more then 'check if the file isn't read only etc. ' It has been discussed here multiple times, and changing the db-name always worked for me.

If you don't give any feedback on what you tried and the results of it, then i don't see ....

Or i could recommend you to restart your machine but then i would sound like a desperate helpdesk:)

Squall Leonhart
12-24-2003, 12:51 AM
'check if the file isn't read only etc. '

I made sure Game.mdb is not read only.

Squall Leonhart
12-24-2003, 01:15 AM
Ralf, I tried on Brinkster free hosting. It works.
Which means nothing wrong with code.

But when I run this on IIS in XP professional, it doesn't work.

Is there any setting I need to tinker to get it work?

raf
12-24-2003, 01:33 AM
Is there any setting I need to tinker to get it work?
no.

I know i'm just repeating myself, but the few times that i had that problem, i could only solve it by copying the db. deleting the original db and then renaming the copy to the original.
I don't have an explanation for it.



You aren't by any chance querying this db with ColdFusion ? (there are some known bugs with tablelocking in access)

Squall Leonhart
12-24-2003, 08:51 PM
No, I am not using cold fusion.

whammy
12-25-2003, 03:53 AM
Ok, I ran into this problem before - assuming your script works otherwise (which it appears to do by the error you're receiving), it's a permissions problem on your folder or in IIS (I don't remember if it's Windows permissions or IIS permissions, but I think I had to change something in IIS).

I forget the exact steps to make it work, I will look it up again.

Sorry about the late reply, I have been really busy, but I WAS able to find a site with information that got the same problem fixed for me in about 20 minutes before... I will look for it again, but I may not have an answer for you until after Christmas. :(

If you look long and hard enough on google you will find it, I found a tutorial on this that walked you through it with pictures, even.

whammy
12-25-2003, 03:59 AM
Ok, looking at the local stuff I did, just go into IIS and make sure that your directory has read and write permissions in IIS to start with, and that your .mdb file is not read-only...

Squall Leonhart
12-25-2003, 10:09 PM
Thanks for reply, Whammy.

I tried and checked about what you said, but still doesn't work.
These are the setting on my IIS. Please take a look.

http://www33.brinkster.com/shusaku/iis/1.bmphttp://www33.brinkster.com/shusaku/iis/d2.bmp
http://www33.brinkster.com/shusaku/iis/iis.bmphttp://www33.brinkster.com/shusaku/iis/d1.bmp

Squall Leonhart
12-25-2003, 10:19 PM
Whoops

http://www33.brinkster.com/shusaku/iis/d1.jpg
http://www33.brinkster.com/shusaku/iis/1.jpg
http://www33.brinkster.com/shusaku/iis/d2.jpg
http://www33.brinkster.com/shusaku/iis/iis.jpg

whammy
12-26-2003, 02:22 AM
Did you also give IUSR permissions for that directory?

Squall Leonhart
12-26-2003, 09:59 PM
Yes, please have look this



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum