Rs. Open SQL, Conn and conn.exe cute (SQL)

Rs. Open
SQL, Conn and conn.exe cute (SQL)

Use conn.exe cute (SQL)
If you want to perform complex operations on the database! It is better to use Rs. Open SQL, Conn

 
Rs. Open SQL, Conn: A record set is returned. This record set can be moved before and after and can be used to execute select statements. Open records can be displayed by page, but records opened in this way cannot be sorted.
 

 
Conn.exe cute (SQL): The record set can also be returned, but it can only be read-only and only forward record set. It is generally used to execute update, insert, or delete statements.
After executing the SQL statement, the system returns the result and obtains the record set. The filtered record set is relatively simple. However, the record set obtained in this way cannot be displayed by page.
 

  Rs. open SQL, Conn : If the SQL statements are Delete, update, and insert statements, a closed record set is returned. Do not set Rs during use. close and write RS at the end of the file. multiple Record Sets rs1.open sql1, Conn, 
 last closed record set: Rs. close rs1.close 
  conn.exe cute (SQL) : using cute SQL 
 If the SQL statement is a SELECT statement, you must use the set rs1_conn.exe cute (SQL) Brackets. It cannot be omitted: this is because of the features of VB and contains the returned values. The call must be enclosed in parentheses. Call without return values can be performed without parentheses. 

Rs. Open SQL, Conn: Set
Rs = server. Createobject ("ADODB. recordset ")

SQL = "select * from Admin where username = 'xiaozhu '"
Rs. Open SQL, Conn, 1, 1
'You can set some parameters, that is, the locking and cursor moving methods.
'Close the record set and connection.
Rs. Close
Set rs = nothing
Conn. Close
Set conn = nothing

Conn.exe cute (SQL): SQL = "select * from Admin where
Username = 'xiaozhu '"
Set rsw.conn.exe cute (SQL)
'Records are automatically disabled after execution.
'Finally, you only need to close the connection.
Conn. Close
Set conn = nothing

1. Set rs1_conn.exe cute ("if it is a SELECT statement") to obtain Rs. recordcount =-1

Rs. Open SQL, And Conn ("SQL is SELECT statement"). Rs. recordcount is the normal number of records.

2. Rs. Open is to open the database conn.exe cute is to execute SQL commands
Set rs1_conn.exe cute (insert, update, delete) returns a closed record set

Set rs1_conn.exe cute (select) returns a record set that is not closed

3.conn.exe cute SQL is applicable when a recordset object is not returned. For exampleCode:
SQL = "delete from enews where
Id = "& CSTR (ID)
Conn.exe cute SQL

To return A recordset object, use the following code:
SQL = "select from enews where
Id = "& CSTR (ID)
Set rs = conn.exe cute (SQL)

If you remove the parentheses (set rs =) in execute (SQL ),
Conn.exe cute SQL), the system will prompt an error like "the statement is not over". I looked at the syntax format and found that there are two formats. The details are as follows:

Format 1: connection object name. Execute (SQL command ).
Format 2: connection object name. Execute (data table name ).

Appendix 1:

Set rs
= Conn.exe cute (SQL)

