The difference between conn.execute and rs.open is that the usage of conn.execute, rs.open and command.execute methods are very different.
Usually to form a record set, you can use
Set rs=conn.execute (SQL)
or directly
rs=CONN.execute(SQL)
and
Set rs=Server.CreateObject("ADODB.Recordset")
rs.open SQL,CONN,0,1 or rs.open SQL,CONN
In this case, the performance of open is usually slightly better than the previous one.
(The command object is more flexible and has a wider execution range)
Using CONN.execute, you can directly execute the next set rs=conn.execute(SQL) without closing it.
But the performance and speed will be greatly reduced!!
It's about 2~5 times slower!! (I personally tested this in msSQL loop 1000 times, if you don't believe it, test it yourself immediately), so before executing the next rs=conn.execute!! Be sure to close it! It's
a good idea Habit!
When using a single object's rs rs.open, before opening the next record set, the display must be closed, otherwise an error will occur. The speed is basically the same in the case of SQL, CONN, 0, 1,
so I like to use a CreateObject(" adodb.recordser" (If there is no need to interleave multiple record sets, multiple records are not a problem!)
Then use it from beginning to end!!!
Open and close. Open and close. The last set rs=nothing comes to an end!
More importantly, open provides a very flexible way to operate many attributes of database metadata!!
When you open the treasure box, you can see everything in the golden light!
There are many situations where you have to use open!
And conn.execute wants to change the film under the black cloth~~~sneak~~very simple!!!
For example:
rs1.open SQL1,conn,0,1
rs.open SQL,conn,1,3
if rs(0)<0 then rs(0)=0
rs(1)=aaa
rs(2)=bbb
rs.update 'Update the record set and write it to the database
rs.addnew 'Add a new record
rs(0)=1
rs(1)=ddd
rs(2)=kkk
rs.update
id=rs(3) rs(s) 'The field taken is the automatic number. Immediately obtain the automatic number id of the new record. //This is very commonly used.
rs1.addnew adds a new record related to rs1 using the id rs.
rs1("id"=id
rs1(1)=...
..
rs1.update
rs1.close:set rs1=nothing
rs.close:set rs=nothing
If CONN.execute is used for the above operation, four CONN.executes will be used
CONN.execute("update .."
CONN.execute("insert into table 1:"
rs=CONN.execute("select new automatic id"
CONN.execute("insert into table 2"
Which method is more logical and clear at a glance?
There is also an interesting way to use Recordset open to record!!
It's a bit confusing when I think of it.
Set rs=server.CreateObject("Adodb.recordset"
Set rs=server.CreateObject("Adodb.recordset"
rs.open SQL1,CONN
rs.open SQL2, CONN
sets two objects with the same name at the same time to open two record sets, and they are available!
'As long as the column names are consistent with the metadata in these two record sets
'Then operation is normal!!
Expand
Set rs=server.CreateObject("Adodb.recordset"
Set rs=server.CreateObject("Adodb.recordset"
Set rs=server.CreateObject("Adodb.recordset"
Set rs=server.CreateObject("Adodb.recordset"
rs.open SQL1,CONN
rs.openSQL2,CONN
rs.open SQL3,CONN
rs.open SQL4, CONN,
which is larger than two rs with the same name!!
It feels a bit like a joint query with SQL and then open the record set~~~
I've used it like this before!! It shouldn't be dazzling!!
===============
When to use what?
That’s just based on my personal experience:
If you only need to touch the database once, use execute
If you want to perform complex operations on the database, it is best to use rs.open