Parameters collection

[martin rydman]

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

[Martin]

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

[martin rydman]

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

[Matthew Solnit]

"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

[martin rydman]

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
>
>

[Kevin]

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
>>
>>
>

[martin rydman]

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
>>>
>>>
>>
>

[igor]

"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

[martin rydman]

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
>