Connection object
Attributes
Attribute Name Data Type and Use Attributes Read Write LONG Type, with two constants and specified whether to use retainning transactions. Constant ADXACTCommitRetaining indicates a new transaction when calling a committrans method; constant adxactabortretaning means that a new transaction is initiated when calling the RollbackTrans method. The default is 0, indicating that it does not use the retained transaction. CommandTimeout reads the long type to specify the time you must wait until the Execute call for a relevant Command object. The default is 30 seconds. Connectionstring readable String type, providing the data provider or service provider to open the specific information of the connection to the data source ConnectionTIMEOUT to read the long type, specifying the time required to wait for a failure CONNECTION.Open method call, default The value is 15 seconds. CursorLocation Reads LONG Type, determined to use the client (ADUSECLIENT) cursor engine, or use the server-side cursor engine. The default is aduseserver. DEFAULTDATABASE reads to write string types, if you do not specify a database name in ConnectString, use the name specified here. For SQL Server, its value is usually a Pubs ISOLATIONLEVEL readable long type, specifying and other concurrent transaction interactions. Or affairs. See the ISOLATIONLEVEL constant Mode long type, specify read and write permissions to Connection. See the MODE constant provider readable String type, if the NELE DB data or service provider's name is not specified in Connectionstring, use the name specified here. The default is MSDasql (Microsoft Ole DB Provider for ODBC). State Readable LONG Type, specifying the connection is in an open state, or is in a closed state or an intermediate state. See the State constant Version read-only String type, return to the ADO version number.
Note: Most of the readable and writable properties listed above, only when the connection is in turn off, it is written.
Only when the user uses the BeGintrans ... CommitTrans ... RollbackTrans method defines the specified for the degree of transaction isolation. If there are multiple database users to execute transactions at the same time, the application must specify how to respond to other transactions in the run.
method
Method Use Begintrans initializes a transaction; there must be a committetrans and / or rollbacktrans Eating Close Close Connection CommitTrans Submit a transaction to complete a permanent change for the data source (Begintrans method must be called before use) EXECUTE people SELECT SQL statement Return A Forward-Only Recordset object, is also used to perform the structural information that does not return a recordset statement, such as INSERT, UPDATE, DELETE query, or DDL statement Open to open a connection OpenSchema to return a RecordSet object to provide data sources (Metadata RollbackTrans cancels a transaction, restores temporary changes to data sources (Begintrans methods must be called before use)
Note: Three ways that execute, Open, and OpenSchema can accept variable parameters. Execute's syntax is: cnnname.execute strcommand, [lnGrowsaffected [, LNGOPTION] STRCOMMAND value can be a SQL statement, a table name, and a stored procedure name, or any string that the data provider can accept. In order to improve performance, it is best to specify a suitable value for the LNGOptions parameter (see the constant for the LNGOptions parameter) to enable the provider to explain the statement without determining its type. Optional parameters LNGROWSAFFECTED will return the number of INSERT, UPDATE, or DELETE queries. These queries returns an off Recordset object. A SELECT query will return the LNGROWSAFFECTED value of 0 and return Forward-Only RecordSet with a row or multi-line content. event
The event name triggered the timing BegintransComplete Begintrans method.
Private Sub cnnName_BeginTransComplet (ByVal TransactionLevel As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) after CommitTransComplete CommitTrans method performed Private Sub Connection1_CommitTransComplete (ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum after, ByVal pConnection As ADODB.Connection) ConnectComplete Connection is successfully established to the data source Private Sub Connection1_ConnectComplete (ByVal pError As ADODB.Error, after adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) Disconnect Connection Close Private Sub Connection1_Disconnect (adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) ExecuteComplete completed Connection.Execute or Command.Execute of Private Sub Connection1_ExecuteComplete (ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB. Command, Byval PrecordSet as adoDb.recordset, Byval PConnection As adodb.connection) InfubusSage A Error object is added to Adodb.co When nnectio.Error collection of Private Sub Connection1_InfoMessage (ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) method is performed after RollbackTransComplete RollbackTrans Private Sub Connection1_RollbackTransComplete (ByVal pError As ADODB.Error, adStatus As ADODB. when EventStatusEnum, ByVal pConnection As ADODB.Connection) WillConnect about to call Connection.Open method of Private Sub Connection1_WillConnect (ConnectionString As String, UserID As String, Password As String, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection Willexecute is about to call Connection.execute or Command.execute method Private sub connection1_willexecute (Source AS String, Cursortype, LockTypeEnum, LockType As Adodb.
LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection) Note: The use therein adStatus constant parameter names and meanings see adStatus Constant
constant
IsolationLevel constant
Constant Meaning ADXACTCURSORSTABILITY only allows to read other transactions (default) AdxactBrowse allows you to read other transactions Uncommitted Change ADXACTCHAOS This transaction does not overwrite the change of other transactions made by higher isolation ADXACTISOLATED All transactions independent ADXACTREADCOMMITTED ADXACTCURSORSTABILITY ADXACTREADUNCOMMITTED is equivalent to change to read other transactions by ADXACTBROWSE ADXACTREPEATABLEREAD, which is prohibited by reading other transactions.
Mode constant
Constant Meaning AdmodeunkNown None Connection Permissions (Default) AdmodeRead Connection is Read-only AdmodeReadwrite Connection is Readable-writable Admodesharedenynynone does not reject other users' read and write access (the default value of Jet Ole DB Provider) AdmodesharedenyRead refuses other users Open to the data source ADMODESHAREDENYWRITE Reject other users to open to the data source ADMODESHAREEXCLUSIVE to open the data source in exclusive way Admodewrite connection is only written
State constant
Constant Meaning adStateClosed Connection (or other objects) is a closed state (default) adStateConnecting is connected to the data source adStateExecuting Execute method Connection or Command object has been invoked adStateFetching returns rows (Row) to Recordset object adStateOpen Connection (or other objects) Is open (activity)
Constant for LNGOption parameters in the execute method
Command Type constant Meaning AdcmDunkNown Command Type Unexpected (Default) Name AdcmdtableDirect Command is a table name adcmdtext command that can get row content directly from the table, is a SQL statement
ADODB event processes the constant used by subsection parameters Adstatus
Constant Meaning AdstusCancel Operation is canceled by the user that the AdstusCnatDeny operation cannot reject other users' access AdstatuserRorsoccurred operations caused by the data source, which is an unpredictable event in the Errors collection. ADSTATUNWANTEVENT operation is activated.
[NextPage]
(two)
Command object
The main purpose of the Command object is to perform a parameterized stored procedure. The form is either temporary preparation (prepared) or a precompiled SQL statement. If you want (store) one or more queries for multiple executions on the same connection, the Command object is also useful. When you want to create RecordSet, an efficient way is to bypass the commord set.open method. Attributes
Property Name Data Type and Used ActiveConnection Pointer Type, pointing to the Connection object associated with Command. For existing open connections, set cmmname.activeConnection = cnnname can be used. Alternatively, a new connection can be created using a valid connection string without using the associated Connection object name. The default is NULL. CommandText reads the String type. Specify an SQL statement, a table name, a stored procedure name, or an arbitrary string that the provider can accept for ActiveConnection. The value of the CommandType property determines the format of the CommandText property value. The default value is empty string: "" CommandTimeout reads the long type, specifying the time you must wait until a Command.execute call is aborted. The value at this time is preferred in the set value in Connection.comAndTimeout. The default is 30 seconds. CommandType reads the long type, specifying how the data provider explains the CommandText property value. CommandType is equivalent to an optional parameter LNGOption in the Connection.execute method. See the constant used in CommandType for details. The default value is adcmdunkown. Name Read Write String Type, specify the name of the Command. Prepared can read the Boolean type, determine whether the data source compiles the SQL statement in CommandText to Prepared Statement. Prepared Statement is only available in the ActiveConnection life cycle of Command. Many client / server RDBMS, including SQL Server, supports Prepared Statement. If the data source does not support Prepared Statement, set this property to true, which will result in a fallfrower error. State Readable LONG Type, specify the commnad status. See the State constant.
Note: It is best to specify a suitable constant value for CommandType each time, otherwise the efficiency of the system operation will be reduced.
method
Method Use CreateParameter must first declare an adodb.parameter object before performing the method. Calling syntax: cmmname.createParameter [strname [, lngtype "]]]]]]]]]]]]]]]]]]]]]]] EXECUTE call syntax is substantially the same as Connection.execute.
constant
State constant
Constant Meaning adStateClosed Connection (or other objects) is a closed state (default) adStateConnecting is connected to the data source adStateExecuting Execute method Connection or Command object has been invoked adStateFetching returns rows (Row) to Recordset object adStateOpen Connection (or other objects) Is open (activity)
Command used by CommandType
Command Type constant Meaning AdcmDunkNown Command Type Unexpected (Default) Name AdcmdtableDirect Command is a table name that can get row content directly from the table ADCMDText Command is a SQL statement [NextPage]
(three)
Recordset object
Attributes
Property Name Data Type and Purpose Absolute ABSOLUTEPAGE Readable LONG Type, or return the page number where the current record is located, or a positionenum constant, see the constant used by AbsolutePage. You must set the value of Pagesize before getting or setting the value of the ABSOLUTEPAGE. AbsolutePage is counting from 1. If the current record is at the first page, the return value of the AbsolutePage is 1, and the Absolute Point will give the current record pointer to the first record of the specified page. AbsolutePosition * Readable loong type (counting from 1), setting or returns location at the current record. The maximum value of AbsolutePosition is the value of the RecordCount property. ActiveCommand readable string type, the previously opened Command object name ActiveConnection pointer type, pointing to the previously opened Connection object associated with RecordSet, or pointing to a fully valid Connectionstring string value.
BOF * read-only Boolean type, if true, indicates that the record pointer is already before the Recordset first record, and there is no current record bookmark * to read the VARIANT type, return to a reference to a specific record or use a Bookmark value to make the record pointer Point to a specific record cachesize * readable write long type, specify the number of records stored in the local cache, the smallest (default) is 1. If the value of the Cachesize is added, the number of communication with the server can be reduced when flowing RecordSet for more records. CursorLocation reads the long type to specify the position of the flowable cursor, that is, the Cursortype is located at the client or the server side, see the constant used by CursorLocation. The default is the cursor provided using the OLE DB data source. Cusrsortype * Readable LONG type, specify the type of Recordset cursor, see the constant used by Cursortype, the default value is the forward-only cursor DataMber pointer type, pointing to the associated DataEnvironment.command object DataSource pointer type, pointing to the associated DataEnvironment.connection object EditMode * read-only the long type, return to the recordset's editing status, see the constant EOF * read-only Boolean type for EditMode, if true, indicating that the record pointer has exceeded the last record of the Recordset, and there is no current record. Filter * readable to write a Variant type, or one-piece expression (a valid SQL WHERE clarion but does not have a reserved word where), or point to the Bookmark array of specific records, or a Filter constant, see Filter A constant. LockType * Readable LONG type, specifying the recording lock method used to open RecordSet. The default is read-only, and corresponds to the read-only feature of the Forward-Only cursor. See the constant used by the LockType property. MarshalOptions can read the long type, specify which record collection should be returned after the client is changed, this property is only suitable for uncommon Ador.Recordset objects, which is one of the RDS.Ador.Recordset object members. MaxRecords * readable LONG type, specifying the maximum number of records returned by the SELECT query or stored procedure, the default value is 0, that is, all returned pageCount read-only LONG type, return all the number of pages of Recordset, you must set the value of PageSize, The return value of PageCount is really meaningful. If the Recordset does not support the PageCount property, the return value is -1 PageSize readable loong type, setting, or returns a logical page number of records included in the logical page. Use logic pages to decompose large RecordSet into multiple easily handled small parts. The usual approach is to set up the number of records that PageSize can display by DataGrid, MSFlexGrid, or hierarchical FlexGrid control. PageSize and lock Jet (2K) or lock SQL Server (6.5 editions and earlier versions, 2K; version 7.0, 8K) Database Table Signings When the table size is not related to the PERSISTFORMAT readable long, set, or returned by calling Save method The format of the Recordset file created.
There is only one value adpersistadtg (default format: Advanced Data TableGram) RecordCount * Read-only long type, if Recordset supports approximation or support bookmarks, return the number of records contained with the RECORDSET with the flowable cursor; if not supported, you must use The MOVELAST method is to achieve the accurate RECOrdcount value of all records. If the forward-only type recordset has one or more records, RecordSet returns -1 (TRUE), any type of empty Recordset will return 0 (false) sort * to read the String type, including a reserved word Order by SQL Ordery By clause to specify the sorting method of RecordSet Source * to read the String type, which can be a SQL statement, a table name, a stored procedure name, or a related Command object name. If a Command object name is provided, the source will return the value of Command.comMandText. The parameters of the Open method can specify the type of STATE-readable long type providing to the Source value, one of the object state constants. See the State constant STATUS read-only LONG type, indicating the status of the RecordSet or other multi-recorded (BULK) operation. See the constant used by the status property Note: The list of properties listed above is the standard properties of the AdoDB.Recordset object, that is, those that are supported by most Universal OLE DB data providers of the relational database. The property of the strap indicates that it is exactly the same or close to the corresponding attribute of the DAO.Recordset or RDORESultset object.
method
Method Use AddNew * Add a new record to the updatable Recordset. Call the syntax for rstname.addnew [{varfield | avarfields}], {varValue | avarvaluese}], where varField is a single field name, and avarfields are field names. Varvalue is a single field value, and AvarValue is an array consisting of a value specified by avarfields. Call the UPDATE method to add the new record to the table of the database. If you add a record to the RecordSet of the first field, you must specify the name of the primary key in the AddNew method, Cancel cancel the execution of the asynchronous query, the abort store, or the composite SQL statement to create multiple RecordSet, call batch update operation will come into force syntax for rstName.Cancel CancelBatch * cancel Recordset LockEdit BatchOptimistic value of the call syntax is: rstName.CancelBatch [lngAffectRecords], optional parameter values lngAffectRecords see lngAffectRecords used with a constant Clone copy With a RECORDSET object with a stand-alone record pointer, call syntax is: set RSTDUPE = Rstname.clone () Close Close RecordSet object, will retrore the Recordset property and use the Open method to re-access the Recordset. Calling syntax: Rstname.close delete * If the Recordset's LockedIt property value is not set to AdlockBatchOptimistic, immediately remove the current record Find from the Recordset and the corresponding table to meet the records that meet the specified condition. Calling syntax: rstname.finds, lngsearchdirection [, lngstart]], where strcriteria is a SQL WHERE clause that does not contain WHERE keywords, optional parameters LNGSKIPRecords is the number of records skipmed before application, LNGDirection designated Find the direction (AdSearchForward, and AdSearchForward, where AdSearchForward is the default value), the optional parameter lngstart specifies where to start search, its value is either a Bookmark value, or the Bookmark constant, see the Bookmark constant for the Varstart parameter. Variant GetRows returns a two-dimensional array (row, column), call syntax avarname = rstName.GetRows (lngRows [, varStart [, {strFieldName | lngFieldIndex | avarFieldNames | avarFieldIndexes}]], which is the number of records returned lngRows rows, varStart designated Where start looking for, it is either a Bookmark value, or the Bookmark constant, see the Bookmark constant for the Varstart parameter. The third parameter can be a single column (field) name or index, or multiple column names Or the Variant array of indexes. If the third parameter is not specified, getRows returns all columns in the Recordset. GetString By default, returns the String string value of the specified number record, and the record is separated by the return code. Separate by Tab. The syntax is: strclip = rstname.getstring (lngrows, [, strcloumndelimiter [, strrowdelimiter, [strnullexpr]]).
Where lnGROWS is the number of records, strcolumndelimiter is an optional column separator (VBTAB is the default), StrowDelimiter is an optional line division (VBCR is the default), and strnullexpr is an optional parameter, used to touch NULL values Alternative (default is empty string). The main purpose of GetString is to process the MSFLEXGRID or MSHFLEXGRID control MOVE * from the current record moving record pointer by setting the CLIP attribute of the control to StrClip. Calling syntax: Rstname.move LNGNUMRECORDS [, VARSTART], where LNGNumRecords is the number of records to be skipped, and optional parameter varStart specifies where to start moving. Its value is either a Bookmark value, or the Bookmark constant, see the Bookmark constant for the Varstart parameter. MoveFirst * Move the record pointer to the first record, call the syntax as: RstName.MoveFirst MoveLast * Move record pointer to the last record, call the syntax to move the record pointer to the next record, call the syntax: Rstname. MoveNext. It is the unique MOVEPREVIOS * mobile record pointer to the previous record, call the syntax to return the syntax to: RstName.MovePrevious next, which is usually a composite SQL statement that can generate multiple Recordset (eg Select * from Orders; select * from customers) or stored procedure to create. Call the syntax for next = rstname.nextRecordset [(LNGRecordsaffected), where optional parameter lngRecordsaffected specifies the number of records that return to RSTNext. If there is no RecordSet, RstNext is set to Nothing Open to open a Recordset on a active Command or Connection object, call syntax: RstName.Open [VARSOURCE [, VARACTIVECONNECTION [, LNGCURSORTYPE [, LNGLOCKTYPE [, LNGOPTIONS]]]]] . These parameters are optional, Requuery Re-acquires the contents of Recordset, equivalent to Close, and then OPEN. It is a resource set operation. The syntax is: RstName.Requery Resync * Re-slave the table to get part of the Recordset content. Call the syntax for RstName.Resync [LNGAFFECTRecords], where the value of LNGAffECTRecords see the constant used by LNGAffECTRecords. If this parameter is set to AdaffectCurrent or AdaffectGroup, it is less than the resources consumed than the Adaffectall (default). Save creates files that contain the RECORDSET permanent copy. Call the syntax for Rstname.save strfilename. Strfilename is the path and file name. Usually use .RST as an extension of the file. Supports Returns True if the data provider supports the specified cursor, otherwise returns to false. Call the syntax for supported = RstName.supports (LNGCURSorOptions). About LNGCURSorOptions, see the constant used by the Supports method. Update * enables the modification of the RecordSet to take effect on the table in the underlying data source.
For bulk operations, the Update method only enables the modification to take effect on the local (Cached) Recordset. Calling syntax for rstname.Update updatebatch * Complex indication type RecordSet (LockType property value is adbatchoptimistic, CURSORTYPE Attribute Value AdopenKeySet or AdopenStatic) to change the table in the underlying data source. Call the syntax for RstName.Updatebatch [LNGAFFECTRECORDS], where the value of LNGAffECTRecords see the constant for LNGAffECTRecords. Note: AdoDB.Recordset object does not support Edit method. In order to change the value of one or more fields currently recorded, you can use RstName.fields (n) .Value = varValue to change the value of the corresponding field to the value required, and then execute RstName.Update.
event
Event Name Trigger Time EndOfRecordset Record Pointer Try to move to the last record, the FieldChangeComplete field value is completed after the MoveComplete Move or Move ... method is executed after the RecordSchangeComplete is completed in the RecordSetChangeComplete cache after a single record editing is completed. WillChangfield The change in the WillChangeRecordSet cache before the WillChangeRecord in the WillChangeRecordset cache before the field value changes to the underlying table before the underlying table takes effect.
Note: The function header of the event processing module is almost all of the Adreason parameters. The value of this parameter see the constant used by the Adreason parameter.
constant
Absolute analog
Constant Meaning The ADPOSUNKNOWN data provider does not support the page, the Recordset is empty, or the data provider cannot determine the page number. ADPOSBOF record pointer is positioned in the file (the BOF property value is true) adposeof record pointer positioned in the end of the file (the EOF property is true)
CursorLocation properties used constants
Constant Meaning AduseClient Use the cursor provided by the client tournament. AdoDb.Recordset requires client games ADUSERVER to use the cursor provided by the data source, usually (but not absolute) is located on the server (default)
Cursortype properties used constants
Constant Meaning AdopenForwardonly provides a one-way mobile cursor and read-only RecordSet (default) AdoPendynAmic provides scrollable cursors, which can display other users to change the records (including add new records) AdoPenKeySet provides scrolling cursors, only hidden other users Changes, similar to DYNASET type DAO.Recordset AdoPenStatic provides a scrollable cursor on Recordset static copy, DAO.Recordset, similar to the snapshot type, but updatable features
Constant used by EditMode properties
Constant Meaning AdeDitnone Uncommon Edit Operation (Default) AdeditAdd temporarily adds a record, but the data in the current record currently recorded in the table has not been stored in the database, but has not deposited in the table of the database.
Filter properties used constants
Constant Meaning Adfilternone Removes existing filters, display all records in RecordSet (equivalent to setting the filter attribute to an empty string, default) ADFILTERAFFECTedRecords only shows the records that the last Cancelbatch, Delete, Resync or Updatebatch method AdfilterFetchedRecords only current Cache records, the number of records is cachesize to determine that the ADFILTERPENDINGRecords only displays records that have been changed but have not been processed by the data source (only for bulk update mode) LockType properties.
Constant Meaning AdlockRecordonly Specifies read-only access (default) AdlockBatchOptimistic uses bulk update modes instead of the default update mode AdlockOptimistic uses optimism locks (Lock records only during the update process) AdlockPESSIMISTIC uses pessimism (editing or updating the whole process Unlock records or pages)
State constant
Constant Meaning adStateClosed Connection (or other objects) is a closed state (default) adStateConnecting is connected to the data source adStateExecuting Execute method Connection or Command object has been invoked adStateFetching returns rows (Row) to Recordset object adStateOpen Connection (or other objects) Is open (activity)
Status properties use constants (only for Batch or Bulk Recordset operation)
Constant Meaning adRecOK successfully updated adRecNew successfully added adRecModified successfully modified adRecDeleted successfully removed adRecUnmodified No change adRecInvalid unsaved: Bookmark property is not adRecMultipleChanges unsaved: Save will affect other records adRecPendingChanges unsaved: Record quoted a wait insertion adRecCanceled unsaved: operation is Cancel Adreccantrelease is not saved: Existing recorded values Block Save AdrecConcurrencyviolation Not Save: Optimacy and Shock ADRECINTEGRITYVIOLATION Operation: Operation will affect the adherent AdystemaxchangesexceedEDEDEDED: There are too many waiting modifications AdRecobjectopen unpaid: Open storage object Conflict AdrecOutofMemory is not saved: User permissions are not saved: User permissions are not saved: The recorded structure does not match the definition of the definition in the database is not saved or deleted: Record has been removed
LNGAFFECTRecords parameter used constant
Command Type constant Meaning AdaffectAll All records of RecordSet objects, those recorded by Filter property filtering, also calculate (default) AdaffectCurrent only includes current records AdaffectGroup only includes records that meet the current Filter condition.
Bookmark constant for Varstart parameters
Constant Meaning AdbookMarkCurrent starts from the current record (default) AdbookMarkFirst From the first record start AdbookMarkLast starting from the last record
Constant used by Supports methods
Constant Meaning adAddNew AddNew method calls and obtain Absoluteposition adApproxPosition provided AbsolutePage and attribute values obtained adBookmark Bookmark setting and calling the Delete method attribute values adDelete adHoldRecords acquire additional recording or changing the position of the pointer acquired record, but does not commit the changes undetermined adMovePrevious the GetRows call, Move, MoveFirst and MovePrevious methods (indicated a two-way scrollable cursor) AdResync calls RESYNC method AduPDate call Update method AduPdateBatch calls UpdateBatch and CancelBatch method Adreason parameters
Constant Meaning AdRsnAddNew call AddNew method AdRsnClose call the Close method AdRsnDelete call the Delete method AdRsnFirstChange for the first time on record field value was revised AdRsnMove call the Move method AdRsnMoveFirst calls MoveFirst method AdRsnMoveLast calls the MoveLast method AdRsnMovePrevious called MovePrevious method AdRsnRequery call Requery method AdrsnResync calls the RESYNC method AdrsNundoadDNew AddNew operation is canceled by the user to cancel the AdrsNundOrdate Update operation by the user to cancel the use of AdrsNupdate. Update method