Using the Recordset Object to do DB updates in ADO

This is a question that I have seen alot of lately. When doing DB updates from the ADO, people have been reporting errors saying that they must use a "updateable query".

This is more likely than not, caused by the cursortype used as the default by the server when you open the recordset object. By default, the ADO will use adOpenForwardOnly. Some recordset cursortypes will not allow you to update the database through the RS object. For a better explanation of which cursortype does what check out the link below from MSDN.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjodbrec.asp

There are 2 ways around this. First, assign a different recordset cursortype when you open the recordset.

Or the better way is to not open the RS object at all. Once you have your SQL statement, and you have your database connection, you can run update/insert/delete statements against the DB without ever opening up a recordset. Remember, the "RECORDSET OBJECT" is best used only when you really need to return values from the DB. Why open up a object and create memory space for it on the server if you don't really need it. Try this instead

Set Conn = Server.createobject("adobd.connection")
Conn.open (providerstring)
conn.execute (SQLString)

This will allow you to do all the DML (Data Manipulation Language) commands that you want, and you never had to do anything but open a connection to the DB, which you can't avoid. Better, yet, unlike the RS object, these changes are issued as soon as you issue the command, not when you close the RS like there are when you do them that way.

Hope this helps.