ADO can work with data, or execute commands, in several ways:
Data can be queried or gathered based on a specific list of qualifications (selection commands).
Data can be manipulated with an action query that usually changes data in some common way throughout your data source (update commands).
Data can be restructured with statements that alter the way the data resides in a particular data source (restructuring commands).
These three types of data gathering and manipulation can be done through either SQL statements or stored procedures. The Connection object allows the execution of both SQL statements and stored procedures directly, through the use of an Execute method. The Execute method can also be used to open an entire table from your data source.
The following sections describe in detail execution of commands.
The Execute Method:-
There are two different syntaxes for the Connection object's Execute method. The first is for commands that return information in the form of a Recordset object (see Chapter 5 ), and the second is for commands that do not return anything.
The correct syntax for a call to the Execute method that returns a Recordset object is:
Set recordset = connection.Execute (CommandText, RecordsAffected,
Options)
The correct syntax for a method call that does not return any records is:
connection.Execute CommandText, RecordsAffected, Options
Table 4-3 describes each of the components found in these syntaxes.
Table 4-3. The Components of the Execute Method
| Component | Description |
| connection | A currently open Connection object. |
| CommandText |
A string value containing a SQL
statement, a table name, or a stored
procedure command.
|
| RecordsAffected |
Optional. A long value that
returns the number of records that were
affected by the call to the Execute method.
|
| Options |
Optional. A long value that
indicates the precise content of the
CommandText argument. See Table 4-4.
|
Table 4-4 describes the different values for the Options argument of the Execute method, which are values of the CommandTypeEnum.
Table 4-4. The CommandTypeEnum Values
| Constant | Value | Description |
| adCmdText | 1 | Indicates that the CommandText string is a data provider-specific command and will pass it onto that data provider for evaluation and execution. This value would be used when passing a SQL statement to a SQL data provider. |
| adCmdTable | 2 |
Indicates that the CommandText
string is evaluated as a name of a
table in the current database. This type of CommandText string,
when used with the Execute method of the Connection object, will
result in a recordset that includes the entire table specified.
|
| adCmdStoredProc | 4 |
Indicates that the CommandText
string is evaluated as a name of a
stored procedure located within the current database.
|
| adCmdUnknown | 8 |
Default. Indicates that you have
no clue what kind of
information you are passing to the data provider and that the
data provider will have to figure it out itself. This usually
indicates that the Options flag was not set.
|
| adCmdFile | 256 |
Indicates that the CommandText
string is evaluated as a name of a
file that contains a previously persisted Recordset object. This
option can be used only with the Open and Requery methods.
|
If you do not specify a value for the Options argument, then ADO has to communicate with your data provider to determine whether the CommandText string is a SQL statement, a stored procedure name, or a table name. This could take a considerable amount of time; therefore, it is recommended that you always specify the kind of information that you are sending to the data provider through the CommandText argument. Example 4-11 illustrates how the Execute method can be used to execute each type of command matching those indicated in Table 4-4.
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rst As ADODB.Recordset
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset
Set rst = New ADODB.Recordset
' use this variable to record the number of records affected by the
' Execute method
Dim lRecordsAffected As Long
' Execute method
Dim lRecordsAffected As Long
con.Open "SQLNorthwindDSN"
' execute a SQL statement on the database
Set rst = con.Execute("SELECT * FROM Orders;", _
lRecordsAffected, _
adCmdText)
' execute an open table command
Set rst = con.Execute("Orders", _
lRecordsAffected, _
adCmdTable)
rst.Close
Set rst = Nothing
' execute a stored proceudre
' notice that we did not specify the options argument, therefore,
' the data provider must determine what type of command this is
con.Execute "Invoices"
con.Close
Set con = Nothing
The OLE DB provider for ODBC can utilize the CommandText argument of the Execute method to access stored procedures with a special syntax. An example of this syntax appears in Example 4-12 where it is used to execute a Microsoft Access select query. (I will discuss the CommandText property of the Command object in Chapter 7.)
The Parameters collection belongs only to a Command object, not to a Connection object. For this reason, we cannot pass parameters to stored procedures via a Connection object. We can execute queries that do not require parameters, using this special syntax or by naming the stored procedure as in Example 4-12.
Example 4-12. Executing Stored Procedures:-
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset
Dim lRecordsAffected As Long
con.Open "SQLNorthwindDSN"
' use just the name of the stored procedure
Set rst = con.Execute("[Ten Most Expensive Products]", _
lRecordsAffected, _
adCmdStoredProc)
' use OLE DB Provider for ODBC Drivers special stored procedure
' call syntax
Set rst = con.Execute("{call SalesByCategory(1)}", _
lRecordsAffected, _
adCmdText)
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
The code that is emphasized in Example 4-12 illustrates the syntax used for calling stored procedures with the OLE DB provider for ODBC drivers.
The CommandTimeout Property:-
The behavior of the Connection object's CommandTimeout property is very similar to the ConnectionTimeout property, although the setting for one does not affect the other. As described earlier, the ConnectionTimeout property indicates the maximum number of seconds allowed when completing a connection on a specified data source. The CommandTimeout property represents the maximum number of seconds allowed for ADO to complete a given command with the Execute method.
The default value for the CommandTimeout property is 30, representing 30 seconds. If the execution of a command exceeds the number of seconds specified in the CommandTimeout property, then the command is abandoned and an error is raised. This permits your command to timeout if network traffic is too busy to carry out such an operation.
Example 4-13 shows the use of the CommandTimeout property.
Example 4-13. The CommandTimeout Property:-
Public Sub CommandTimeout( )
On Error GoTo ERR_CommandTimeout:
Dim con As ADODB.Connection
Set con = New ADODB.Connection
' set the timeout period to 2 seconds
con.CommandTimeout = 2
' attempt to open the Connection object with a connection string
con.Open "BiblioDSN"
con.Execute "[All Titles]"
' close the connection
con.Close
GoTo CleanUp:
' an error has occurred
Dim oErr As ADODB.Error
' there can be multiple errors in ADO; we must look at all of them
For Each oErr In con.Errors
Select Case (Err.Number)
Case adErrStillConnecting:
' timeout error
MsgBox "The command timed out on attempting to
execute."
Case Else:
' other type of error
MsgBox "Other Error: " & oErr.Description
End Select
Next oErr
' this code will be ran whether there was an error or not
CleanUp:
' clean up
Set con = Nothing
End Sub
The CommandTimeout property is read-only once the Connection object is opened, but while it is closed, you can either read or set its value.
No comments:
Post a Comment