1. Rs.Open SQL,Conn,A,B,C
2. Conn.Execute(SQL, RowsAffected, C)
1. Conn.Execute(SQL, RowsAffected, C)
Parameter meaning:
The value of SQL can be a SQL statement, a table name, a stored procedure name, or any string that the data provider can accept. To improve performance, it is best to specify appropriate values for C parameters
Optional parameter RowsAffected
1. After an Insert, Update or Delete query is executed, RowsAffected returns the number of affected, and these queries will return a closed Recordset object.
2. A SELECT query will return a RowsAffected value of -1 and an open recordset with one or more rows of contents.
<%Set Conn=OpenDatabase' (For details of the OpenDatabase function, see: Method for establishing connection with ACCESS and MSSQL databases in ASP) Conn.Execute update Table1 set Col1='123',RowsAffected,&H0001Response.Write RowsAffected& RowsAffected Conn.closeSet Conn= Nothing%>
2. Rs.Open SQL,Conn,A,B,C
Set Rs=Server.CreateObject (adodb.RecordSet)sql=xxxxRs.Open Sql,Conn,1,1,&H0001irs=rs.getrows(10)
What does &H0001 mean? When I first saw this, I was really confused for a while. Now the website information about this is flying all over the world. Let me mention it here, there are specific instructions in the following parameter description.
A parameter: cursor type
Const adOpenForwardOnly=0 Read-only, default. Cursors are only allowed to move forward between records within the record set. Pagination, Recordset, BookMark is not supported
Const adOpenKeyset=1 Read-only, key set cursor, the current data record can be moved freely, and the modifications made by other users to the record will be reflected in the record set, but other users add or delete records will not be reflected in the record set. Supports pagination, Recordset, BookMark
Const adOpenDynamic=2 can be read and written, the current data record can be moved freely, the dynamic cursor function is the strongest, but it also consumes the most resources. The modifications, additions or deletions of records by users to records will be reflected in the record set. Support full-featured browsing (ACCESS does not support).
Const adOpenStatic=3 Static cursor is just a snapshot of the data. The modifications made by the user to the record, addition or deletion of records will not be reflected in the record set. Supports forward or backward movement
B parameter: lock type
Const adLockReadOnly=1 Default value, the record set is read-only and cannot be modified.
Const adLockPessimistic=2 'Psychological lock, lock the record immediately when editing to ensure successful editing of the record. The safest way
Const dLockOptimistic=3 'Optimistic lock, the record is not locked until the update record is submitted using the Update method. Other previous operations can still change, insert and delete the current record, etc.
Const dLockBatchOptimistic=4 'Batch optimistic locking allows multiple records to be modified, and records are locked only after calling the UpdateBatch method.
When no changes to any records are required, a read-only record set should be used so that the provider does not need to do any detection. For general use, optimistic locking may be the best option, as the record is locked for only a short period of time, and the data is updated during this period. This reduces the use of resources.
C parameters: (Specify SQL statement type)
Const ADCmdUnknown=&H0008 'Unknown, the system needs to judge, the speed is slow, the default value
Const ADCmdText=&H0001 'A segment of sql will be executed
Const ADCmdTable=&H0002 'Will operate a table
Const ADCmdStoredProc=&H0004 'The stored procedure name will be operated
Const ADCmdFile= &H0100 'The corresponding file name of the object type
Const ADCmdTableDirect=&H0200 'is the table name that can directly obtain row content from the table
3. The difference between Rs.Open and Conn.Execute
Rs.Open SQL,Conn
If sql is delete, update, insert, it will return a closed record set. During use, do not use rs.close to write rs.close at the end of the file and then add multiple record sets rs1.open sql1, conn, and finally Close the record set in one piece: rs.close rs1.close
Conn.Execute(SQL)
If sql is delete, update, insert, it will return a closed record set. During use, do not use rs.close to write rs.close at the end of the file and then add multiple record sets rs1.open sql1, conn, and finally Close the record set together: rs.close rs1.close.
If sql is updated, insert, delete, then Conn.Execute(sql) should be used or without brackets Conn.Execute sql
If sql is a select statement, you should use Set Rs=Conn.Execute(sql) brackets, and must not be omitted.
This is because of the nature of vb. calls with return values must be parenthesed, and calls without return values can be parenthesed.
1. Conn.Execute
sql=select * from admin where username='xiaozhu'Set Rs=Conn.Execute(sql)'Automatically close the record set after execution' Finally, just close the connection is enough conn.closeSet conn=nothing
2. Rs.Open
Set Rs=Server.CreateObject(Adodb.Recordset)sql=select * from admin where username='xiaozhu'Rs.Open sql,Conn,1,1' You can set some parameters yourself, namely the locking and cursor movement method' Finally To close the record set and connect Rs.CloseSet Rs=nothingConn.CloseSet Conn=Nothing
3. command.execute
sql=select * from admin where username='xiaozhu'
Set Rs=Command.Execute(sql)
1.
Set Rs=Conn.Execute (if it is a select statement) The obtained rs.recordcount=-1
Rs.Open sql,conn(sql is the select statement) The rs.recordcount obtained is the normal number of records
2.
Rs.Open is to open the database Conn.Execute is to execute SQL instructions
Set Rs=Conn.Execute(insert, update, delete) returns a closed record set
Set Rs=Conn.Execute(select) returns a record set that is not closed
3.
Conn.Execute(SQL, RowsAffected, C)
Parameter meaning:
The value of SQL can be a SQL statement, a table name, a stored procedure name, or any string that the data provider can accept. To improve performance, it is best to specify an appropriate value for the C parameter.
The optional parameter RowsAffected will return the number of impacts after the execution of the INSERT, UPDATE, or DELETE query. These queries return a closed Recordset object.
A SELECT query will return a RowsAffected value of -1 and an open Recordset with one or more rows of contents.
4.
Conn.Execute sql is suitable for use when no recordset object is returned, such as the following code:
sql=delete from news where id=&cstr(id)
Conn.Execute sql
If you need to return the recordset object, use the following code:
sql=select from news where id=&cstr(id)
Set Rs=Conn.Execute(sql)
If you remove the brackets in execute(sql) (that is, set rs = Conn.Execute sql), it will prompt that the statement has not ended. I looked through the syntax format and found that there are two formats, which are explained in detail as follows:
Format 1: Connection object name.Execute(SQL instruction)
Format 2: Connection object name.Execute (data table name)
Conn.Execute(sql)(0) is the value of the first field of the dataset
Conn.Execute(sql) Returns the record set.
Conn.Execute sql does not return recordsets. Generally used for inserting, updating, deleting and other operations without return values.