need help with ado error (read only)

[bsw2112]

hello
i don't know what is going on with my code
i am trying to add a new record to an access db table and i am getting this error

*****************

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.

*****************

i manualy inserted two records and was able to read them back so i know the conenction is fine.

the line that gives me the error i think is the the objRS.AddNew
and i think the culprit is this line
objRS.open SQL,MyConn,adOpenDynamic,adLockOptimistic,adCmdText



this my code

set objRS = server.createobject("ADODB.recordset")
objRS.open SQL,MyConn,adOpenDynamic,adLockOptimistic,adCmdText


'here is my output of the two records i manualy inserted in the databse

response.write objRS ("q1")
objRS .movenext
response.write objRS ("q1")

' this is the area that causes the error because when when i comment these three lines the error is gone

objRS.AddNew
objRS ("q1") = "some string"
objRS.Update



'closing down now
objRS .close
set objRS = nothing

please does anyone have any suggestions as to how i can make this work? it looks easy but i just don't get it.
in the meantime i will study the parameter settings for the
open method of a recordset object

thanks in advance

bsw

[mendhak]

1) Have you defined the constant values for adOpenDynamic,adLockOptimistic, and adCmdText

2)Instead of objrs.addnew, try

MyConn.Execute "INSERT INTO tablename VALUES('" & strabc & "');"



And see if it works.

Also, what db is it? Access?

[bsw2112]

hi mendhak

i am so perplexed by this.
Yes it's access

i took the code straight from another page of mine that works and then i went to a site and copied code from the internet.

a stripped doen version of the code

	SET objConn = SERVER.CreateObject("ADODB.Connection")
	SET objRS = SERVER.CreateObject("ADODB.Recordset")

	coStr = "DRIVER={Microsoft Access Driver (*.mdb)};"
	coStr = coStr & "DBQ=" & Server.MapPath("db\db1.mdb") & ";"
	objConn.Open(coStr)


	objRS.Open "question1",objConn,2,2

	objRS.AddNew
	objRS("field1") = "string"
	objRS.Update

	'kill objects

	set objRS = Nothing
	objConn.Close
	SET objConn=Nothing

1) data base is 1 level higher in a folder called db
2) question1 is the name of my table
this table has two fields (field1, id) id PK autonumber

now i am just trying to add a string and it's giving me a 500 error

i will try your tip but i still don't get why it's not working the way i have since it worked in the past

Have you defined the constant values for adOpenDynamic,adLockOptimistic, and adCmdText

i tried earlier to add them but it still gave me errors

i took the code from here
http://www.domaindlx.com/asp_db.asp?p=3


thanks for your help...
this is so frustrating
i thought i was making progress but i guess i have a long way to go....


bsw

[bsw2112]

hello again

it's not working for me..
was this how i was to code it?

dim q1
q1 = "some text2"
response.write(q1)



	SET objConn = SERVER.CreateObject("ADODB.Connection")
	SET objRS = SERVER.CreateObject("ADODB.Recordset")

	coStr = "DRIVER={Microsoft Access Driver (*.mdb)};"
	coStr = coStr & "DBQ=" & Server.MapPath("db\db1.mdb") & ";"
	objConn.Open(coStr)


	

objConn.Execute "INSERT INTO question1 VALUES('" & q1 & "');"


	

	objConn.Close
	SET objConn=Nothing

bye mendhak

[mendhak]

	SET objConn = SERVER.CreateObject("ADODB.Connection")
	SET objRS = SERVER.CreateObject("ADODB.Recordset")

	coStr = "DRIVER={Microsoft Access Driver (*.mdb)};"
	coStr = coStr & "DBQ=" & Server.MapPath("db\db1.mdb") & ";"
	objConn.Open(coStr)


	objRS.Open "SELECT * FROM question1",objConn,2,2


objConn.Execute "INSERT INTO question1(field1) VALUES ('" & stringvalue & "');" 


	'kill objects

	set objRS = Nothing
	objConn.Close
	SET objConn=Nothing

1) data base is 1 level higher in a folder called db

higher? So shouldn't the path be

coStr = coStr & "DBQ=" & Server.MapPath("../db1.mdb") & ";"


The .AddNew method is rather, uhm.... stupid. With the INSERT string above, you can be sure the autonumber field gets auto-incremented.

[mendhak]

Originally posted by bsw2112
hello again

it's not working for me..
was this how i was to code it?

dim q1
q1 = "some text2"
response.write(q1)



	SET objConn = SERVER.CreateObject("ADODB.Connection")
	SET objRS = SERVER.CreateObject("ADODB.Recordset")

	coStr = "DRIVER={Microsoft Access Driver (*.mdb)};"
	coStr = coStr & "DBQ=" & Server.MapPath("db\db1.mdb") & ";"
	objConn.Open(coStr)


	

objConn.Execute "INSERT INTO question1 VALUES('" & q1 & "');"


	

	objConn.Close
	SET objConn=Nothing

bye mendhak

Note the change in the INSERT string in my previous post.

[bsw2112]

higher? So shouldn't the path be

coStr = coStr & "DBQ=" & Server.MapPath("../db1.mdb") & ";"

sorry i meant to say that it's on the same level bu in a folder called db

i will try again to see if i can get it to work and will post back

thanks mendhak
btw nice avartar

bsw

[bsw2112]

once again you saved the day

your last code worked (at least on PWS) i hope it works on my site too.

the wierd thing is that this code works now too
i renamed the database to survey.mdb

<!--#include file="adovbs.inc"-->


<%

dim q1
q1 = "text"


	SET objConn = SERVER.CreateObject("ADODB.Connection")
	SET objRS = SERVER.CreateObject("ADODB.Recordset")

	coStr = "DRIVER={Microsoft Access Driver (*.mdb)};"
	coStr = coStr & "DBQ=" & Server.MapPath("survey.mdb") & ";"
	objConn.Open(coStr)


	SQL = "select * from question1"
	objRS.open SQL,objConn,adOpenDynamic,adLockOptimistic,adCmdText

	objRS.AddNew
		objRS("field1") = q1
	objRS.Update

	objConn.Close
	SET objConn=Nothing
	SET objRS = Nothing
%>

thanks mendhak

[bsw2112]

ok this is weird

on PWS (win 98 se)

this code runs and inserts the string into my database

<!--#include file="adovbs.inc"-->


<%


	dim q1

	q1 = "test string"


	SET objConn = SERVER.CreateObject("ADODB.Connection")
	SET objRS = SERVER.CreateObject("ADODB.Recordset")

	coStr = "DRIVER={Microsoft Access Driver (*.mdb)};"
	coStr = coStr & "DBQ=" & Server.MapPath("survey.mdb") & ";"
	objConn.Open(coStr)


	SQL = "select * from question1"
	objRS.open SQL,objConn,adOpenDynamic,adLockOptimistic,adCmdText

	objRS.AddNew
		objRS("field1") = q1
	objRS.Update


	objConn.Close
	SET objConn=Nothing
	SET objRS = Nothing
%>

but on my personal website and at work this does not work at all and i get a 500 error with no explanation

but if i comment out the three lines in bold, the script runs
(of course no addition takes place but at least it doesn't throw an error

why is this happening?

this works

<!--#include file="adovbs.inc"-->


<%


	dim q1

	q1 = "test string"


	SET objConn = SERVER.CreateObject("ADODB.Connection")
	SET objRS = SERVER.CreateObject("ADODB.Recordset")

	coStr = "DRIVER={Microsoft Access Driver (*.mdb)};"
	coStr = coStr & "DBQ=" & Server.MapPath("survey.mdb") & ";"
	objConn.Open(coStr)


	SQL = "select * from question1"
	objRS.open SQL,objConn,adOpenDynamic,adLockOptimistic,adCmdText


 ' now there are no erros ...
	'objRS.AddNew
	'	objRS("field1") = q1
	'objRS.Update


	objConn.Close
	SET objConn=Nothing
	SET objRS = Nothing
%>

if you know the answer it would help so much and possibly eliminate this type of error from my error repertoire

thank you
bsw

ps i will try you rcode now to see if it works live

[bsw2112]

i think i found the problem
this is a working version of what i was doing
http://www.wawryn.com/surv/surv_e.asp
can't believe i spent 4hrs debuggin something that wasn't broken...
i will avoid AddNew if you sday it's not the best way to go.

on my site it only works if i put in a database folder that my host provided. Running PWS it works from anywhere.
I bet it's the same reason it's not working at work.

It's funny cause this issue only arises when i try to write to the database... the database can by anywhere if all i want to do is read the data.

thanks again for your helpful advice
maybe one day i will be able to help you out .... not

unless you need some graphic design help (that's what i did before my boss found out i knew a little javascript. now that's all it seems i do these days (still i find that it doesn't take much for me to get confused...
then again...judging by your site i doubt you will need too much help in that department either

bsw

[mendhak]

With PWS, it'll work anywhere, because the database folder doesn't need permissions set to it. For your website, your administrator, for security reasons, has set aside just one folder for databases. Similar to something brinksters does. That's why it works there only.



I don't know the exact technical reason, but I have never known the .Addnew method to be reliable. I've always used the connection object's execute method because this way I can am assured it will work. Besides, with the execute method, I get to write SQL strings.

All I can say is, rule of thumb:

reading from db: use recordset object.
writing to db: use connection object.



PS: you from vancouver?

[bsw2112]

hi mendhak
no i am in ottawa (work for government)
i am helping our web design person with a couple of script that our client wanted implemented. one of them is a survey which you saw one of the questions on my dev site

once again thanks for your advice and interest in my problem
bsw

[mendhak]

No probs.