Question about COMMAND OBJECT in a LOOP?

Hello All,

I have 2 questions......

FIRST: if you are looping through INSERTING 500 users via a STORED
PROCEDURE, what parts of the Command Object can be INSIDE the loop and what
can be outside.... following is an example of how I did it..

Set ConnOb = Server.Createobject("ADODB.Connection")
ConnOb.Open Session("ConnectionString")
Set CmdOb = Server.Createobject("ADODB.Command")

*** LOOP ****
CmdOb.CommandText = "TheStoredProc"
CmdOb.CommandType = 4
CmdOb.Parameters.Append 'something'
CmdOb.Parameters.Append 'something else'
Set CmdOb.ActiveConnection = ConnOb
CmdOb.Execute
*** END LOOP ***

SECOND: How do I FLUSH the Parameter string after EACH loop???

thanks...

Re: Question about COMMAND OBJECT in a LOOP?

MSUTech wrote on Tue, 5 Dec 2006 11:31:01 -0800:

> Hello All,
>
> I have 2 questions......
>
> FIRST: if you are looping through INSERTING 500 users via a STORED
> PROCEDURE, what parts of the Command Object can be INSIDE the loop and
> what can be outside.... following is an example of how I did it..
>
> Set ConnOb = Server.Createobject("ADODB.Connection")
> ConnOb.Open Session("ConnectionString")
> Set CmdOb = Server.Createobject("ADODB.Command")
>
> *** LOOP ****
> CmdOb.CommandText = "TheStoredProc"
> CmdOb.CommandType = 4
> CmdOb.Parameters.Append 'something'
> CmdOb.Parameters.Append 'something else'
> Set CmdOb.ActiveConnection = ConnOb
> CmdOb.Execute
> *** END LOOP ***
>
> SECOND: How do I FLUSH the Parameter string after EACH loop???
>
> thanks...

If the stored proc name and the parameters are the same, take them outside
the loop, eg.

Set ConnOb = Server.Createobject("ADODB.Connection")
ConnOb.Open Session("ConnectionString")
Set CmdOb = Server.Createobject("ADODB.Command")

With CmdOb
.CommandText = "TheStoredProc"
.CommandType = 4
.Parameters.Append 'something'
.Parameters.Append 'something else'
.ActiveConnection = ConnOb
End With

**Loop**
With CmbOb
.Parameters("something") = value1
.Parameters("something else") = value2
.Execute
End With
** End Loop **

This will change the parameter values for each step of the loop, and execute
the proc.


For clearing Parameters, you can loop through them and use the .Delete
method, or remove each by name/ordinal, eg.

For Each oParameter in CmbOb.Parameters
oParameter.Delete
Next


Or if you're changing more of command object properties, you might find it
easier to just instantiate the object again to get a clean command object.
While this will "impact performance", if you're doing it infrequently then
the application run time will likely appear not to be affected, however if
you are looping thousands of times and instantiating the object in each step
then it may well be noticeable.


Dan

Re: Question about COMMAND OBJECT in a LOOP?

THANKS!!! this helped a ton!!

"Daniel Crichton" wrote:

> MSUTech wrote on Tue, 5 Dec 2006 11:31:01 -0800:
>
> > Hello All,
> >
> > I have 2 questions......
> >
> > FIRST: if you are looping through INSERTING 500 users via a STORED
> > PROCEDURE, what parts of the Command Object can be INSIDE the loop and
> > what can be outside.... following is an example of how I did it..
> >
> > Set ConnOb = Server.Createobject("ADODB.Connection")
> > ConnOb.Open Session("ConnectionString")
> > Set CmdOb = Server.Createobject("ADODB.Command")
> >
> > *** LOOP ****
> > CmdOb.CommandText = "TheStoredProc"
> > CmdOb.CommandType = 4
> > CmdOb.Parameters.Append 'something'
> > CmdOb.Parameters.Append 'something else'
> > Set CmdOb.ActiveConnection = ConnOb
> > CmdOb.Execute
> > *** END LOOP ***
> >
> > SECOND: How do I FLUSH the Parameter string after EACH loop???
> >
> > thanks...
>
> If the stored proc name and the parameters are the same, take them outside
> the loop, eg.
>
> Set ConnOb = Server.Createobject("ADODB.Connection")
> ConnOb.Open Session("ConnectionString")
> Set CmdOb = Server.Createobject("ADODB.Command")
>
> With CmdOb
> .CommandText = "TheStoredProc"
> .CommandType = 4
> .Parameters.Append 'something'
> .Parameters.Append 'something else'
> .ActiveConnection = ConnOb
> End With
>
> **Loop**
> With CmbOb
> .Parameters("something") = value1
> .Parameters("something else") = value2
> .Execute
> End With
> ** End Loop **
>
> This will change the parameter values for each step of the loop, and execute
> the proc.
>
>
> For clearing Parameters, you can loop through them and use the .Delete
> method, or remove each by name/ordinal, eg.
>
> For Each oParameter in CmbOb.Parameters
> oParameter.Delete
> Next
>
>
> Or if you're changing more of command object properties, you might find it
> easier to just instantiate the object again to get a clean command object.
> While this will "impact performance", if you're doing it infrequently then
> the application run time will likely appear not to be affected, however if
> you are looping thousands of times and instantiating the object in each step
> then it may well be noticeable.
>
>
> Dan
>
>
>