Freetutes.com

VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

Systems Analysis - System analysis and Design tutorial for Software Engineering


You are here: Visual Basic > Advanced VB6 tutorial > Chapter 9

Using the Parameters Collection to Manipulate and Evaluate Parameters for Stored Procedures

The Parameters collection of a Command object represents the parameters passed between the requester and a stored procedure. If the stored procedure returns a value, the Parameters collection also contains an additional member (element 0) that holds the return value.

Before you can call a stored procedure that uses parameters, you must add a Parameter object to the Parameters collection for each parameter that the stored procedure uses. There are two methods for populating the Parameters collection with a stored procedure's parameters:

  • Explicitly create a Parameter object in code and append it to the Command object's Parameters collection for each parameter that the stored procedure uses. Listing 9.6 gives an example of this technique.

    LISTING 9.6
    POPULATING THE Parameters COLLECTION EXPLICITLY

    Dim cmdPubs As ADODB.Command
    Set cmdPubs = New ADODB.Command
    Set cmdPubs.ActiveConnection = connPubs

    cmdPubs.CommandText = "Publishers_All"
    cmdPubs.CommandType = adCmdStoredProc

    Dim param As ADODB.Parameter

    Set param = cmdPubs.CreateParameter("Return", _
    adInteger, _
    adParamReturnValue, , 0)
    cmdPubs.Parameters.Append param

    Set rsPubs = cmdPubs.Execute
  • Automatically populate the Parameters collection with the appropriate members by calling the Refresh method of the Command object's Parameters collection. You must, of course, do this only after you set the Command object's CommandType and CommandText properties for the stored procedure that you want to call. Listing 9.7 gives an example of this technique.

    LISTING 9.7
    POPULATING THE Parameters COLLECTION AUTOMATICALLY WITH THE Command OBJECT'S Refresh METHOD

    Dim cmdPubs As ADODB.Command
    Set cmdPubs = New ADODB.Command
    Set cmdPubs.ActiveConnection = connPubs
    With cmdPubs
    .CommandType = adCmdStoredProc
    .CommandText = "Update_Titles_Title"

    'Refresh Parameters collection
    .Parameters.Refresh

    'and then set properties of each parameter:

    '@id parameter
    .Parameters("@id").Value = txtUpdateTitleID
    .Parameters("@id").Direction = adParamInput

    '@title parameter
    .Parameters("@title").Value = txtUpdateTitleName
    .Parameters("@title").Direction = adParamInput

    .Execute
    End With

After you have populated the Parameters collection and called the stored procedure with the Command object's Execute method, you can then check the values of any Output parameters by checking the Value property of the appropriate Parameter objects. If the stored procedure furnishes a return value, the return value will appear in element 0 of the Parameters collection, as illustrated in Listing 9.8

LISTING 9.8
CALLING A STORED PROCEDURE THAT FURNISHES AN OUTPUT PARAMETER AND A RETURN VALUE

Dim cmdPubs As ADODB.Command
Set cmdPubs = New ADODB.Command
Set cmdPubs.ActiveConnection = connPubs

cmdPubs.CommandText = "Count_Titles_For_PubID"

With cmdPubs.Parameters
'append directly to parameters collection, using
'the return value of the CreateParameter method

.Append cmdPubs.CreateParameter("@PubID", _
adVarChar, _
adParamInput, _
4, _
RTrim(txtCountPubID.Text))

.Append cmdPubs.CreateParameter("@NumTitles", _
adInteger, _
adParamOutput, _
, _
0)

End With

cmdPubs.Execute
lblCountTitlesPublisher = _
cmdPubs.Parameters("@NumTitles").Value
blnSuccess = _
cmdPubs.Parameters(0).Value


  

<< Previous | Content | Next >>

Home | About Us | Privacy Policy | Contact Us

Copyright © Freetutes.com | All Rights Reserved