Hi,



I am prototyping and i have a tough problem.  I am running IIS4.0.  I have

an access database.  I get the error :



Microsoft OLE DB Provider for ODBC Drivers error '80004005' 



[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable

query. 



I have cut and pasted SQL that works in an Access 2000 querybuilder, but The

webserver won't have it! 

I have made sure the database is not read only.



excerpt of my code:

<%

Set conn = server.createobject("adodb.connection")

DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "

'DSNtemp=dsntemp & "DBQ=" & server.mappath("database.mdb") remarked out just

to check it wasn't this

DSNtemp=DSNtemp & "DBQ=" &

"c:\inetpub\wwwroot\tristest\ASPUpdateDatabase\database.mdb"

conn.Open DSNtemp

'...

'some other code

'...

    SQLstmt = "UPDATE Prices SET "

    SQLstmt = SQLstmt & "Item='" & TRIM(Request.Form("Item")) & "', "

    SQLstmt = SQLstmt & "Price='" & TRIM(Request.Form("Price")) & "', "

    SQLstmt = SQLstmt & "Ordernumber=" & TRIM(Request.Form("Ordernumber"))

    SQLstmt = SQLstmt & " WHERE ID=" & TRIM(Request.Form("Recordid"))

    %>

    <HR>

    SQL statement: <%=SQLstmt%>

    <HR>

    <%

    Conn.Execute (SQLstmt)

    Response.Write "Operation Complete<br>

%>

Tristian,



	I haven't looked through your code extensively but I noticed one thing

right off.  Access97 (and possibly 2000) requires a trailing semicolon ";"

on the SQL command.  Try appending it after the criteria, like so:



    SQLstmt = SQLstmt & " WHERE ID=" & TRIM(Request.Form("Recordid")) & ";"





	If that doesn't help, write back.  I do know that to be one definite

problem with TransactSQL in Access97 so if it isn't a problem in this

iteration, you will eventually run into it before you get this code

functioning.

:)



KD

Kevin D. Riggs



kevin.riggs@p...



Programmer/Analyst III

UnumProvident Insurance

Chattanooga, TN 37402

(xxx) xxx-xxxx



ICQ - 63816842

AIM - wsi tn



http://www.mindspring.com/~riggsck/

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



Cheers

Ken



----- Original Message -----

From: "Tristian O'brien" 

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, May 15, 2000 11:15 PM

Subject: [asp_databases] Trouble with Update queries





> Hi,

>

> I am prototyping and i have a tough problem.  I am running IIS4.0.  I have

> an access database.  I get the error :

>

> Microsoft OLE DB Provider for ODBC Drivers error '80004005'

>

> [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable

> query.

>

> I have cut and pasted SQL that works in an Access 2000 querybuilder, but

The

> webserver won't have it!

> I have made sure the database is not read only.

>

> excerpt of my code:



<munged>

Nice try, but there is no problem with the code :-)



Everything works now.  In my opinion the whole of NT Server and IIS4 can be

as flaky as a sausage roll.  Basically I tried re-installing option pack

4.0, which of course didn't install properly.  Then i retried this code and

now i can refresh with glee!



Weird!  I would love someone to tell me what is going on, but without

knowing what software i have installed and in which order this task is

probably impossible.







-----Original Message-----

From: Kevin D Riggs 

Sent: 16 May 2000 01:17

To: ASP Databases

Subject: [asp_databases] Re: Trouble with Update queries





Tristian,



	I haven't looked through your code extensively but I noticed one

thing

right off.  Access97 (and possibly 2000) requires a trailing semicolon ";"

on the SQL command.  Try appending it after the criteria, like so:



    SQLstmt = SQLstmt & " WHERE ID=" & TRIM(Request.Form("Recordid")) & ";"





	If that doesn't help, write back.  I do know that to be one definite

problem with TransactSQL in Access97 so if it isn't a problem in this

iteration, you will eventually run into it before you get this code

functioning.

:)



KD

Kevin D. Riggs



kevin.riggs@p...



Programmer/Analyst III

UnumProvident Insurance

Chattanooga, TN 37402

(xxx) xxx-xxxx



ICQ - 63816842

AIM - wsi tn



http://www.mindspring.com/~riggsck/

> I haven't looked through your code extensively but I noticed one thing

> right off.  Access97 (and possibly 2000) requires a trailing semicolon ";"

> on the SQL command.  Try appending it after the criteria, like so:





No it doesn't - sorry.





> If that doesn't help, write back.  I do know that to be one definite

> problem with TransactSQL in Access97



Actually Access doesn't use TransactSQL - it uses JET SQL.



Cheers

Ken