Cannot Insert into the accesss DB!!!! PLS. HELP!!!!

[kazai]

I have an insert statement, which works perfect when I run it on Access. But when I try to run the same thro' ASP, it gives me an error saying:
Error: Operation must use an updateable query.
Well, all the security settings have been set to read/write access to the everyone user, and the table is not in read only mode.
Can some one pls. give me the solution to this pbm.?
Thanks,
Radhika.

 

[FengShui1998]

kazai,

Without seeing any code, it is hard for us to see what your problem is. Are you using the correct cursors? Try writing your insert statement to the screen in asp then plug it in you ACCESS db.



fengshui_1998

 

[jasonsalas]

Try posting the SQL code for your insert statement in the forum so we could see what's going on.

In ASP, your SQL statement should be something like:

INSERT INTO tableName (field1, field2, field3, field4) VALUES (@value1, @value2, @value3, @value4)

...and your ASP script should contain something like this:

<%
Dim objConn, objCmd

' create and open the database object
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;DSN=dsnName&quot;

' create the command object
Set objCmd = Server.CreateObject(&quot;ADODB.Command&quot

' set the command object's properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = INSERT INTO tableName (field1, field2, field3, field4) VALUES (@value1, @value2, @value3, @value4)&quot;
objCmd.CommandType = adcmdText

' execute the command
objCmd.Execute

' close the open objects
Set objCmd = nothing
Set objConn = nothing
objConn.Close
%>

...something to that extent. You could also use the AddNew method, which for your purposes seems to be a bit easier pill to swallow. This takes values entered from a Web form and adds them into database fields.

<%
Dim objRS

' create a new recordset object
objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.AddNew
objRS.Fields(&quot;field1&quot = Request.Form(&quot;field1&quot
objRS.Fields(&quot;field2&quot = Request.Form(&quot;field2&quot
objRS.Update

' close and clean up open objects
objRS = nothing
objRS.Close
%>

The preceding code is pretty similar to the longer SQL statement approach.

 

[kazai]

The Insert statement that I printed out was:

INSERT INTO ResourceUseBegin073101 ( FirstName, LastName, Address1, Address2, City, State, Zip, PhoneHome, PhoneWork, Title, Organization, ItemsOnLoan01, ItemsOnLoan02, ItemsOnLoan03, ItemsOnLoan04, ItemsOnLoan05, NoOfStudents, NoOfTeachers, NoOthers, DateBorrowed, ExpectedReturnDate, Email )
VALUES ('m', 'm', 'm', '', 'm', 'm', '1', '', '12', 'm', 'm', 'm', '', '', '', '', '1', '2', '12', '1/1/2002', '1/16/2002', 'm@w.k');

Now this very same statement works perfect in Access, bt not through ASP....any suggestions???
Pls. Help.
Thanks!!!
-Radhika.

 

[lobstah]

how are you trying to execute this sql statement in asp? i suspect that to be where the problem lies.

if your sql statement is sSQL, then:

' execute the command
objCmd.Execute sSQL

should work.

 

[Swany]

Check the permissions on the directory the access db is located in, and make sure the anonymous internet user has write access to the database file, and create file permission in the directory with the database. The directory should have CREATOR/OWNER permission of modify. .
.. Eat, think and be merry .
... ....................... .

 

[lobstah]

PRB: ASP Returns 'Operation Must Use an Updateable Query' Error

http://support.microsoft.com/support/kb/articles/Q175/1/68.ASP

 

[kazai]

I have checked the permissions for the user , and the 'everyone' user has been given the write permission...
and I do use the obj.Execute(sSQL) statement.
?????!!!!!!! (
Thanks,
-Radhika.

 

[kazai]

Any help guyz??? else, I am doomed.....

 

[lobstah]

does the 'everyone' user include the anonymous user?

 

[Ovatvvon]

Everyone includes everyone.
I have had this problem before.
One thing to check is that your permissions are set right...I know you just said that you had set the folder to allow everyone read/write permissions, but is that for the specific database folder? Make sure the sharing permissions for that specific folder is on read write..and also for the web site as a whole. Also, make sure the database isn't set on &quot;read-only&quot;. Try both of these. This isn't a problem with the code. -Ovatvvon :-Q

 

[Swany]

Are you sure the insert statement that is failing is the one you pasted in?

Are you sure there isn't a recordset being opened, and .update being called somewhere?

The SQL that you posted shouldn't generate that error unless there is a permission problem.

This database file isn't on a remote server is it?

Assuming you are using anonymous web access, the anonymous user needs to have execute and list on all directories leading to the one with the database.

So if you had a dir struct like
c:\databases\web_databases\sitea
then
IUSR_YOURMACHINENAME would need list and execute on c:\databases and c:\databases\web_databases and the user should have modify on c:\databases\web_databases\sitea and all files in it.

Also make sure you don't have a space in any directory names that contain the database. Sometimes the ms-access db driver has problems with that. .
.. Eat, think and be merry .
... ....................... .