Problem with Adding new record to ACCESS DB 1

ivan9843 · Dec 27, 2003

Hello, Everybody
This is what I get when i am trying to add a new record to a DB:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.
/Opinion.asp, line 30

This is my code:
<%@ Language=VBScript %>
<%option explicit%>
<html>
<head>
</head>
<body bgcolor="#66ffcc">
<%
dim cnMyConnection
dim strConnectionString
dim rsMyRecordset

set cnMyConnection = Server.CreateObject("ADODB.Connection&quot

strConnectionString="Driver={Microsoft Access Driver (*.mdb)};dbq=" & Server.MapPath("YourOpinion.mdb&quot

set rsMyRecordset = Server.CreateObject("ADODB.Recordset&quot

cnMyConnection.Open strConnectionString

rsMyRecordset.Open "YourOpinion", cnMyConnection, 1 , 2

rsMyRecordset.AddNew
rsMyRecordset.Fields("Date&quot

= Request.Form("txtDate&quot

rsMyRecordset.Fields("FirstName&quot

= Request.Form("txtFirst&quot

rsMyRecordset.Fields("LastName&quot

= Request.Form("txtLast&quot

rsMyRecordset.Fields("Email&quot

= Request.Form("txtEmail&quot

rsMyRecordset.Fields("Message&quot

= Request.Form("txtMessage&quot

rsMyRecordset.Update ' this is line 30 on the page..
set rsMyRecordset = Nothing
rsMyRecordset.Close
set cnMyConnection = Nothing
cnMyConnection.Close
%>
I think I trying everything...
I am running IIS on WIn XP... anybody have a solution!!!
Thanks ahead...

sharedworld · Dec 27, 2003

you have to give write permissions to the folder where the database is located, which means that read-only option available when you right-click on the folder where your database is located should be disabled, then everything gonna work for you.

And by the way, when your opening your recordset, i would better do it like this:
rsMyRecordset.Open "YourOpinion", cnMyConnection, 3 , 3
instead of 1,2

information on this is available on

http://www.w3schools.com/ado/met_rs_open.asp

LV · Dec 27, 2003

Wouldn't it be easier to just execute an INSERT statement(of course, enabling the read/write file premissions first)?

sharedworld · Dec 27, 2003

In fact the way ivan is doeing it is easier than with the INSERT statement, because a lot of times, your code is too long, let's say that you have to format or trim the values before putting they in the database, so that way is easier, for me at list... but there is any differances in the speed of the script?

LV · Dec 28, 2003

Well, to me it seems that you have to open a recordset against the table and then still execute an "inderect" INSERT anyways, instead of just executing one statement. Also you don't have to instantiate an ADODB.Recordset object, while executing the INSERT, onlyConnection object would be needed.

Code:

set cnMyConnection = Server.CreateObject(&quot;ADODB.Connection&quot;)
strConnectionString=&quot;Driver={Microsoft Access Driver (*.mdb)};dbq=&quot; & Server.MapPath(&quot;YourOpinion.mdb&quot;)
cnMyConnection.Open strConnectionString
strSQL = &quot;INSERT INTO TableName(Date,FirstName,LastName,Email,Message)&quot; & _
&quot; VALUES('&quot; & Request.Form(&quot;txtDate&quot;) & &quot;','&quot; & _
Request.Form(&quot;txtFirst&quot;) & &quot;','&quot; & _
Request.Form(&quot;txtLast&quot;) & &quot;','&quot; & _
Request.Form(&quot;txtEmail&quot;) & &quot;','&quot; & _
Request.Form(&quot;txtMessage&quot;) & &quot;')&quot;
cnMyConnection.Execute strSQL

cnMyConnection.Close
set cnMyConnection = nothing

ivan9843 · Dec 28, 2003

Thanks LV, at least now i don't have any permission problems, but here is what I am getting now...
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/Opinion.asp, line 28 ' This line is cnMyConnection.Execute strSQL on the page, I am not that big with SQL, and took your example exactly,
please, help... is there a possible misspelling there?
thanks

sharedworld · Dec 28, 2003

change this line
strSQL = "INSERT INTO YourOpinion(Date,FirstName,LastName,Email,Message)" &

LV · Dec 28, 2003

Exactly like sharedworld stated. Sorry, I just entered TableName as a sample name of the table to insert into. Should be YourOpinion.

ivan9843 · Dec 28, 2003

LV and shareworld, you guys were very helpful, but I guess this is not a problem, cause I put my Table Name right away, problem is somethere else..
here is that page on my web :

http://leva.homeip.net/YourOpinion.asp,

if somebody can help, please do....
ivan

ivan9843 · Dec 28, 2003

This is how my page looks right now:
<%@ Language=VBScript %>
<%option explicit%>
<html>
<head>
</head>
<body bgcolor="#66ffcc">
<%
dim cnMyConnection
dim strConnectionString
dim rsMyRecordset
dim strSQL

set cnMyConnection = Server.CreateObject("ADODB.Connection&quot

strConnectionString="Driver={Microsoft Access Driver (*.mdb)};dbq=" & Server.MapPath("YourOpinion.mdb&quot

'set rsMyRecordset = Server.CreateObject("ADODB.Recordset&quot

cnMyConnection.Open strConnectionString

strSQL = "INSERT INTO YourOpinion(Date,FirstName,LastName,Email,Message)" & _
" VALUES('" & Request.Form("txtDate&quot

& "','" &_
Request.Form("txtFirst&quot

& "','" &_
Request.Form("txtLast&quot

& "','" &_
Request.Form("txtEmail&quot

& "','" &_
Request.Form("txtMessage&quot

& "')"

cnMyConnection.Execute strSQL
cnMyConnection.Close
set cnMyConnection = Nothing

%>

<h2 align="center">Thank you for your time, your message was sent successfully</h2>
<h4 align="center"><a href="YourOpinion.asp" target="Main">Back to YourOpinion Page</a></h4>

</body>
</html>

and error is like this:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/Opinion.asp, line 28 '

LV · Dec 28, 2003

Could you please do the following:

Code:

...........
strSQL = &quot;INSERT INTO YourOpinion(Date,FirstName,LastName,Email,Message)&quot; & _ 
&quot; VALUES('&quot; & Request.Form(&quot;txtDate&quot;) & &quot;','&quot; &_
Request.Form(&quot;txtFirst&quot;) & &quot;','&quot; &_
Request.Form(&quot;txtLast&quot;) & &quot;','&quot; &_
Request.Form(&quot;txtEmail&quot;) & &quot;','&quot; &_ 
Request.Form(&quot;txtMessage&quot;) & &quot;')&quot;

Response.Write strSQL
Response.End

copy the statement and post it here?

LV · Dec 28, 2003

OK, here is the fix. Sorry, my error. "Date" is a keyword, and in the statement it should be surrounded by []. So it should look like this:

Code:

strSQL = &quot;INSERT INTO YourOpinion
([Date],FirstName,LastName,Email,Message)&quot; & _ 
&quot; VALUES('&quot; & Request.Form(&quot;txtDate&quot;) & &quot;','&quot; &_
Request.Form(&quot;txtFirst&quot;) & &quot;','&quot; &_
Request.Form(&quot;txtLast&quot;) & &quot;','&quot; &_
Request.Form(&quot;txtEmail&quot;) & &quot;','&quot; &_ 
Request.Form(&quot;txtMessage&quot;) & &quot;')&quot;

ivan9843 · Dec 28, 2003

You guys really working hard on this, thanks a lot...
but now 2 things
1)statement from Response.Write is:
INSERT INTO YourOpinion(Date,FirstName,LastName,Email,Message) VALUES('12/28/2003 6:37:16 PM','1','1','1','1')
2) after I put the Date in [], here is what kind of error I get:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
/Opinion.asp, line 29

What now? ....
Ivan

LV · Dec 28, 2003

Did you look at my previous post with a (hopefuly) fix?

LV · Dec 28, 2003

Sorry, didn't notice you've already done that. What happens when you execute that copied INSERT string directly against the database?

ivan9843 · Dec 28, 2003

I am sorry what do you by that?

ivan9843 · Dec 28, 2003

I meant what do you mean by that? sorry

LV · Dec 28, 2003

You can try to execute this statement against your Access database and see if it cause the same(or other) error. To do that, open your Access db, go to "Queries" and select "Create query in Design view". Close the "Show Table" dialog and click on "SQL" button in the upper left corner of the screen. Paste the above (from Response.Write) INSERT statement into the field(if should display "SELECT;" by default) and hit the "!" toolbar button. If there is no errors in the statement, it shoulf display the "You are about to append 1 row(s)..." dialog box, or an error otherwise.

ivan9843 · Dec 29, 2003

Hi LV, it's being a pain in a but....
it run a query in Access, but it gives me this kind of error on the web:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
/Opinion.asp, line 30

aspmonster · Dec 29, 2003

This happens to most users who upgrade to XP or sometimes even on Windows 2000 pro - on Win 98 never had the problem.
It happens after you open the Access database. The only way I found to fix it, was to create another folder, for example "database1", copy the DB into it, than delete the folder were the DB was before. Now rename "database1" to "database" - assuming this is the name of the original folder.
Now go to Windows Explorer, find the folder, right click on it, click properties, then security tab > click Everyone > check full control > Apply

If anybody else knows an easier way, I'd love to know it.

Another thing, if you need to view/modify your Access database often, create another folder (i.e. DatabaseView) and use it to place the db in there to make changes and view. When you are done with it, copy it back into "Database"

These issues happen with Win XP. Its already recorded with Microsoft as a bug.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problem with Adding new record to ACCESS DB 1

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor