Point in the right direction

Results 1 to 2 of 2

Thread: Point in the right direction

  1. #1
    Join Date
    Dec 1969

    Default Point in the right direction

    I am looking for information on output parameters with the use of coommand object. I have looked into the wrox books and other various reading materials. Very limited information. Thank you in advance for all advice.

  2. #2
    Join Date
    Dec 1969

    Default Is this what you're after?

    Parameter Object (ADO)<BR> <BR><BR>A Parameter object represents a parameter or argument associated with a Command object based on a parameterized query or stored procedure.<BR><BR><BR><BR><BR>Remarks<BR><BR>Many providers support parameterized commands. These are commands where the desired action is defined once, but variables (or parameters) are used to alter some details of the command. For example, an SQL SELECT statement could use a parameter to define the matching criteria of a WHERE clause, and another to define the column name for a SORT BY clause.<BR><BR>Parameter objects represent parameters associated with parameterized queries, or the in/out arguments and the return values of stored procedures. Depending on the functionality of the provider, some collections, methods, or properties of a Parameter object may not be available.<BR><BR>With the collections, methods, and properties of a Parameter object, you can do the following: <BR><BR>Set or return the name of a parameter with the Name property.<BR><BR><BR>Set or return the value of a parameter with the Value property.<BR><BR><BR>Set or return parameter characteristics with the Attributes and Direction, Precision, NumericScale, Size, and Type properties.<BR><BR><BR>Pass long binary or character data to a parameter with the AppendChunk method. <BR>If you know the names and properties of the parameters associated with the stored procedure or parameterized query you wish to call, you can use the CreateParameter method to create Parameter objects with the appropriate property settings and use the Append method to add them to the Parameters collection. This lets you set and return parameter values without having to call the Refresh method on the Parameters collection to retrieve the parameter information from the provider, a potentially resource-intensive operation.<BR><BR>Here&#039s an example that uses some of these properties...<BR><BR>ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction Properties Example<BR>This example uses the ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction properties to execute a stored procedure.<BR><BR>Public Sub ActiveConnectionX()<BR><BR> Dim cnn1 As ADODB.Connection<BR> Dim cmdByRoyalty As ADODB.Command<BR> Dim prmByRoyalty As ADODB.Parameter<BR> Dim rstByRoyalty As ADODB.Recordset<BR> Dim rstAuthors As ADODB.Recordset<BR> Dim intRoyalty As Integer<BR> Dim strAuthorID As String<BR> Dim strCnn As String<BR><BR> &#039 Define a command object for a stored procedure.<BR> Set cnn1 = New ADODB.Connection<BR> strCnn = "Provider=sqloledb;" & _<BR> "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "<BR> cnn1.Open strCnn<BR> Set cmdByRoyalty = New ADODB.Command<BR> Set cmdByRoyalty.ActiveConnection = cnn1<BR> cmdByRoyalty.CommandText = "byroyalty"<BR> cmdByRoyalty.CommandType = adCmdStoredProc<BR> cmdByRoyalty.CommandTimeout = 15<BR> <BR> &#039 Define the stored procedure&#039s input parameter.<BR> intRoyalty = Trim(InputBox( _<BR> "Enter royalty:"))<BR> Set prmByRoyalty = New ADODB.Parameter<BR> prmByRoyalty.Type = adInteger<BR> prmByRoyalty.Size = 3<BR> prmByRoyalty.Direction = adParamInput<BR> prmByRoyalty.Value = intRoyalty<BR> cmdByRoyalty.Parameters.Append prmByRoyalty<BR> <BR> &#039 Create a recordset by executing the command.<BR> Set rstByRoyalty = cmdByRoyalty.Execute()<BR> <BR> &#039 Open the Authors table to get author names for display.<BR> Set rstAuthors = New ADODB.Recordset<BR> rstAuthors.Open "authors", strCnn, , , adCmdTable<BR> <BR> &#039 Print current data in the recordset, adding<BR> &#039 author names from Authors table.<BR> Debug.Print "Authors with " & intRoyalty & _<BR> " percent royalty"<BR> Do While Not rstByRoyalty.EOF<BR> strAuthorID = rstByRoyalty!au_id<BR> Debug.Print , rstByRoyalty!au_id & ", ";<BR> rstAuthors.Filter = "au_id = &#039" & strAuthorID & "&#039"<BR> Debug.Print rstAuthors!au_fname & " " & _<BR> rstAuthors!au_lname<BR> rstByRoyalty.MoveNext<BR> Loop<BR><BR> rstByRoyalty.Close<BR> rstAuthors.Close<BR> cnn1.Close<BR> <BR>End Sub<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts