ADO RecordSet Populate: Difference between revisions

From AutoIt Wiki
Jump to navigation Jump to search
mNo edit summary
(→‎Recordset collections: added Properties Collection and some remarks and links to MSDN)
 
(3 intermediate revisions by 2 users not shown)
Line 2: Line 2:
The ADO Recordset object is used to contain the set of data extracted from a database and is composed of records (rows) and fields (columns).<br />
The ADO Recordset object is used to contain the set of data extracted from a database and is composed of records (rows) and fields (columns).<br />


The Recordset object is the heart of ADO. Via this object, we can select desired data and change the data. Equally important is the ability to move around inside the database. However, the functionality of the provider may impose limitations.<br />
The Recordset object is the heart of ADO. Via this object, we can select desired data. Equally important is the ability to move around inside the database. However, the functionality of the provider may impose limitations.<br />


  RecordSet.Open(Source, Connection, CursorType, LockType, Options)
  RecordSet.Open(Source, Connection, CursorType, LockType, Options)
Line 24: Line 24:
! Constant !! Value !! Description
! Constant !! Value !! Description
|-
|-
| adOpenDynamic || 2 || A dynamic cursor with both forward and backward scrolling where additions, deletions, insertions, and updates made by other users are visible
| adOpenUnspecified || -1|| Cursor type not specified
|-
|-
| adOpenForwardOnly || 0 || (Default) A forward scrolling only, static cursor where changes made by other users are not visible
| adOpenForwardOnly || 0 || (Default) A forward scrolling only, static cursor where changes made by other users are not visible
|-
|-
| adOpenKeyset || 1 || A keyset cursor allows you to see dynamic changes to a specific group of records but you cannot see new records added by other users
| adOpenKeyset || 1 || A keyset cursor allows you to see dynamic changes to a specific group of records but you cannot see new records added by other users
|-
| adOpenDynamic || 2 || A dynamic cursor with both forward and backward scrolling where additions, deletions, insertions, and updates made by other users are visible
|-
|-
| adOpenStatic || 3 || A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records
| adOpenStatic || 3 || A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records
|-
| adOpenUnspecified || -1|| Cursor type not specified
|}
|}


Line 60: Line 60:
! Constant !! Value !! Description
! Constant !! Value !! Description
|-
|-
| adCmdFile || 256 || Evaluate as a previously persisted file
| adCmdUnspecified || -1 || (Default) does not specify how to evaluate
|-
|-
| adCmdStoredProc || 4 || Evaluate as a stored procedure
| adCmdText || 1 || Evaluates as a textual definition of a command or stored procedure call
|-
|-
| adCmdTable || 2 || Have the provider generate an SQL query and return all rows from the specified table
| adCmdTable || 2 || Have the provider generate an SQL query and return all rows from the specified table
|-
|-
| adCmdTableDirect || 512 || Return all rows from the specified table
| adCmdStoredProc || 4 || Evaluate as a stored procedure
|-
|-
| adCmdText || 1 || Evaluates as a textual definition of a command or stored procedure call
| adCmdUnknown || 8 || The type of the CommandText parameter is unknown. ADO will make several attempts to interpret the CommandText: adCmdText then adCmdStoredProc then adCmdTable
|-
|-
| adCmdUnknown || 8 || The type of the CommandText parameter is unknown. ADO will make several attempts to interpret the CommandText: adCmdText then adCmdStoredProc then adCmdTable
| adCmdFile || 256 || Evaluate as a previously persisted file
|-
|-
| adCmdUnspecified || -1 || (Default) does not specify how to evaluate
| adCmdTableDirect || 512 || Return all rows from the specified table
|}
|}


