The Connection Object - The Execute Method
(Page 4 of 15 )
This method executes the query, SQL statement, stored procedure, or provider-specific text.
Set Recordset = _
Connection.Execute(CommandText, _
[RecordsAffected], [Options])
| Parameter | Type | Description |
|---|
| CommandText | String | Contains the SQL statement, table name, stored procedure name, or provider-specific text to execute |
| | |
| RecordsAffected (Optional) | Long | For action commands, a variable into which the provider returns the number of records that the operation affected |
| | | |
| | |
| Options (Optional) | Long | A value that indicates how the provider should interpret the CommandText argument (adCmdUnspecified default value) |
Options can be one or more of the CommandTypeEnum constants:
- adCmdText, for a SQL string
- adCmdTable, for a table name, whose columns are returned via a SQL command
- adCmdTableDirect, for a table name, whose columns are all returned
- adCmdStoredProc, for a stored procedure name
- adCmdFile, for a saved recordset
- adCmdUnknown, for an unknown command type
- adCmdUnspecified, to indicate the command type is unspecified (ADO will work out the command type itself, but this will lead to poorer performance, so you should always explicitly set the command type.)
You can also add the following ExecuteOptionEnum modifiers to Options:
- adAsyncExecute, for asynchronous execution
- adAsyncFetch, for asynchronous fetching
- adAsyncFetchNonBlocking, for asynchronous fetching that does not block
- adExecuteNoRecords, for a non-row returning command
More details of these options appear under the CommandType property in Chapter 4.
The Execute method returns a new Recordset object, even if it is not used. If the command did not return any rows, the recordset will be empty. If you specify the adExecuteNoRecords option, a null recordset is returned. The Recordset object returned is always a forward-only, read-only cursor (often called a firehose cursor). However, you can specify different cursor types by using the Open method of the Recordset object instead of the Connection’s Execute method.
You can use this method with or without some arguments, and with or without it returning a recordset. For example, to return a recordset, you could use this syntax:
strCommandText = "SELECT * FROM authors"
Set rsAuthors = objConn.Execute (strCommandText, ,_
adCmdText)
If the statement specified in the strCommandText parameter does not return any rows, then you should include the adCmdExecuteNoRecords option; this can improve performance, because the null recordset is not automatically returned:
strCommandText = "UPDATE titles SET price = price * 1.10"
objConn.Execute strCommandText, , adCmdText + _
adExecuteNoRecords
To supply multiple values, you add them together.
If you want to find out how many records were affected by the command, you can use the RecordsAffected argument, passing in a variable. This is especially useful for action queries, where a recordset is not returned:
strCommandText = "UPDATE titles SET Price = Price * 1.10"
objConn.Execute strCommandText, lngRecsAffected, adCmdText
Print lngRecsAffected & " were updated."
If you are using RecordsAffected and you find that it always returns -1 when you expect a different value, check if data store options are stopping the return of this information. SQL Server, for example, has a SET NOCOUNT ON statement, which stops the number of rows affected by a command from being returned. Check that this option is not set anywhere in your command (you might not realize it’s there if you are using a stored procedure), and check that it’s not set as a global database variable.
To have the command executed asynchronously, you can also add one of the asynchronous flags to the Options argument. For example:
objConn.Execute strCommandText, lngRecsAffected, _
adCmdText + adAsyncExecute
An ExecuteComplete event will be raised when this operation finishes. This happens even if the command is executed synchronously, but is more useful when asynchronous operations are in use.
 This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now. |
Next: The Open Method >>
More ASP.NET Articles
More By Apress Publishing
/ 19 
