ADO RecordSet Populate - Revision history

MLipok: /* Recordset collections */ added Properties Collection and some remarks and links to MSDN

2016-02-28T22:27:48Z

Recordset collections: added Properties Collection and some remarks and links to MSDN

← Older revision		Revision as of 22:27, 28 February 2016
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 ==

Mlipok at 02:23, 7 April 2014

2014-04-07T02:23:18Z

← Older revision		Revision as of 02:23, 7 April 2014
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
	\|}		\|}

Mlipok at 02:15, 7 April 2014

2014-04-07T02:15:18Z

← Older revision		Revision as of 02:15, 7 April 2014
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~~
	\|}		\|}

Rt01: /* Populate the recordset */

2014-01-17T20:33:06Z

Populate the recordset

← Older revision		Revision as of 20:33, 17 January 2014
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)

Rt01 at 10:32, 17 January 2014

2014-01-17T10:32:58Z

← Older revision		Revision as of 10:32, 17 January 2014
Line 115:		Line 115:
	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:		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]"		$sSQL = "SELECT * FROM [Sheet1$A1:B10]"

			[[Category:ADO]]

Rt01: Created page with "= 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).
..."

2014-01-17T10:28:33Z

Created page with "= 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). ..."

New page

= 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 and change the 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 → [[ADO#Ado|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 [http://msdn.microsoft.com/en-us/library/windows/desktop/ms681771%28v=vs.85%29.aspx CursorTypeEnum] enumeration are:
:{| class="wikitable"
|-
! 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
|-
| 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
|-
| adOpenStatic || 3 || A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records
|-
| adOpenUnspecified || -1|| Cursor type not specified
|}

; LockType
: This optional parameter indicates the type of lock in effect on a Recordset.
: Possible values for the [http://msdn.microsoft.com/en-us/library/windows/desktop/ms680855%28v=vs.85%29.aspx LockTypeEnum] enumeration are:
:{| class="wikitable"
|-
! 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 [http://msdn.microsoft.com/en-us/library/windows/desktop/ms675946%28v=vs.85%29.aspx CommandTypeEnum] enumeration are:
:{| class="wikitable"
|-
! Constant !! Value !! Description
|-
| adCmdFile || 256 || Evaluate as a previously persisted file
|-
| adCmdStoredProc || 4 || Evaluate as a stored procedure
|-
| 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
|-
| 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
|-
| adCmdUnspecified || -1 || (Default) does not specify how to evaluate
|}

== Recordset properties ==
List of useful RecordSet properties. 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]
; 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 [http://msdn.microsoft.com/en-us/library/windows/desktop/ms675841%28v=vs.85%29.aspx here]
; Fields
: A collection containing all of the Field objects associated with a Recordset object

== Recordset methods ==
A list of useful RecordSet methods can be found in section → [[ADO#Search.2Fprocess_the_fetched_data|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]"