Detailed understanding and distinction of ASP (VBS) Rs.Open and Conn.execute and &h0001 Description _asp Foundation

1. Rs.Open Sql,conn,a,b,c
2. Conn.execute (SQL,ROWSAFFECTED,C)

First, Conn.execute (SQL,ROWSAFFECTED,C)

Parameter meaning:

The SQL value can be either an SQL statement, a table name, a stored procedure name, or any string that the data provider can accept. In order to improve performance, it is best to specify the appropriate value for the C parameter
Optional parameter rowsaffected
1. After an insert, update, or delete query executes, rowsaffected returns the number of effects that will return a closed Recordset object.
2, a SELECT query returns the rowsaffected value of-1 and returns an open Recordset with one or more rows of content.

<%
set Conn=opendatabase ' (see the OpenDatabase function in ASP to establish a connection method with access and MSSQL database)
conn.execute "Update Table1 set Col1= ' 123 ', rowsaffected,&h0001
Response.Write rowsaffected& ' row affected '
conn.close
Set conn= Nothing
%>

Second, Rs.Open Sql,conn,a,b,c

Set rs=server.createobject ("ADODB. RecordSet ")
sql=" xxxx "
rs.open sql,conn,1,1,&h0001
irs=rs.getrows (10)

&H0001What is the meaning? The first time I saw this, I was puzzled for a while. Now the website about this information is flying. To mention here, the following parameter descriptions are specified in the description.

A parameter: Cursor type

Const Adopenforwardonly=0 read-only, default. Cursors within a Recordset are only allowed to move forward. Paging, Recordset, BookMark not supported
Const Adopenkeyset=1 is read-only, keyset-cursor, the current data record is free to move, and other users ' changes to the record are reflected in the recordset, but other users adding or deleting records are not reflected in the recordset. Support paging, Recordset, BookMark
The Const adopendynamic=2 can read and write, the current data record is free to move, dynamic cursors are the most powerful, but the most expensive sources. User changes to the record, add or delete records will be reflected in the recordset. Supports full feature browsing (not supported by Access).
Const adopenstatic=3 static cursors, just a snapshot of the data, changes that the user makes to the record, adding or deleting records are not reflected in the recordset. Supports moving forward or backward

B Parameter: Lock type

Const Adlockreadonly=1 defaults, Recordset is read-only, records cannot be modified
Const adlockpessimistic=2 ' pessimistic locking, which locks records as soon as they are edited to ensure successful editing of records. The safest way.
Const Dlockoptimistic=3 ' optimistic locking until records are locked with the Update method, while other previous operations can still make changes, inserts, and deletions to the current record
Const dlockbatchoptimistic=4 ' Bulk optimistic locking allows you to modify multiple records and lock records only after calling the UpdateBatch method.
When you do not need to change any records, you should use a read-only recordset so that the provider does not have to do any testing. For general use, optimistic locking may be the best option because the record is only locked for a short period of time and the data is updated during this time period. This reduces the use of resources.

C parameter: (Specify SQL statement type)

Const adcmdunknown=&h0008 ' unknown, need system to judge, slow, defaults
Const adcmdtext=&h0001 ' will execute a section of SQL
Const adcmdtable=&h0002 ' will manipulate a table
Const adcmdstoredproc=&h0004 ' will manipulate the stored procedure name
Const adcmdfile= &h0100 ' object type corresponding file name
Const adcmdtabledirect=&h0200 ' is a table name that can get row content directly from a table

Iii. the difference between Rs.Open and Conn.execute

Rs.Open Sql,conn
If SQL is Delete,update,insert, it returns a closed recordset, not a rs.close in the process of the file after the last write Rs.close can be more than one recordset Rs1.open Sql1,conn, the last to close the recordset: Rs.close Rs1.close
Conn.execute (SQL)
If SQL is Delete,update,insert, it returns a closed recordset, not a rs.close in the process of the file after the last write Rs.close can be more than one recordset Rs1.open Sql1,conn, the last to close the recordset: Rs.close Rs1.close.
If SQL is update,insert,delete, you should either use Conn.execute (SQL) or do not have parentheses Conn.execute sql
If SQL is a SELECT statement, you should use the set Rs=conn.execute (SQL) bracket to make sure that you cannot omit
This is because of the characteristics of VB, the call with the return value must be parentheses, without the return value of the call can not be parentheses.

1, Conn.execute

Sql= "SELECT * from admin where username= ' Xiaozhu '"
set Rs=conn.execute (SQL)
' automatically close the recordset after execution
' finally only need to close the connection.
Conn.close
Set conn=nothing

2, Rs.Open

Set rs=server.createobject ("Adodb.recordset")
sql= "select * from admin where username= ' Xiaozhu '"
Rs.Open sql, conn,1,1
' can set some parameters, that is, the way the lock and the cursor move
' finally to close the recordset and connect
rs.close
set rs=nothing
conn.close
Set conn=nothing

3, Command.Execute

Sql= SELECT * from admin where username= ' Xiaozhu '
Set rs=command.execute (SQL)
1.
Rs.recordcount=-1
Rs.Open sql,conn ("SQL is a SELECT statement") obtained by the Set rs=conn.execute ("If it is a SELECT statement") The resulting rs.recordcount is the normal number of records
2.
Rs.Open is to open the database conn.execute Execute SQL instruction
Set Rs=conn.execute (Insert,update,delete) return a closed recordset
set Rs=conn.execute (select) returns a Recordset
3 that is not closed.
Conn.execute (sql,rowsaffected,c)
parameter meaning:
The value of SQL can be an SQL statement, table name, stored procedure name, or any string that can be accepted by the data provider. To improve performance, it is a good idea to specify the appropriate value for the C parameter. The
Optional parameter rowsaffected returns the number of effects after the insert, UPDATE, or delete query execution. These queries return a closed Recordset object.
A SELECT query returns a rowsaffected value of 1 and returns an open Recordset with one or more rows of content.
4.
Conn.execute SQL is suitable for use when you do not need to return a Recordset object, such as the following code:
sql= "Delete from news where id=" &cstr (ID)
Conn.execute SQL
If you need to return a Recordset object, use the following code:
sql= "Select from news where id=" &cstr (ID)
Set rs= Conn.execute (SQL)
If you remove the parentheses here (that is, set rs = Conn.execute sql), you will be prompted with a "statement not ended" error prompt, I looked through the syntax format, found that there are two formats, The following is a detailed explanation:

Format One: Connection object name. Execute (SQL Directive)
Format Two: Connection object name. Execute (data table name)
Conn.execute (SQL) (0) is the value of the first field in the DataSet
Conn.execute (SQL) returns the recordset.
Conn.execute SQL does not return a recordset. Generally used for inserts, updates, deletes, and so on without the return value of the operation