Reusing Command Object 
Author Message
 Reusing Command Object

Hi, people.
I have a list of data that needs to be inserted into the database.
Here is how I'm doing in VB.

Dim Cmd As ADODB.Command
Dim i As Integer

For i = 0 to 100
    Set Cmd = New ADODB.Command
    With Cmd
        .ActiveConnection = "blahblahblah, replace this string with a
connection string"
        .CommandType = adStoredProc
        .CommandText = "ins_someSP"

        .Execute
    End With
    Set Cmd = Nothing
Next

I think this is kind of a redundant code because you create an instance of
command object,
make a connection to the database. Instead, I want to be able to reuse the
object.
Idealy, I want to code like the following.

Dim Cmd As ADODB.Command
Dim i As Integer

Set Cmd = New ADODB.Command

For i = 0 to 100
    With Cmd
        .ActiveConnection = "blahblahblah, replace this string with a
connection string"
        .CommandType = adStoredProc
        .CommandText = "ins_someSP"

        .Execute
    End With
Next
Set Cmd = Nothing

But when I do this, I get an error message that I have too many parameters.
That makes sense because every time I loop, I keep on adding parameter to
the command
object. What I want to know is to get rid of Parameter. I tried Delete
method, but no luck.

How could I accomplish this?

Thanks!



Mon, 12 Jan 2004 14:07:40 GMT  
 Reusing Command Object
Hayato,

Quote:
>Dim Cmd As ADODB.Command
>Dim i As Integer

>Set Cmd = New ADODB.Command

>For i = 0 to 100
>    With Cmd
>        .ActiveConnection = "blahblahblah, replace this
string with a
>connection string"
>        .CommandType = adStoredProc
>        .CommandText = "ins_someSP"

adVarChar, 0, i)
>        .Execute
>    End With
>Next
>Set Cmd = Nothing

>What I want to know is to get rid of Parameter. I tried
Delete
>method, but no luck.

>How could I accomplish this?

To answer your question, you should be able to delete a
parameter using:


But a more optimized way to implement would be to define
your command object and create the parameter before the
loop.  Then just update the parameter within the loop.  
Something approximately like this:

Dim Cmd As ADODB.Command
Dim i As Integer

Set Cmd = New ADODB.Command
Cmd.ActiveConnection = "blahblahblah"
Cmd.CommandType = adStoredProc
Cmd.CommandText = "ins_someSP"

adVarChar, 0, i)

For i = 0 to 100
    With Cmd

        .Execute
    End With
Next
Set Cmd = Nothing

I haven't tested it with this code, but it would be an
option I would look at using.  I can offer no guarantees
with this code because I haven't run it myself.  So play
with it and use at your own risk/success.  :-)

I hope this is helpful to you!

Best regards,
Brian



Tue, 13 Jan 2004 02:20:00 GMT  
 Reusing Command Object
You don't need a Command object to execute SPs--unless you want to capture
the return value or output parameters. Try this (Cn is a Connection object)

Set rs = New Recordset
rs..... property settings as needed
Cn.MySP p1, "p2", rs

Simple? It works. It's fast and does not require any extra overhead in ADO
to execute--not even a background refresh. No, the MySP won't appear in
statement completion--but it's recognized at runtime.

hth

--
William (Bill) Vaughn
President, Beta V Corporation
Redmond, Washington USA
www.betav.com
(425) 556-9205 (v/f)


Quote:
> Hayato,

> >Dim Cmd As ADODB.Command
> >Dim i As Integer

> >Set Cmd = New ADODB.Command

> >For i = 0 to 100
> >    With Cmd
> >        .ActiveConnection = "blahblahblah, replace this
> string with a
> >connection string"
> >        .CommandType = adStoredProc
> >        .CommandText = "ins_someSP"

> adVarChar, 0, i)
> >        .Execute
> >    End With
> >Next
> >Set Cmd = Nothing

> >What I want to know is to get rid of Parameter. I tried
> Delete
> >method, but no luck.

> >How could I accomplish this?

> To answer your question, you should be able to delete a
> parameter using:


> But a more optimized way to implement would be to define
> your command object and create the parameter before the
> loop.  Then just update the parameter within the loop.
> Something approximately like this:

> Dim Cmd As ADODB.Command
> Dim i As Integer

> Set Cmd = New ADODB.Command
> Cmd.ActiveConnection = "blahblahblah"
> Cmd.CommandType = adStoredProc
> Cmd.CommandText = "ins_someSP"

