Parameters collection


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Parameters collection

  1. #1
    martin rydman Guest

    Parameters collection


    Hi Gurus!

    Consider this code:

    Set cm = New Command
    With cm
    .ActiveConnection = "some connection info"
    .CommandType = adCmdStoredProc
    .CommandText = "MySP"

    'If I break and inspect cm.Parameters here,
    'it is fully populated according to the
    'parameters defined in MySP and contrary to all
    'documentation (at least that I know of)!
    End With

    Read my comment. This is fact of life, at least when I run it on SQL Server.
    No, a couple of questions:

    1. It seems some part of the system does an automatic cm.Refresh. Who?

    2. As I understand it, to gather the information a network roundtrip is necessary,
    which could have negativ impact on performance. Is this so?

    3. Is there a way to prevent this? It seems that as soon as the three properties
    above have correct values, the parameters collection gets populated.

    TIA!

    /Martin






  2. #2
    Martin Guest

    Re: Parameters collection


    Hi Martin,

    The order of the property-settings is important.

    Set the cmd.commandText before you set the cmd.CommandType and the parameters
    collection won't get populated automatically.
    This is one of the undocumented MS specials.

    Martin


  3. #3
    martin rydman Guest

    Re: Parameters collection


    Hi Martin!

    Well, it figures...

    Anyway, thanks! Do you have any additional thoughts on performance and such?
    If it's not critical, it's a pretty convinient feature, eh?

    /Martin

  4. #4
    Matthew Solnit Guest

    Re: Parameters collection

    "Martin" <mkvasnicka@dtc.ch> wrote in message news:3a3f5ac0$1@news.devx.com...
    >
    > Hi Martin,
    >
    > The order of the property-settings is important.
    >
    > Set the cmd.commandText before you set the cmd.CommandType and the parameters
    > collection won't get populated automatically.
    > This is one of the undocumented MS specials.


    I don't think that's correct. The order of setting CommandType and CommandText
    don't matter, except for readability.

    In fact, if you access any method in the Parameters collection +other than
    Append+, it will do an automatic Refresh.

    By using the Parameters.Append method, you can define the parameters without a
    round-trip to the database.

    -- Matthew Solnit



  5. #5
    martin rydman Guest

    Re: Parameters collection


    Hi Matthew!

    Well, that's not quite correct either. Yes, you can use the Append method,
    and you might even think that you populate the entire collection yourself.
    However, if you inspect the Parameters collection after you've populated
    it with Append, *it will contain a duplicate list of items!*. I haven't tried
    out the impact of ordering the property settings differently, but if that
    won't help, then apparently we're stuck with the round-trip!

    /Martin


    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Martin" <mkvasnicka@dtc.ch> wrote in message news:3a3f5ac0$1@news.devx.com...
    >>
    >> Hi Martin,
    >>
    >> The order of the property-settings is important.
    >>
    >> Set the cmd.commandText before you set the cmd.CommandType and the parameters
    >> collection won't get populated automatically.
    >> This is one of the undocumented MS specials.

    >
    >I don't think that's correct. The order of setting CommandType and CommandText
    >don't matter, except for readability.
    >
    >In fact, if you access any method in the Parameters collection +other than
    >Append+, it will do an automatic Refresh.
    >
    >By using the Parameters.Append method, you can define the parameters without

    a
    >round-trip to the database.
    >
    >-- Matthew Solnit
    >
    >



  6. #6
    Kevin Guest

    Re: Parameters collection


    I think the main problem here is that the ActiveConnection property is being
    set BEFORE you append the parameters.
    Do not set until AFTER you populate the parameter collection manually. Without
    an ActiveConnection then the Command object won't be able to "automatically"
    populate the collection as it won't know where to pull it from ... so it
    will leave it empty.

    This is directly from the MDSN help file:
    ***********************
    If you have not defined your own Parameter objects and you access the Parameters
    collection before calling the Refresh method, ADO will automatically call
    the method and populate the
    collection for you.

    You can minimize calls to the provider to improve performance if you know
    the properties of the parameters associated with the stored procedure or
    parameterized query you wish to call.
    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
    provider for the parameter information. If you are writing to a provider
    that does not supply parameter information, you must manually populate the
    Parameters collection using this method to be able to use parameters at all.


    Their example

    ' Open command object with one parameter.
    Set cmdByRoyalty = New ADODB.Command
    cmdByRoyalty.CommandText = "byroyalty"
    cmdByRoyalty.CommandType = adCmdStoredProc

    ' Get parameter value and append parameter.
    intRoyalty = Trim(InputBox("Enter royalty:"))
    Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", _
    adInteger, adParamInput)
    cmdByRoyalty.Parameters.Append prmByRoyalty
    prmByRoyalty.Value = intRoyalty

    ' Create recordset by executing the command.
    Set cmdByRoyalty.ActiveConnection = cnn1
    Set rstByRoyalty = cmdByRoyalty.Execute

    ************************

    Note in the example they provide the ActiveConnection property is not set
    until AFTER the parameters have been added.

    Kevin

    "martin rydman" <martin@aprire.se> wrote:
    >
    >Hi Matthew!
    >
    >Well, that's not quite correct either. Yes, you can use the Append method,
    >and you might even think that you populate the entire collection yourself.
    >However, if you inspect the Parameters collection after you've populated
    >it with Append, *it will contain a duplicate list of items!*. I haven't

    tried
    >out the impact of ordering the property settings differently, but if that
    >won't help, then apparently we're stuck with the round-trip!
    >
    >/Martin
    >
    >
    >"Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >>"Martin" <mkvasnicka@dtc.ch> wrote in message news:3a3f5ac0$1@news.devx.com...
    >>>
    >>> Hi Martin,
    >>>
    >>> The order of the property-settings is important.
    >>>
    >>> Set the cmd.commandText before you set the cmd.CommandType and the parameters
    >>> collection won't get populated automatically.
    >>> This is one of the undocumented MS specials.

    >>
    >>I don't think that's correct. The order of setting CommandType and CommandText
    >>don't matter, except for readability.
    >>
    >>In fact, if you access any method in the Parameters collection +other than
    >>Append+, it will do an automatic Refresh.
    >>
    >>By using the Parameters.Append method, you can define the parameters without

    >a
    >>round-trip to the database.
    >>
    >>-- Matthew Solnit
    >>
    >>

    >



  7. #7
    martin rydman Guest

    Re: Parameters collection


    Hi Kevin!

    Yup, that's it, I just tried it out. Thanks!

    /Martin



    "Kevin" <KDM@worldnet.net> wrote:
    >
    >I think the main problem here is that the ActiveConnection property is being
    >set BEFORE you append the parameters.
    >Do not set until AFTER you populate the parameter collection manually. Without
    >an ActiveConnection then the Command object won't be able to "automatically"
    >populate the collection as it won't know where to pull it from ... so it
    >will leave it empty.
    >
    >This is directly from the MDSN help file:
    >***********************
    >If you have not defined your own Parameter objects and you access the Parameters
    >collection before calling the Refresh method, ADO will automatically call
    >the method and populate the
    >collection for you.
    >
    >You can minimize calls to the provider to improve performance if you know
    >the properties of the parameters associated with the stored procedure or
    >parameterized query you wish to call.
    > 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
    >provider for the parameter information. If you are writing to a provider
    >that does not supply parameter information, you must manually populate the
    >Parameters collection using this method to be able to use parameters at

    all.
    >
    >
    >Their example
    >
    > ' Open command object with one parameter.
    > Set cmdByRoyalty = New ADODB.Command
    > cmdByRoyalty.CommandText = "byroyalty"
    > cmdByRoyalty.CommandType = adCmdStoredProc
    >
    > ' Get parameter value and append parameter.
    > intRoyalty = Trim(InputBox("Enter royalty:"))
    > Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", _
    > adInteger, adParamInput)
    > cmdByRoyalty.Parameters.Append prmByRoyalty
    > prmByRoyalty.Value = intRoyalty
    >
    > ' Create recordset by executing the command.
    > Set cmdByRoyalty.ActiveConnection = cnn1
    > Set rstByRoyalty = cmdByRoyalty.Execute
    >
    >************************
    >
    >Note in the example they provide the ActiveConnection property is not set
    >until AFTER the parameters have been added.
    >
    >Kevin
    >
    >"martin rydman" <martin@aprire.se> wrote:
    >>
    >>Hi Matthew!
    >>
    >>Well, that's not quite correct either. Yes, you can use the Append method,
    >>and you might even think that you populate the entire collection yourself.
    >>However, if you inspect the Parameters collection after you've populated
    >>it with Append, *it will contain a duplicate list of items!*. I haven't

    >tried
    >>out the impact of ordering the property settings differently, but if that
    >>won't help, then apparently we're stuck with the round-trip!
    >>
    >>/Martin
    >>
    >>
    >>"Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >>>"Martin" <mkvasnicka@dtc.ch> wrote in message news:3a3f5ac0$1@news.devx.com...
    >>>>
    >>>> Hi Martin,
    >>>>
    >>>> The order of the property-settings is important.
    >>>>
    >>>> Set the cmd.commandText before you set the cmd.CommandType and the parameters
    >>>> collection won't get populated automatically.
    >>>> This is one of the undocumented MS specials.
    >>>
    >>>I don't think that's correct. The order of setting CommandType and CommandText
    >>>don't matter, except for readability.
    >>>
    >>>In fact, if you access any method in the Parameters collection +other

    than
    >>>Append+, it will do an automatic Refresh.
    >>>
    >>>By using the Parameters.Append method, you can define the parameters without

    >>a
    >>>round-trip to the database.
    >>>
    >>>-- Matthew Solnit
    >>>
    >>>

    >>

    >



  8. #8
    igor Guest

    Re: Parameters collection


    "martin rydman" <martin@aprire.se> wrote:
    >
    >Hi Matthew!
    >
    >Well, that's not quite correct either. Yes, you can use the Append method,
    >and you might even think that you populate the entire collection yourself.
    >However, if you inspect the Parameters collection after you've populated
    >it with Append, *it will contain a duplicate list of items!*. I haven't

    tried
    >out the impact of ordering the property settings differently, but if that
    >won't help, then apparently we're stuck with the round-trip!
    >
    >/Martin

    Hey Martin,
    Could you please tell me how can you inspect the parameyers collection after
    you populate it, and I am serious I would like to know how to do it.
    Igor


  9. #9
    martin rydman Guest

    Re: Parameters collection


    Hi igor!

    By using the debugger. Set a breakpoint in your code where you want to start
    inspecting the collection. Add something like cm.Parameters to the watch
    window (where cm is a reference to the command-object that owns the Paramters
    collection you want to inspect).

    Note the square with a plus sign. Click the plus sign, and the Paramters
    collection is 'opened'. Now you can easily follow what happens with the Count
    property and the list of Items. Step through your code with F8 see what happens.
    Very educational!

    HTH

    /Martin




    >"martin rydman" <martin@aprire.se> wrote:
    >>
    >>Hi Matthew!
    >>
    >>Well, that's not quite correct either. Yes, you can use the Append method,
    >>and you might even think that you populate the entire collection yourself.
    >>However, if you inspect the Parameters collection after you've populated
    >>it with Append, *it will contain a duplicate list of items!*. I haven't

    >tried
    >>out the impact of ordering the property settings differently, but if that
    >>won't help, then apparently we're stuck with the round-trip!
    >>
    >>/Martin

    >Hey Martin,
    >Could you please tell me how can you inspect the parameyers collection after
    >you populate it, and I am serious I would like to know how to do it.
    >Igor
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center