Line 93: Line 93:
List of useful RecordSet collections. A full list of properties, collections and methods can be found [http://msdn.microsoft.com/en-us/library/windows/desktop/ms675841%28v=vs.85%29.aspx here]
List of useful RecordSet collections. A full list of properties, collections and methods can be found [http://msdn.microsoft.com/en-us/library/windows/desktop/ms675841%28v=vs.85%29.aspx here]
; Fields
; Fields
: A collection containing all of the Field objects associated with a Recordset object
: A collection containing all of the Field objects associated with a Recordset object. For more info read [https://msdn.microsoft.com/en-us/library/windows/desktop/ms676603(v=vs.85).aspx MSDN article about Fields Collection]
; Properties
: Contains all the Property objects for a specific instance of an object. For more info read [https://msdn.microsoft.com/en-us/library/windows/desktop/ms675257(v=vs.85).aspx MSDN article about Properties Collection]


== Recordset methods ==
== Recordset methods ==

Latest revision as of 22:27, 28 February 2016

Populate the recordset

The ADO Recordset object is used to contain the set of data extracted from a database and is composed of records (rows) and fields (columns).

The Recordset object is the heart of ADO. Via this object, we can select desired data. Equally important is the ability to move around inside the database. However, the functionality of the provider may impose limitations.

RecordSet.Open(Source, Connection, CursorType, LockType, Options)

The Open method is called on a Recordset object to open a cursor which gives you access to the records contained in the base table, the results from a query (used in the examples of this tutorial), or a previously saved Recordset.
There are five optional parameters.

Source
This optional parameter is a variant that can be any one of the following data sources:
  • SQL query string (used in the examples of this tutorial)
  • table name
  • Others: More details can be found in section → External Links - ADO
Connection
This optional parameter is a valid Connection object (used in the examples of this tutorial) or a connection string that defines the connection.
CursorType
This optional parameter specifies the type of cursor to use when you open a Recordset object.
Possible values for the CursorTypeEnum enumeration are:
Constant Value Description
adOpenUnspecified -1 Cursor type not specified
adOpenForwardOnly 0 (Default) A forward scrolling only, static cursor where changes made by other users are not visible
adOpenKeyset 1 A keyset cursor allows you to see dynamic changes to a specific group of records but you cannot see new records added by other users
adOpenDynamic 2 A dynamic cursor with both forward and backward scrolling where additions, deletions, insertions, and updates made by other users are visible
adOpenStatic 3 A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records
LockType
This optional parameter indicates the type of lock in effect on a Recordset.
Possible values for the LockTypeEnum enumeration are:
Constant Value Description
adLockBatchOptimistic 4 Multiple users can modify the data and the changes are cached until BatchUpdate is called
adLockOptimistic 3 Multiple users can modify the data which is not locked until Update is called
adLockPessimistic 2 The provider locks each record before and after you edit, and prevents other users from modifying the data
adLockReadOnly 1 (Default) Read-only data
adLockUnspecified -1 Lock type unknown
Options
This optional parameter tells the provider how to evaluate the Source parameter when it contains something other than a Command object. The appropriate use of this option can speed up performance since ADO will not have to determine the type of the data source.
Possible values for the CommandTypeEnum enumeration are:
Constant Value Description
adCmdUnspecified -1 (Default) does not specify how to evaluate
adCmdText 1 Evaluates as a textual definition of a command or stored procedure call
adCmdTable 2 Have the provider generate an SQL query and return all rows from the specified table
adCmdStoredProc 4 Evaluate as a stored procedure
adCmdUnknown 8 The type of the CommandText parameter is unknown. ADO will make several attempts to interpret the CommandText: adCmdText then adCmdStoredProc then adCmdTable
adCmdFile 256 Evaluate as a previously persisted file
adCmdTableDirect 512 Return all rows from the specified table

Recordset properties

List of useful RecordSet properties. A full list of properties, collections and methods can be found here

BOF
Returns a Boolean value indicating if the current record position is before the first record
EOF
Returns a Boolean value indicating if the current record position is after the last record
Filter
Sets or returns a variant value that is either a string, array of bookmarks, or a FilterGroupEnum value used to filter data. You also use this property to turn an existing Filter off
MaxRecords
Sets or returns a long value that specifies the maximum number of records that can be returned to a Recordset object as the result of a query
RecordCount
Returns a long value that is the count of how many records are in a Recordset object
Sort
Sets or returns a string value that is a comma-delineated list of the names of which fields in the Recordset to sort. After each name, you can optionally add a blank space and the keywords ASC or DESC to designate the sort direction

Recordset collections

List of useful RecordSet collections. A full list of properties, collections and methods can be found here

Fields
A collection containing all of the Field objects associated with a Recordset object. For more info read MSDN article about Fields Collection
Properties
Contains all the Property objects for a specific instance of an object. For more info read MSDN article about Properties Collection

Recordset methods

A list of useful RecordSet methods can be found in section → Search/process the fetched data.

Select Excel data

Excel data may be contained in the workbook in one of the following:

  • An entire worksheet
  • A named range of cells on a worksheet
  • An unnamed range of cells on a worksheet

Specify a Worksheet
To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:

$sSQL = "SELECT * FROM [Sheet1$]"

Specify a Named Range
To specify a named range of cells as your recordsource, simply use the defined name. For example:

$sSQL = "SELECT * FROM MyRange"

Specify an Unnamed Range
To specify an unnamed range of cells as the recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

$sSQL = "SELECT * FROM [Sheet1$A1:B10]"