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!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UPDATE assistance needed 1

Status
Not open for further replies.
dswitzer

dswitzer

Technical User
Aug 2, 2002
298
0
0
US
I'm racking my brain on this (still new to ASP) -- simplified as much as I can....I am trying to update a record on an access database. My SQL is good, it errors on the line "objConn.Execute strSQL" saying 'Operation must use an updateable query.' -- which leads me to believe it is a problem on my recordset parameters....but I don't see it.
Any ideas appreciated...

"movieReview" is a table in the billTest.mdb database
The movieReview table contains fields including ID and Movie



<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<!-- #include virtual=&quot;/vetroDS/includes/adovbs.inc&quot;-->
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>
<body>

<%
Dim objConn
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
objConn.ConnectionString=&quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot; & _
&quot;DBQ=&quot; & Server.MapPath(&quot;billTest.mdb&quot;)
objConn.Open

Dim objRS
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)
objRS.Open &quot;movieReview&quot;,objConn,adOpenKeyset,adLockBatchOptimistic

strSQL=&quot;UPDATE movieReview SET Movie='XXX' WHERE ID=15&quot;
objConn.Execute strSQL

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

%>
</body>
</html>
 
Sort by date Sort by votes
Unless you're getting values needed for the update from the recordset, I'd not open a recordset there. There's no need, and it might confuse Access, the poor thing. [wink]

Also, and more seriously, I think there might be something wrong with the way you're opening the recordset. Usually, in the first parameter of the Open method on the Recordset object, I pass a SQL statement or a stored proc. I've never seen just passing the database name in that parameter - that might be what's causing Access to choke on the UPDATE.
I'd try getting rid of the recordset and see what happens.

HTH,
jp
 
Upvote 0 Downvote
So, I strip it down more...

When I use:
objConn.Open adOpenKeyset,adLockBatchOptimistic,adCmdTableDirect
-- I get an error at that line:
Data source name not found and no default driver specified

When I use:
objConn.Open (no parameters)
-- I get an error at the objConn.Execute line
Operation must use an updateable query.

--Clearly I am missing something fundamental...




<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<!-- #include virtual=&quot;/vetroDS/includes/adovbs.inc&quot;-->

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>

<body>


<%
Dim objConn
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
objConn.ConnectionString=&quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot; & _
&quot;DBQ=&quot; & Server.MapPath(&quot;billTest.mdb&quot;)
objConn.Open adOpenKeyset,adLockBatchOptimistic,adCmdTableDirect
'objConn.Open

strSQL=&quot;UPDATE movieReview SET Movie='XXX' WHERE ID=15&quot;
objConn.Execute strSQL

objConn.Close
Set objConn = Nothing

%>
</body>
</html>
 
Upvote 0 Downvote
the problem with the original code resides in this block :

Dim objRS
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)
objRS.Open &quot;movieReview&quot;,objConn,adOpenKeyset,adLockBatchOptimistic

strSQL=&quot;UPDATE movieReview SET Movie='XXX' WHERE ID=15&quot;
objConn.Execute strSQL

you're opening &quot;moviereview&quot; in which the recordset object on the open method is looking for a complete SQL statement, it's failing there for starters, secondly, you need to set objconn.execute = to an object in order for the resulting ( even if empty) recordset to be processed.

this code would be less confusing to you and easier to debug if you dropped it to it's essential components :

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
objConn.Open(&quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; & Server.MapPath(&quot;billTest.mdb&quot;))

strSQL=&quot;UPDATE movieReview SET Movie='XXX' WHERE ID=15&quot;
Set objRS = objConn.Execute strSQL

Set objRS = Nothing

save your self some typing and more lines for possible typos, or errors to occur.
 
Upvote 0 Downvote
One other thing to check. Make sure your access database isn't flagged as read only. I've found this same error when trying to update a record on an access database that was marked as a read only file.


 
Upvote 0 Downvote
Good thought. Checked out OK but led me to share the directory -- and that worked!

Thanks to all.
 
Upvote 0 Downvote
The problem with the original statement was with the keyset attribute. I think in order to have an updateable recordset you would have needed to use either dynamic or static, not keyset (or forwardonly).
Using the .execute method can be more efficient, unless your doing a large number of updates. If your doing a large number of updates it may be more efficient to use the recordset object and use batch transactions.

Also, you do not need the recordset object if your doing a conn.Execute on a query that will not return records. As long as you don't use parantheses in the execute it will not return anything.
Code: 
conn.Execute strSQL
is perfectly legal. If you were actually gettiong a recordset back you would want to do this:
Code: 
Set rs = conn.Execute(strSQL)
or
Set rs = conn.Execute strSQl

-Tarwn

[sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Do you know how hot your computer is running at home? I do
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to update the content of radio buttons in a database?
Replies
2
Views
77
leifoet
leifoet
leifoet
  • Locked
  • Question
Classic ASP multiple record update with Access
Replies
7
Views
104
dhookom
dhookom
leifoet
  • Locked
  • Question
How to update current date and time in an databasefield
Replies
1
Views
53
leifoet
leifoet
axLW
  • Locked
  • Question
IsNull assistance needed
Replies
4
Views
50
axLW
axLW
Tord Bergset
  • Locked
  • Question
Classic asp - Only some active directory attributes can be updated.
Replies
3
Views
110
Tord Bergset
Tord Bergset

Part and Inventory Search

Sponsor

Back
Top