> adVarChar, 0, i)

> For i = 0 to 100
>     With Cmd

>         .Execute
>     End With
> Next
> Set Cmd = Nothing

> I haven't tested it with this code, but it would be an
> option I would look at using.  I can offer no guarantees
> with this code because I haven't run it myself.  So play
> with it and use at your own risk/success.  :-)

> I hope this is helpful to you!

> Best regards,
> Brian



Tue, 13 Jan 2004 08:26:11 GMT  
 Reusing Command Object
Yeah, that's a good idea.
Let me give it a try.

Thanks!



Quote:
> You don't need a Command object to execute SPs--unless you want to capture
> the return value or output parameters. Try this (Cn is a Connection
object)

> Set rs = New Recordset
> rs..... property settings as needed
> Cn.MySP p1, "p2", rs

> Simple? It works. It's fast and does not require any extra overhead in ADO
> to execute--not even a background refresh. No, the MySP won't appear in
> statement completion--but it's recognized at runtime.

> hth

> --
> William (Bill) Vaughn
> President, Beta V Corporation
> Redmond, Washington USA
> www.betav.com
> (425) 556-9205 (v/f)



> > Hayato,

> > >Dim Cmd As ADODB.Command
> > >Dim i As Integer

> > >Set Cmd = New ADODB.Command

> > >For i = 0 to 100
> > >    With Cmd
> > >        .ActiveConnection = "blahblahblah, replace this
> > string with a
> > >connection string"
> > >        .CommandType = adStoredProc
> > >        .CommandText = "ins_someSP"

> > adVarChar, 0, i)
> > >        .Execute
> > >    End With
> > >Next
> > >Set Cmd = Nothing

> > >What I want to know is to get rid of Parameter. I tried
> > Delete
> > >method, but no luck.

> > >How could I accomplish this?

> > To answer your question, you should be able to delete a
> > parameter using:


> > But a more optimized way to implement would be to define
> > your command object and create the parameter before the
> > loop.  Then just update the parameter within the loop.
> > Something approximately like this:

> > Dim Cmd As ADODB.Command
> > Dim i As Integer

> > Set Cmd = New ADODB.Command
> > Cmd.ActiveConnection = "blahblahblah"
> > Cmd.CommandType = adStoredProc
> > Cmd.CommandText = "ins_someSP"

> > adVarChar, 0, i)

> > For i = 0 to 100
> >     With Cmd

> >         .Execute
> >     End With
> > Next
> > Set Cmd = Nothing

> > I haven't tested it with this code, but it would be an
> > option I would look at using.  I can offer no guarantees
> > with this code because I haven't run it myself.  So play
> > with it and use at your own risk/success.  :-)

> > I hope this is helpful to you!

> > Best regards,
> > Brian



Tue, 13 Jan 2004 09:08:09 GMT  
 Reusing Command Object
Hi,

Actually, some properties of the command object such as the parameter
information, Activeconnection do not change when you execute the stored
procedure multiple times. Therefore, we do not need to place these codes
within the circle statement. This also resolves the problem you met.
Consider to change your codes as follows:

' I assume you have a string array liststr(101) as string, which stores the
parameter
'values that you want to pass into the stored procedure  

Dim Cmd As New ADODB.Command

With Cmd
    .ActiveConnection = "Provider=SQLOLEDB.1;User ID=test;Password=test
;Initial Catalog=test;Data Source=<server name>"
    .CommandType = adCmdStoredProc
    .CommandText = "ins_someSP"

10)
End With

For i = 0 To 100

   Cmd.Execute
Next

Set Cmd.ActiveConnection = Nothing
Set Cmd = Nothing



Tue, 13 Jan 2004 10:45:07 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Reusing Command Object Redux... Run-time Error 3021

2. HowTo: Reuse ADO Command Params w/ Access 2K DB

3. Object Technology Reuse Survey

4. Object Technology Reuse Survey

5. OLE Object Communication and Reuse

6. Reusing an Object Interfaced to DECommand

7. Problem reusing ADO.Recordset object in VBA script in Excel

8. Reuse application-level connection object

9. Reusing Connection objects with MSDAIPP

10. Unclear on Reuse of Connection & Recordset Objects

11. Sending command to a FoxPro database using ADODB.Command Object

12. ADO Command object and MSShape commands

 

 
Powered by phpBB® Forum Software