what does this ADO OPEN Method do?

Question

in plain english can you explain to me what happens here:

rs.Open "batchinfo", oConn, adOpenKeyset, adLockOptimistic, adCmdTable

what i need to do is to be able to insert values into a table called batchinfo. would this be the best way to do an OPEN?

the only thing i would be doing is inserting values.

Answer 1

rs is the RecordSet object. (A recordset is like an in-memory table) This line of code causes it to open the connection to the database and get the data, by calling the stored procedure named "batchinfo" or possibly just selecting all rows from a table or view named "batchinfo".

This method is fully documented here.

One option would be to open the recordset , update the values in the recordset in memory, and then call the Save() method. (See this link for examples and how to do it this way.)

However, this is NOT the most efficient way to update data. You're better off using an Update or Insert command

See this tutorial

Answer 2

The use of adCmdTable typically results in ADO generating a select * from thetable command that is executed by the OLE DB provider. If you update the resulting recordset, the provider (or actually ADO on top of it) typically generates statements of the form update table set field = ?, ... WHERE .... The WHERE clause is the potential issue. If there is a primary key on the table, then the WHERE clause will include that primary key of the updated record and thus be fairly efficient in its execution. Since you are only inserting values, it should result in simple INSERT statements being generated and should be very efficient.

Note that if the provider supports it, then adCmdTableDirect will result in a direct table open, and updates will be extremely efficient. I don't know if MySQL supports that option; I don't think very many providers do support that.

Answer 3

I hope that this is going to help you.

dim sql, value
dim connection, rs

sql = "SELECT * FROM batchinfo"
value = "foo"

set connection = Server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")

connection.open "Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=192.168.0.1;Initial Catalog=Northwind;User ID=userId;Password=1234"
rs.open sql, connection, 0, 3, 1

rs.addNew
rs("fieldName1") = replace(value, "''", "'")
rs("fieldName2") = replace(value, "''", "'")

rs.update
rs.close
set rs = nothing