It is returned. It is generally used when SQL is used for query. For example
Set rs =
Conn.exe cute ("select * from
...")
Of course, you can execute insert into and delete.
Set rs =
Conn.exe cute ("insert into table
(F1, F2 ..")
However, you need to create a recordset object to occupy server resources. In insert
During into and delete operations, we generally do not need to return record sets. So I think:

Set rs
= Conn.exe cute ("select *
From table ")
Use
Conn.exe cute ("delete from table
Where ...")

Method 2: the code is concise and you do not need to explicitly Create A recordset object (but it seems to be implicitly created)
The second method also has some concise writing methods: for example, the number of records in the most specific table

Dim
Count
Count = conn.exe cute (select
Count (*) from Table) (0)
The preceding statement is equal
Count = conn.exe cute (select
Count (*) from table). Fields (0). Value

To sum up, the difference is that set rs =
Conn.executeindicates the created recordsetobject, and conn.exe cute is implicit.
Conn.exe cute can be used when conn.executecan be used.
I. concise code
2. efficiency should be improved

Appendix 2:

There are three methods to form a record set: 1.conn.exe cute 2. Rs. Open SQL. The usage of the conn 3.command.exe cute method is greatly unavailable.
Usually set
Rsw.conn.exe cute (SQL) or directly
Rsw.conn.exe cute (SQL) and

Set rs = server. Createobject ("ADODB. recordset ")

Rs. Open SQL, Conn, 0, 1, or Rs. Open SQL, Conn
In this case, open is usually a little better than the previous one.

Use conn.exe cute to directly execute the next set
Rsw.conn.exe cute (SQL), but the performance speed will be greatly reduced !! It takes about 2 ~ 5 times !!

(This is my own test in MSSQL loop for 1000 times. If you don't believe it, test it on your own right away.) So before executing the next rsw.conn.exe cute !! Be sure to close it! It's a good habit!

Use the RS of a single object
Rs. Open must be displayed and closed before opening the next record set. Otherwise, an error occurs. The speed is basically the same in the case of SQL, Conn, 0, 1.

Therefore, I like to set up a Createobject ("ADODB. recordser") directly. (If you do not need multiple record sets to be staggered, no problem will occur !)

Use it from the beginning !!!

Open and Close the door ~~~~~ Step by step ~~~~
Last
Set rs = nothing is finished, great!

More importantly, open provides a lot of attributes for flexible operation of database metadata !!
Open the treasure chest and see everything!

In many cases, open is not required!

Conn.exe cute wants to change the film to a black cloth ~~~ Sneaky ~~ Very single !!!


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 record set written to database


Rs. addnew' adds a new record.
RS (0) = 1
RS (1) = ddd
RS (2) = KKK
Rs. Update
Id = RS (3) RS (s) '.
Get the automatic ID of the new record immediately // This is very common


Rs1.addnew add join ID to RS1
New Records of RS
RS1 ("ID") = ID
RS1 (1) =...
..
Rs1.update

Rs1.close: Set RS1 = nothing
Rs. Close: Set rs = nothing


For specific operations, if conn.exe cute is used, four conn.exe cute

Conn.exe cute ("Update ..")
Conn.exe cute ("insert into Table 1 :")
Rsw.conn.exe cute ("New Automatic select
ID ")
Conn.exe cute ("insert into Table 2 ")
That method is more logically organized ~~ Easy to understand


======================================

There is also an interesting way to use recordset open record !!
I think it's a bit confusing.

Set rs = server. Createobject ("ADODB. recordset ")
Set rs = server. Createobject ("ADODB. recordset ")

Rs. Open sql1, Conn
Rs. Open sql2, Conn

'Hey ~~~ This is to set two objects with the same name at the same time to open two record sets, and it is available!
'As long as the retrieved column name conforms to the metadata of these two sets of records
'Operation is normal !!
Extended

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. Open sql2, Conn
Rs. Open sql3, Conn
Rs. Open sql4, Conn

I have not tried more than two Rs with the same name !!


It seems a bit similar to querying with SQL and then opening the record set ~~~

I used this before !! It should not be dazzling !!

====================

When and when?
The feeling of personal experience accumulation is as follows:
If you only need to touch the database with one stroke, execute

If you want to perform complex operations on the database! It is best to use
Rs. Open

Appendix 3: Rs. Close () and conn. Close (), set rs = nothing and set
Conn = nothing

The conn database object can be simply understood as an MDB file or a SQL database (note that this understanding method is incorrect, strictly speaking, it should be an "object" that establishes a connection with that database .)

RS recordset object (record set) when only reading data, it can be understood as a series of rows retrieved from a database. When used to update data,

It can be understood as the remote control that connects to the specified row in the database. Now, we should understand when to disable Conn?

Open the database. If you do not need to perform database operations below, you should immediately close it.

Different pages have to be reconnected. They have nothing to do with the operations on the previous page. Open the database before you close other pages.

for resource conservation and server load reduction, the sooner the database is opened, the sooner the database is closed, and the better.