Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002

    Unanswered: Calling Stored Proc. with optional parameter

    I have an Access 2000 ADP that is linked to an SQL server database. I am trying to execute a stored proc. that has parameters with default values specified from within VBA code.

    I was under the impression that if I specify which parameters I am adding by using the ado command object, that I would not have to pass in all of the parameters, rather I could specify the name of the parameter, and its value, and it would be set. When I try to do this however, if I have 5 parameters, and set 3 of them using the createparameter, it is the first 3 that are set, not the named ones that I specify.

    Do I have the wrong impression about how this works??

    I am using the commands as shown below...

    stored proc defined as:
    Create proc SP (@value1 int = 0, @value2 int = 1, @value3 int = 2)

    VB CODE:
    Set adoCmd = CreateObject("ADODB.Command")
    adoCmd.CommandText = strProcName
    adoCmd.CommandType = adCmdStoredProc
    Set objNewParam = adoCmd.CreateParameter("value1", adinteger, adparaminput, , 6)
    adoCmd.Parameters.Append objNewParam
    Set objNewParam = adoCmd.CreateParameter("value3", adinteger, adparaminput, , 100)
    adoCmd.Parameters.Append objNewParam

    This appears to set the parameters @value1 and @value2, not @value1 and @value3 as desired...

    Any help greatly appreciated...


  2. #2
    Join Date
    Jun 2002

    Re: Calling Stored Proc. with optional parameter

    ADO requires all of the paremeters to be defined in order as it tries to match them by number not name. Why not just define all parameters and set the ones not required to null.

  3. #3
    Join Date
    Dec 2002

    Talking thanks

    yeah - thanks....have since found out that you are right using the method that I put in the code sample, however it is possible can do it by adding the parameters by name as follows:
    adoCmd.Parameters("@Value3") = 100

    This then sets the value of the specific parameter.

    Thanks for your help!!

Posting Permissions

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