Novice School: Detailed explanation of rs.open and conn.execute

Author：Eve Cole Update Time：2009-06-25 17:45:03

1. RS.OPEN SQL,CONN,A,B,C

2. CONN.EXECUTE(SQL,RowsAffected,C)

Parameter meaning:

The value of SQL can be an SQL statement, table name, stored procedure name, or a data provider Any string accepted. In order to improve performance, it is best to specify appropriate values for the C parameter.

The optional parameter RowsAffected will return the number affected after the execution of the INSERT, UPDATE or DELETE query. These queries will return a closed Recordset object.

A SELECT query will return a RowsAffected value of -1 and return an open Recordset with one or more rows of content.

<% 
set Conn=Server.CreateObject("ADODB.Connection") 
Conn.open "Provider=SQLOLEDB;Password=xiaolu;User ID=sa;Database=Test;Data Source =127.0.0.1" 
conn.execute "update Table1 set Col1='123'",RowsAffected,&H0001 
Response.Write RowsAffected&" Rows Affected" 
Conn.close 
Set Conn=Nothing 
%>

A: ADOPENFORWARDONLY (=0)

is read-only, and the current data record can only be moved downwards.

ADOPENKEYSET (=1)

is read-only, and the current data record can be moved freely

ADOPENDYNAMIC (=2)

is read-write, and the current data record can be moved freely

ADOPENSTATIC ( =3)

Readable and writable, the current data record can be moved freely, and new record

B can be seen: ADLOCKREADONLY (=1)

Default lock type, the record set is read-only, and records cannot be modified

ADLOCKPESSIMISTIC (=2)

Pessimistic lock, When a record is modified, the data provider will attempt to lock the record to ensure successful editing of the record. As soon as editing begins, the record is locked.

ADLOCKOPTIMISTIC (=3)

optimistic locking, the record is not locked until the updated record is submitted using the Update method.

ADLOCKBATCHOPTIMISTIC (=4)

batch optimistic locking, which allows multiple records to be modified. The records are locked only after calling the UpdateBatch method.

When no records need to be modified, a read-only recordset should be used so that the provider does not need to do any detection.

For general use, optimistic locking is probably the best option, since records are only locked for a short period of time,

during which time the data is updated. This reduces resource usage.

C: (Specify SQL statement type)

ADCmdUnknown (= &H0008)

is unknown and needs to be judged by the system. The speed is slow and is the default value.

ADCmdText (= &H0001)

command statements such as SQL statements

such as: Select * from Table1

ADCmdTable (= &H0002)

query Table name, for example: Table1

ADCmdStoredProc (= &H0004)

stored procedure name

ADCmdFile (= &H0100)

object type corresponding file name

ADCmdTableDirect (= &H0200)

is the table name that can directly obtain row content from the table

conn.execute(sql)(0 ) is the value of the first field of the data set