"The code is exactly
how I would like to write code and the algorithms used are very
efficient and well-documented."
Van T. Dinh, Microsoft MVP
|
|
Class: CRDOData
Class to support client/server operations using Microsoft's Remote Data Objects (RDO) technology.
This class uses an existing rdoConnection to perform queries, create resultsets, execute stored procedures etc.
When using Visual Basic to deploy client/server applications, you have a variety of development choices. You can use DAO and Jet, DAO and ODBC Direct, RDO and the Remote Data Control, or program directly against the API. This class exposes functionality to use RDO against ODBC data sources. For information on the other choices, see the other classes in the Database category.
This class was designed and tested against an ODBC connection using Microsoft SQL Server. The behavior with other servers may be slightly different.
|
Procedure Name
|
Type
|
Description
|
|
(Declarations)
|
Declarations
|
Declarations and private variables for the CRDOData class
|
|
Connection
|
Property
|
Gets a pointer to rdoConnection object that was previously assigned to this property.
You must assign an rdoConnection object to this property before using the following methods: Execute, ExecuteSP, OpenResultSet, OpenResultSetFromSP, RetrieveParameters.
The CRDOData class on its own has no way to make a connection to an ODBC datasource. To assign an rdoConnection object to this property, your code would do:
Dim cnnLocal As RDO.rdoConnection
Dim rdoData As CRDOData
Set cnnLocal = New RDO.rdoConnection
cnnLocal.Connect = "dsn=pubs;uid=sa;pwd=;database=pubs"
cnnLocal.EstablishConnection rdDriverNoPrompt, False
Set rdoData = New CRDOData
' Assign an existing connection to the property here:
Set rdoData.Connection = cnnLocal
rdoData.SQL = "insert stores " & "values ('2000','The FMS Store','123 Any Street','Vienna','VA','22182')"
rdoData.Execute True
Note: The related CRDOConnection class can be used to create an rdoConnection which can be assigned to the Connection property of this class.
|
|
CursorType
|
Property
|
Sets the type of cursor to use when opening a resultset from an rdoQuery SQL statement, or from a stored procedure.
The value is a long integer set to one of the following enumerated constant values:
- rdUseIfNeeded 0 The ODBC driver will choose the appropriate style of cursors. Server-side cursors are used if they are available.
- rdUseOdbc 1 RemoteData will use the ODBC cursor library.
- rdUseServer 2 Use server-side cursors.
- rdUseClientBatch 3 RDO will use the optimistic batch cursor library.
- rdUseNone 4 Result set is not returned as a cursor.
The default value for this class is rdUseIfNeeded. See the VB RDO documentation for more information on CursorDriver settings.
|
|
KeysetSize
|
Property
|
Sets the number of rows in the keyset buffer
The settings for value must be greater than or equal to the RowsetSize property.
The KeysetSize property is a value that specifies the number of rows in the keyset for a keyset- or dynamic-type rdoResultset cursor. If the keyset size is 0 (the default), the cursor is fully keyset-driven. If the keyset size is greater than 0, the cursor is mixed (keyset-driven within the keyset and dynamic outside the keyset).
If KeysetSize is a value greater than RowsetSize, the value defines the number of rows in the keyset that are to be buffered by the driver.
Not all ODBC data sources support keyset cursors.
*Note* Because version 2.5 of the Microsoft SQL Server ODBC driver does not support mixed-style cursors, if you set a value, KeysetSize is reset to 0 and the driver returns error 01S02: "Option value changed."
*Warning* When using rdConcurLock concurrency (pessimistic), the KeysetSize determines the number of rows locked when the cursor is first opened. The entire keyset remains locked as long as the cursor remains open.
|
|
LockType
|
Property
|
Sets the type of concurrency handling
The valid settings are:
- rdConcurReadOnly 1 (Default) Cursor is read-only. No updates are allowed.
- rdConcurLock 2 Pessimistic concurrency.
- rdConcurRowVer 3 Optimistic concurrency based on row ID.
- rdConcurValues 4 Optimistic concurrency based on row values.
- rdConcurBatch 5 Optimistic concurrency using batch mode updates. Status values returned for each row successfully updated.
See the Visual Basic RDO documentation for more information on the various LockType options
|
|
MaxRows
|
Property
|
Gets the maximum number of rows to be returned from a query or processed in an action query.
The setting for value ranges from 0 to any number. If value is set to 0, no limit is placed on the number of rows returned (default). Setting value to a negative number is invalid and is automatically reset to 0.
The MaxRows property limits the number of rows processed by the remote server. When MaxRows is set to a value greater than 0, only 'n' rows are processed. When executing a query that returns rows, it means that only the first 'n' rows are returned. When executing an action query, it means that only the first 'n' rows are updated, inserted or deleted.
This property is useful in situations where limited resources prohibit management of large numbers of result set rows. By setting MaxRows to 1 on an action query, you can be assured that no more than one row will be affected by the operation.
|
|
Options
|
Property
|
Gets the options used with this class.
This property determines which RDO options will be used when creating queries and recordsets. The value is a long integer set to one of the following enumerated constant values:
- rdAsyncEnable 32 Execute operation asynchronously.
- rdExecDirect 64 Execute query using SQLExecDirect instead of SQLPrepare/ SQLExecute. The rdoQuery object's Prepared property also controls this feature.
- rdFetchLongColumns 128 Download all the data for long character and long binary columns.
The default value for this class is 0. See the VB RDO documentation for more information on Options settings.
|
|
QueryTimeout
|
Property
|
Gets the number of seconds the ODBC driver manager waits before a timeout error occurs when a query is executed.
The default QueryTimeout property setting is 30 seconds. When you're accessing an ODBC data source using the OpenResultset or Execute methods, there may be delays due to network traffic or heavy use of the remote server - perhaps caused by your query. Rather than waiting indefinitely, use the QueryTimeout property to determine how long your application should wait before the QueryTimeout event is fired and your application trips a trappable error. At this point you have the option to continue waiting for another 'n' seconds as determined by the QueryTimeout property, or cancel the query in progress by using the Cancel argument in the QueryTimeout event procedure.
Setting this property to 0 disables the timer so your query will run indefinitely. Setting QueryTimeout to 0 is not recommended for synchronous operations as your application can be blocked for the entire duration of the query.
See the VB documentation for more information on how this property affects your query and connection objects.
|
|
QueryType
|
Property
|
Gets the type of rdoQuery object created
The value of this property is set by RDO to one of the following values:
- rdQSelect 0 Select
- rdQAction 1 Action
- rdQProcedures 2 Procedural
- rdQCompound 3 The query contains both action and select statements
See the VB RDO documentation for more information on these values.
|
|
ResultSet
|
Property
|
Returns a pointer to the local rdoResultSet object that was created with OpenResultSet, or was previously assigned to this property.
After issuing an OpenResultSet or OpenResultsetFromSP method, this property will contain a pointer to the current rdoResultSet object that was created. You can use this pointer to do whatever it is you want to do with the resultset. You can assign this property to a local variable of type 'rdoResultSet', or you can use its methods and properties directly. For example,
Dim cnnLocal As RDO.rdoConnection
Dim rdoData As New CRDOData
' Create an rdoConnection object to assign to the Connection property of the rdoData variable
Set cnnLocal = New RDO.rdoConnection
cnnLocal.Connect = "dsn=pubs;uid=sa;pwd=;database=pubs"
cnnLocal.EstablishConnection rdDriverNoPrompt, False
Set rdoData.Connection = cnnLocal
' Create a query that will generate three separate resultsets
mrdoData.SQL = "select state + ' ' + pub_name as StatePub from publishers where state = 'IL' order by pub_name;" & _
"select state + ' ' + pub_name as StatePub from publishers where State = 'CA' order by pub_name;" & _
' Must disable cursor since more than one result set is being created
mrdoData.RowsetSize = 1
' Open the first resultset
mrdoData.OpenResultSet
' Now, use the Resultset property of the rdoData variable as desired.
' This code shows using the following properties and methods: EOF, MoveNext, MoreResults, and referring to members of the rdoFields collection
If Not mrdoData.ResultSet.EOF Then
Do Until mrdoData.ResultSet.EOF
Debug.Print mrdoData.ResultSet!StatePub
mrdoData.ResultSet.MoveNext
Loop
End If
If mrdoData.ResultSet.MoreResults() Then
If Not mrdoData.ResultSet.EOF Then
Do Until mrdoData.ResultSet.EOF
Debug.Print mrdoData.ResultSet!StatePub
mrdoData.ResultSet.MoveNext
Loop
End If
End If
This test produces the following output:
IL Five Lakes Publishing
CA Algodata Infosystems
|
|
ResultsetType
|
Property
|
Sets the type of rdoResultset cursor to create.
- rdOpenForwardOnly 0 Fixed set, non-scrolling.
- rdOpenKeyset 1 Updatable, fixed set, scrollable query result set cursor.
- rdOpenDynamic 2 Updatable, dynamic set, scrollable query result set cursor.
- rdOpenStatic 3 Read-only, fixed set.
Note Not all ODBC drivers or data sources support every type of rdoResultset cursor type. If you choose a cursor that is not supported, the ODBC driver attempts to revert to a supported type. If no supported type is available, a trappable error is fired.
For more information on the Type property of rdoResultset objects, see the VB RDO documentation.
|
|
RowsetSize
|
Property
|
Gets the number of rows in an rdoResultset cursor.
The upper limit of the RowsetSize is determined by the data source driver. The lower limit for value is 1, and the default value is 100.
The RowsetSize property determines how many rows of the keyset are buffered by the application. RDO uses the RowsetSize property to determine how many rows are read into memory with the ODBC SQLExtendedFetch function. Tuning the size of RowsetSize can affect performance and the amount of memory required to maintain the keyset buffer.
This property must be set before creating an rdoResultset object with OpenResultSet or OpenResultSetFromSP
For more information about the RowSetSize property, see the VB RDO documentation.
|
|
SQL
|
Property
|
Sets the SQL string to use to create the rdoQuery object when the OpenResultSet method is called.
The SQL property contains the structured query language statement that determines how rows are selected, grouped, and ordered when you execute a query. You can use a query to select rows to include in an rdoResultset object. You can also define action queries to modify data without returning rows.
This property must be set before using the Execute or OpenResultset methods. (The ExecuteSP and OpenResultSetFromSP methods ignore the setting of this property, since the stored procedure to execute is specified in a different manner.)
The SQL syntax used in a query must conform to the SQL dialect as defined by the data source query processor. The SQL dialect supported by the ODBC interface is defined by the X/Open standard. Generally, a driver scans an SQL statement looking for specific escape sequences that are used to identify non-standard operands like timestamp literals and functions.
When you need to return rows from a query, you generally provide a SELECT statement in the SQL property. The SELECT statement specifies:
1. The name of each column to return or "*" to indicate all columns of the specified tables are to be returned. Ambiguous column names must be addressed to include the table name as needed. You can also specify aggregate expressions to perform arithmetic or other functions on the columns selected.
2. The name of each table that is to be searched for the information requested. If you specify more than one table, you must provide a WHERE clause to indicate which column(s) are used to cross-reference the information in the tables. Generally, these columns have the same name and meaning. For example the CustomerID column in the Customers table and the Orders table might be referenced.
3. (Optionally) a WHERE clause to specify how to join the tables specified and how to limit or filter the number and types of rows returned. You can use parameters in the WHERE clause to specify different sets of information from query to query.
4. (Optionally) other clauses such as ORDER BY to set a particular order for the rows or GROUP BY to structure the rows in related sets.
Each SQL dialect supports different syntax and different ancillary clauses. See the documentation provided with your remote server for more details.
Specifying Parameters
-----------------------
If the SQL statement includes question mark parameter markers (?) for the query, you must provide these parameters before you execute the query. Until you reset the parameters, the same parameter values are applied each time you use the Execute or RefreshResultSet methods. To use parameters to manage SQL query parameters, you must include the "?" parameter marker in the SQL statement. Input, output, input/output and return value parameters must all be identified in this manner. In some cases, you must use the Direction property to indicate how the parameter will be used.
To specify parameter values to be used when opening a resultset, or using the Execute method, use this class's AddParameter method. To change parameters already created, use the SetParameterValue method. To retrieve the output or return value parameters, use the RetrieveParameters and GetParameterValue methods.
The following example snippet shows how to specify replaceable parameters in the SQL property (it assumes a module-level variable called 'mrdoData' already exists):
mrdoData.SQL = "select au_id, au_lname + ', ' + au_fname + ' ' + city + ' ' + state as fullname, city, address, state from Authors where State = ? and City = ? order by au_lname"
mrdoData.AddParameter "State", "CA"
mrdoData.AddParameter "City", "Oakland"
mrdoData.OpenResultSet
|
|
AddParameter
|
Method
|
Adds a parameter which will be used subsequently to supply parameters to the rdoQuery object.
Parameters MUST be supplied in the same order that they will be used for substitution in a prepared-statement query, or as arguments to a stored procedure.
Call this method as many times as necessary to supply the parameters needed for your desired action. The parameter values are saved until such time as you either modify them with SetParameterValue, or erase all parameters with ResetParameters.
The order that you supply the parameters is very important. For standalone queries, the parameters are substituted into the SQL string specified in the SQL property in the order that they are supplied. For example, the following snippet runs a standalone query that supplies five parameters. The parameters are then modified and the query is executed again (this example snippet assumes that a module-level instance of the CRDOData class called mrdoData has already been created):
mrdoData.SQL = "insert into publishers (pub_id, pub_name) values (?, ?)"
' Reset any parameters that might have been left over from a separate job
mrdoData.ResetParameters
' Add the parameter values in the order that they occur in the SQL property
mrdoData.AddParameter "pi", "9912"
mrdoData.AddParameter "pn", "Scott"
' Execute the query
mrdoData.Execute True
' Modify the existing parameters to insert a new row
mrdoData.SetParameterValue "pi", "9913"
mrdoData.SetParameterValue "pn", "Luke"
' Re-execute the query (note the 'false' flag on the Execute method to ensure that the existing rdoQuery object is used instead of being recreated. Only the parameters have changed
mrdoData.Execute False
' And execute it one more time;
mrdoData.SetParameterValue "pi", "9914"
mrdoData.SetParameterValue "pn", "Dave"
mrdoData.Execute False
When supplying parameters for ExecuteSP or OpenResultSetFromSP you must take into account not only the input parameters to the SP itself, but also any return value from the procedure, and any output parameters (arguments passed by reference).
This example shows supplying parameters to a stored procedure that has a return value. It assumes that the module-level CRDOData variable 'mrdoData' already exists, and that the following stored procedure exists in the 'pubs' database:
Create Procedure fms_AddPublisher @pub_name varChar(20), @newpubid Char(4) OUTPUT
As declare @maxcurpubid char(4)
select @maxcurpubid = max(pub_id) from publishers where pub_id > '9900' and pub_id < '9999'
select @newpubid = convert(char(4),convert(int, @maxcurpubid) + 1)
insert into publishers (pub_id, pub_name)
values (@newpubid, @pub_name)
return (1)
------------------------------------------------------------------------------------------------
' Start a transaction using the Connection property of the rdoData variable
mrdoData.Connection.BeginTrans
' Reset any parameters which may have been left over from a separate job
mrdoData.ResetParameters
' Supply the parameters. It is vital that the parameters be supplied in the correct order. The first parameter is the return value from the stored procedure:
.AddParameter "return", "", rdTypeINTEGER, rdParamReturnValue
.AddParameter "pn", "Jims Publishing", rdTypeVARCHAR, rdParamInput
mrdoData.ExecuteSP "fms_AddPublisher", True
' Retrieve the value of the output parameters and return value parameter
mrdoData.RetrieveParameters
' Display the results:
Print "Publisher added. ID: " & mrdoData.GetParameterValue("newid") & vbCrLf & "Return: " & mrdoData.GetParameterValue("return")
|
|
ApplyParameters
|
Private
|
Applies the parameters added with the AddParameter method to the rdoQuery object.
Prior to executing a query with Execute or ExecuteSP, or prior to creating a recordset with OpenResultSet or OpenResultSetFromSP, apply any parameters that may have been set with AddParameter, or modified with SetParameterValue, to the internal rdoQuery object.
|
|
Class_Initialize
|
Initialize
|
Set initial values to defaults which may be overridden with property settings
|
|
Class_Terminate
|
Terminate
|
Release rdoQuery and rdoResultset resources. Do NOT close the connection, since this was assigned externally.
|
|
CreateODBCCallStatement
|
Private
|
Formats a valid ODBC Call statement for use with stored procedures.
The elements in the private array maParameters are used to specify the parameters to include in the statement.
When executing a stored procedure with the ExecuteSP or OpenResultSetFromSP methods, an ODBC call statement is formatted, and parameter values are inserted into it. This call statement is used to actually execute the stored procedure.
The call statement may take one of these formats:
No Parameters {Call My_sp}
With Parameters {Call My_sp ('a','b')}
With return value {? = Call My_sp})
With return value & parms {? = Call My_sp ('a','b')}
|
|
Execute
|
Method
|
Executes an Action query using the value of the SQL property and the other option settings.
This method executes a dynamic SQL action query (INSERT, DELETE, UPDATE and DDL statements such as CREATE TABLE and so forth) based on the SQL property of the class. The action you perform should not return a resultset.
You may supply parameters to the query with the AddParameter method prior to executing the query. (See the AddParameter method for details.)
If the fRecreateQuery flag is set to true, a new internal rdoQuery object is created based on the current value of the SQL property. If the flag is false, then the SQL property is ignored, and the previously-created query is re-executed, after applying any changes to the parameters modified with the SetParameterValue method.
|
|
ExecuteSP
|
Method
|
Executes a saved SQL stored procedure which performs one or more non-row-returning actions.
You may supply parameters to the query with the AddParameter method prior to executing the query. (See the AddParameter method for details.) The parameters can include a return value from the stored procedure, and output parameters (passed by reference) to receive values from the stored procedure.
Since the selected stored procedure to execute is specified in the argument to this method, the value in the SQL property is ignored.
If the fRecreateQuery flag is set to true, a new internal rdoQuery object is created based on the current value of the SQL property. If the flag is false, then the SQL property is ignored, and the previously-created query is re-executed, after applying any changes to the parameters modified with the SetParameterValue method.
Based on the named of the stored procedure to execute, and any parameter values supplied with AddParameter, an ODBC 'call' statement is constructed.
|
|
GetParameterValue
|
Method
|
Returns the Value of the parameter that was previously created with the AddParameter method.
Before executing a dynamic query with Execute or OpenResultSet you may supply parameter values with the AddParameter method. Normally the value of the parameter stays the same value as whatever you assign to it with AddParameter, or how you update it with SetParameterValue. The value returned by GetParameterValue is the same value that was supplied.
If you call a stored procedure with the ExecuteSP or OpenResultSetFromSP methods, however, it is possible for the stored procedure to modify the parameters. The parameter might be an 'output' procedure (parameter passed by reference) or it might be a return value from the stored procedure.
After executing the stored procedure you can retrieve the changes to the parameters with the RetrieveParameters method. To test the changed value, use this GetParameterValue method.
The 'varIndex' parameter to this method is used to locate the parameter that was assigned with AddParameter. You may either assign a string value as the name of the parameter to get, or a number indicating the ordinal position of the parameter (starting with 0.)
For an example using this technique with the GetParameterValue method, see the information on the AddParameterValue method.
|
|
m_rdoConnection_QueryComplete
|
Private
|
The private local m_rdoConnection variable raises connection- related events. This proc simply passes the QueryComplete event on to the user of the CRDOData class.
This occurs after the query of an rdoResultset returns the first result set. You can use this event as a notification that the result set is now ready for processing.
The ErrorOccured parameter indicates if there was an error while the query was executing. If this flag is True, you should check the rdoErrors collection for more information.
The QueryComplete event fires for all queries execute on the class's private rdoConnection object. This includes those queries executed via the OpenResultset or Execute methods, as well as those executed from an associated rdoQuery object. The Query argument is an object reference indicating which query just finished executing. Using this argument, you can write a single event handler for all queries on the connection, but still customize the handler for specific queries. When executing queries against the rdoConnection object itself, RDO creates an rdoQuery object internally, and a reference to this internal rdoQuery is passed as the Query argument.
This event should be used instead of polling the StillExecuting property to test for completion of OpenResultset or Execute method queries.
|
|
m_rdoConnection_QueryTimeout
|
Private
|
The private local m_rdoConnection variable raises connection- related events. This proc simply passes the QueryTimeout event on to the user of the CRDOData class. Fired when a running query has exceeded the time specified by the QueryTimeout property.
This event is fired each time the QueryTimeout time has been reached. This event is fired on both asynchronous and synchronous queries.
The Cancel parameter indicates if RDO should cancel the query or continue processing the query and wait for the number of seconds specified in the QueryTimeout property. The default value of this parameter is True, so if your code not respond to this event, the query is canceled after the QueryTimeout time has been reached. If the value of the parameter is set to False, RDO continues to wait for the query to complete for another QueryTimeout period.
You can use this method to display a message box to the user asking them if they wanted to cancel the query, or continue to wait another N seconds.
The QueryTimeout event fires for all queries execute on this rdoConnection. This includes those queries executed via the OpenResultset or Execute methods, as well as those executed from an associated rdoQuery object. The Query argument is an object reference indicating which query just timed out. Using this argument, you can write a single event handler for all queries on the connection, but still customize the handler for specific queries. When executing queries against the rdoConnection object itself, RDO creates an rdoQuery object internally, and a reference to this internal rdoQuery is passed as the Query argument.
|
|
m_rdoConnection_WillExecute
|
Private
|
The private local m_rdoConnection variable raises connection- related events. This proc simply passes the WillExecute event on to the user of the CRDOData class. This event is fired before the execution of a query, regardless if it is an action or row-returning query
This event is fired before the execution of a query, regardless if it is an action or row-returning query. You can trap this event to disallow the execution of certain queries, or to make last-minute adjustments to the rdoQuery object's SQL string.
The Cancel argument allows you to disallow the query. The Cancel parameter will default to False, but if you set it to True, the query will not execute, and RDO generates a trappable error indicating that the query was canceled.
For example, you can pre-screen the query to make sure the WHERE clause will not cause a table-scan operation. Thus, by setting the Cancel argument to True, you can prohibit users from searching for customers with the last name of "Smith" without also providing a first name or street address.
The WillExecute event fires for all queries execute on this rdoConnection. This includes those queries executed via the OpenResultset or Execute methods, as well as those executed from an associated rdoQuery object. The Query argument is an object reference indicating which query is about to execute. Using this argument, you can write a single event handler for all queries on the connection, but still customize the handler for specific queries. When executing queries against the rdoConnection object itself, RDO creates an rdoQuery object internally, and a reference to this internal rdoQuery is passed as the Query argument.
|
|
m_rdoResultSet_Associate
|
Private
|
The private local m_rdoResultSet variable raises Resultset- related events. This proc simply passes the Associate event on to the user of the CRDOData class. This event is raised after the result set is associated with a new rdoConnection object.
This event is raised after the result set is associated with a new rdoConnection object. You can use this event to initialize the new connection. The ActiveConnection property of the associated rdoResultset object refers to the new connection.
For example, you can use the Associate event procedure to send a special query each time a connection is established, but before other operations are executed.
|
|
m_rdoResultSet_Dissociate
|
Private
|
The private local m_rdoResultSet variable raises Resultset- related events. This proc simply passes the Dissociate event on to the user of the CRDOData class.
This event is raised after the ActiveConnection property has been set to Nothing and the result set has been dissociated from its connection.
|
|
m_rdoResultSet_ResultsChanged
|
Private
|
The private local m_rdoResultSet variable raises Resultset-related events. This proc simply passes the ResultsChanged event on to the user of the CRDOData class.
This event is raised after the MoreResults method completes and a new set of rows is loaded into the result set. This event is fired even if there are no more sets and the MoreResults method returns False.
|
|
m_rdoResultSet_RowCurrencyChange
|
Private
|
The private local m_rdoResultSet variable raises Resultset- related events. This proc simply passes the RowCurrencyChange event on to the user of the CRDOData class.
This event is raised after the result set has repositioned to a new row, or has moved to either BOF or EOF. Any of the Move methods, the AbsolutePosition, PercentPosition, or Bookmark properties, or the Requery, MoreResults, or Update (after an AddNew) methods can also cause a the current row pointer to be repositioned and cause the RowCurrencyChange event to fire. The current position can be determined by accessing the AbsolutePosition, PercentPosition, or Bookmark properties of the object.
The RowCurrencyChange event can be used to execute a detail query when an associated master row currency changes. For example, if you setup a form containing a master customer record, and a set of rows corresponding to customer orders, you can use the RowCurrencyChange event to launch a query that returns all associated order information each time the user chooses another master customer record.
Note The order in which the RowCurrencyChange and Reposition events fire cannot be predicted.
|
|
m_rdoResultSet_RowStatusChanged
|
Private
|
The private local m_rdoResultSet variable raises Resultset-related events. This proc simply passes the RowStatusChanged event on to the user of the CRDOData class.
This event is raised after the status of the current row data changes. The status of a row can change due to an Delete, or Update operation. The current status for the row can be determined using the Status property of the object.
|
|
m_rdoResultSet_WillAssociate
|
Private
|
The private local m_rdoResultSet variable raises Resultset-related events. This proc simply passes the WillAssociate event on to the user of the CRDOData class.
This event israised after you set the ActiveConnection property to a valid rdoConnection object, but before the actual associateis made.
|
|
m_rdoResultSet_WillDissociate
|
Private
|
The private local m_rdoResultSet variable raises Resultset-related events. This proc simply passes the WillDissociate event on to the user of the CRDOData class.
This event occurs beforethe connection is set to nothing.
|
|
m_rdoResultSet_WillUpdateRows
|
Private
|
The private local m_rdoResultSet variable raises Resultset-related events. This proc simply passes the WillUpdateRows event on to the user of the CRDOData class.
The WillUpdateRows event is raised before updated, new and deleted rows are committed to the server. You can override the update behavior of the cursor by responding to this event and perform your own updates using stored procedures or any other mechanism you choose.
If the result set is using batch optimistic concurrency, this event is only raised when the BatchUpdate method is called. In this case, the entire set of changes is about to be transmitted to the server.
If the result set is not in a batch mode, the WillUpdateRows event is raised for each call to the Update method, since the changes for that row are immediately sent to the server.
To summarize, no matter what mode the result set is in, this event is only raised before data is actually sent to the server.
These are the valid ReturnCode settings you may assign:
rdUpdateSuccessful Your code handled the update and was successful in doing so.
rdUpdateWithCollisions Your code handled the update successfully, but some rows produced collisions (batch mode only).
rdUpdateFailed Your code attempted to handle the update, but encountered an error when doing so.
rdUpdateNotHandled Your code did not handle the update. RDO should continue notifying and if no one handles the update, RDO should update the data itself.
If you set the ReturnCode argument to rdUpdateSuccessful, RDO assumes that your code successfully handled the update. RDO will not send this event to any additional clients (if there is more than one handler of this event) and the status for the row(s) and their columns is set to rdRowUnmodified and rdColUnmodified respectively.
If you set the ReturnCode parameter to rdUpdateWithCollisions, RDO assumes that you have successfully handled the update, but some rows caused collisions. RDO will not send this event to any additional clients (if there was more than one handler of this event) and the status for the rows and their columns is not changed. It is your code's responsibility to set the column status flags during the handling of this event. The rdUpdateWithCollisions would only be used if you are using batch optimistic concurrency and you wanted to check for and handle collisions in code.
If the developer sets the ReturnCode parameter to rdUpdateFailed, RDO assumes that your code attempted to handle the update, but encountered an error while doing so. RDO will not send this event to any additional clients (if there was more than one handler of this event) and the status for the row(s) and their columns remains unchanged. Finally, RDO generates a runtime error to be trapped by the Update method causing the WillUpdate event to fire.
If you set the ReturnCode parameter to rdUpdateNotHandled, RDO will assume that the developer did not handle the update, and RDO will continue to raise this event to all remaining clients (if there was more than one handler of this event). If all clients return rdUpdateNotHandled, RDO will perform the update itself, according the normal rules.
The default value for the ReturnCode parameter is rdUpdateNotHandled, so if no client sinks the event, or no client changes the value of ReturnCode, RDO will perform the update.
|
|
OpenResultSet
|
Method
|
Opens an rdoResultset object based on the SQL property of the class, and any parameters created with the AddParameter method of the class.
The type of locking in use, the number of rows returned, what sort of cursor to create and so on, are all determined by the properties of this class that you set prior to calling the OpenResultSet method.
The resultset that is created by this method is made available via the ResultSet property of the class. You can simply refer to the ResultSet property as your local pointer to the rdoResultSet object. In addition, this method returns a pointer to this same object as the return value of the method. You might want to use this pointer by assigning it to a local variable, instead of having to refer to the ResultSet property of the class.
If you wish to requery the resultset, perhaps after changing parameters with the SetParameterValue method, you can use the related RefreshResultSet method.
|
|
OpenResultSetFromSP
|
Method
|
Executes a stored procedure that returns one or more resultsets.
This method is similar to the OpenResultSet method, except that the rdoResultSet is created by running a server-side stored procedure rather than a local query.
You may supply parameters to the query with the AddParameter method prior to executing the query. (See the AddParameter method for details.) The parameters can include a return value from the stored procedure, and output parameters (passed by reference) to receive values from the stored procedure.
Since the selected stored procedure to call is specified in the argument to this method, the value in the SQL property is ignored.
Based on the named of the stored procedure to execute, and any parameter values supplied with AddParameter, an ODBC 'call' statement is constructed
To recreate the recordset created from the stored procedure, possibly after changing parameter values with the SetParameterValue method, use the RefreshResultSet method.
Note: There are certain limitations on creating resultsets from stored procedures which return more than one resultset (or even if the stored procedure contains more than one SELECT statement.) In this case you must disable the ODBC cursor and use what is known as a "firehose cursor" (i.e. a forward-only, read-only cursor with the RowSetSize property set to 1.) A full discussion of this topic is beyond the scope of this documentation note, but a good source of information is the "Hitchhiker's Guide to Visual Basic & SQL Server" by William Vaughn, MS Press, ISBN 1-57231-567-9.
|
|
RefreshResultSet
|
Method
|
Re-runs a query that created a result set previously. If the parameter values have changed, the query will use them before requerying the resultset.
When you use the OpenResultSet and OpenResultsetFromSP methods, and internal rdoQuery object is created and supplied with parameters, if any, that are created with the AddParameter method. Once the rdoQuery object has been created, it is more efficient to re-execute it than to recreate it. Using RefreshResultSet causes the same rdoQuery object to recreate the resultset, after applying any changed parameter values.
|
|
ResetParameters
|
Method
|
Erases the array of parameters for the next query.
Any parameters that you establish with the AddParameter method, or that you modify with the SetParameterValue method, remain in effect until you change them. In many cases this is what you want, since you will only be creating a single resultset from the CRDOData class, or executing a single action query. Once you have set the parameters you want them to be saved for re-use.
If you are changing the SQL property of the CRDOData class, however, or are planning to change the stored procedure executed with the ExecuteSP or OpenResultSetFromSP methods, you will want to start with a fresh set of parameters (or perhaps your new query does not require parameters.) Use the ResetParameters method to erase any parameter that you have set.
|
|
RetrieveParameters
|
Method
|
Retrieves any output parameters or stored-procedure return values that have been updated by the server.
When you execute a stored procedure, it may provide a return value that is assigned to one of the parameters that you create with the AddParameter method. It may also alter one of the parameters passed to it (this is known as an "output" parameter, or one passed by reference.)
Parameter values altered or returned by a stored procedure, are not automatically reflected into the local parameters that you set with this class. n order to fill in the parameter values, so that they may be read back with the GetParameterValue method, you must call the RetrieveParameters method.
Because a stored procedure may return more than one resultset, or may not change an output parameter until the end of the procedure, or because setting the Return value is the last thing that the stored procedue does, It is not possible for this class to automatically know when the parameter values or return values have changed. It is up to you to call the RetrieveParameters method at the appropriate time based on what it is that the stored procedure actually does.
|
|
SetParameterValue
|
Method
|
Modifies the Value of the parameter that was previously created with the AddParameter method.
When you first create parameters for the query or stored procedure, you use the AddParameter method. If you later want to assign different parameter values, so that you may recreate the resultset, or re-execute the action query, you may use the SetParameterValue method.
You cannot change the type of the parameter with this method. This method also does not change the order in which the parameters will be evaluated.
|
|
SetUpQueryObject
|
Private
|
Instantiates the private rdoQuery object, and sets its properties based on the properties exposed in this class.
This is an internal helper function which creates the local rdoQuery object which is used to execute queries, take parameters, and open resultsets. It may either be based on a stored procedure named in the argument to the method, or it may be based on the value of the SQL property of the class.
|
Overview of Total Visual SourceBook
The source code in Total Visual
SourceBook includes modules and classes for Microsoft Access, Visual
Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily
add this professionally written, tested, and documented royalty-free code
into your applications to simplify your application development efforts.
Additional Resources
|
|
All Our Microsoft Access Products
(885K)
