Update query

Update query

Post by c21rMj » Mon, 01 Nov 2004 09:46:01


I am a newbie at ADO code and would like to update a recordset as follows:

rs.Open "UPDATE OccMag AS omg SET omg.AxSeroma = [chkAxSeroma], " & _
"WHERE omg.AilmentID= " & Me.AilmentID & ";"

My connection string is fine, but I get an error on this line.
Should it be rs.Update...? or what?
Thanks so much.
--
sam
 
 
 

Update query

Post by Unicor » Mon, 01 Nov 2004 18:56:33

that query does not look like it would return a recordset. Try using the
execute method of the database object.

Matt

 
 
 

Update query

Post by Kurpo » Wed, 03 Nov 2004 07:32:51

change rs.open to cnn.execute

you could use rs.update but you must first select the record first.
 
 
 

Update query

Post by Val Mazu » Wed, 03 Nov 2004 12:04:48

Hi,

First of all you cannot open recordset using action query, like
INSERT/UPDATE/DELETE. You would need to use Execute method of ADO Command to
do this. Second, you need to use parameterized query, instead of
concatenation to avoid possible SQL injection. See next example how to do it
properly

http://www.yqcomputer.com/ ;en-us;200190

--
Val Mazur
Microsoft MVP
 
 
 

Update query

Post by Mark J. Mc » Wed, 03 Nov 2004 13:24:58

If SQL Server is the provider, you cannot alias the target table in an
update statement, to do so will cause and error:

cn.Execute "UPDATE OccMag SET OccMag.AxSeroma = [chkAxSeroma], " & _
"WHERE OccMag.AilmentID= " & Me.AilmentID & ";"

(Where cn is an open connection.)

Unless you're updating from a joined source, you do not need to prefix the
column names.


-Mark