Clearing the Command Objects Parameter Collection for re-use 
Author Message
 Clearing the Command Objects Parameter Collection for re-use

Thanks for the response Eric,
I have the exact same setup as you (VB 6 Enterprise Edit. SP3) but I can't
even get the code to compile.
The only difference is that I'm working on an ActiveX DLL not EXE.

The compiler stops at: DeleteParams (cmdDS.Parameters)   [with a highlighted
".Parameters"]
The description says: "Argument Not Optional"

Have you or anyone else used another method for deleting the whole
collection?

Thanks,
Alberto.


Quote:
> Alberto,
>     I just tested this and I can delete the Parameters with your code, I
use
> VB6 SP3, tested on a standard EXE project on debug mode.

> Regards,
> Eric Garza



> > I like to use the Command object to perform my ADO queries, I've been
> doing
> > them for quite sometime now in ASP no problem.
> > In cases where I have multiple database queries I like to re-use my same
> > Command.Parameters collection. The code below describes this:

> > ............
> > cmdDS.CommandText = "WS_ViewProfileByUserIDnPass"
> > Set prm = cmdDS.CreateParameter("UserID", adVarChar, adParamInput, 60,
> > frmUserID)
> > cmdDS.Parameters.Append prm
> > Set prm = cmdDS.CreateParameter("Password", adVarChar, adParamInput, 50,
> > frmPassword)
> > cmdDS.Parameters.Append prm
> > Set rsProfile = cmdDS.Execute

> > Call DeleteParams (cmdDS.Parameters)

> > function DeleteParams(x)
> >   While x.Count > 0
> >      x.Delete(0)
> >   Wend
> > end function
> > .................

> > So recently I've been trying to move most of my Database/Business Logic
> Code
> > over to a DLL written in VB6 integrated with MTS.
> > The problem I'm having is that I can't get my parameters collection
> cleared
> > out in VB6.
> > Even if I do a:
> >   cmdDS.Parameters.Delete(0)  or the loop described above I get an
error:
> > Invalid procedure call or argument
> > Has anyone done something similar? If so I would appreciate your input.
I
> > would hate to have to Dim a new Command & Parameter for every query that
I
> > do.

> > Thanks,

> > Alberto.



Fri, 05 Apr 2002 03:00:00 GMT  
 Clearing the Command Objects Parameter Collection for re-use
Why not just set the parameter values to the new values rather than clearing
them?

The source of this problem may be:
1. Make sure you specify the type of object you're passing. As the code is
written, you're passing a variant. Try rewriting the
 function:
DeleteParams(x as Parameters)
   While x.Count > 0
      x.Delete(0)
   Wend
 end function

2 It's possible that the MTS instance for the deleteParams call is not the
same instance as the one you're using for the initial database call -- in
other words, this would the case if you call ObjectContext.SetComplete
before calling the deleteParams call.

Finally, you're wasting effort "conserving" your command object. It's going
to be faster to create a new command object than to try to recycle an
existing command object for a new purpose (new commandText and Parameters).
Conserving your command object is only useful if you run the same command
multiple times, just changing the parameters.


Quote:
> I like to use the Command object to perform my ADO queries, I've been
doing
> them for quite sometime now in ASP no problem.
> In cases where I have multiple database queries I like to re-use my same
> Command.Parameters collection. The code below describes this:

> ............
> cmdDS.CommandText = "WS_ViewProfileByUserIDnPass"
> Set prm = cmdDS.CreateParameter("UserID", adVarChar, adParamInput, 60,
> frmUserID)
> cmdDS.Parameters.Append prm
> Set prm = cmdDS.CreateParameter("Password", adVarChar, adParamInput, 50,
> frmPassword)
> cmdDS.Parameters.Append prm
> Set rsProfile = cmdDS.Execute

> Call DeleteParams (cmdDS.Parameters)

> function DeleteParams(x)
>   While x.Count > 0
>      x.Delete(0)
>   Wend
> end function
> .................

> So recently I've been trying to move most of my Database/Business Logic
Code
> over to a DLL written in VB6 integrated with MTS.
> The problem I'm having is that I can't get my parameters collection
cleared
> out in VB6.
> Even if I do a:
>   cmdDS.Parameters.Delete(0)  or the loop described above I get an error:
> Invalid procedure call or argument
> Has anyone done something similar? If so I would appreciate your input. I
> would hate to have to Dim a new Command & Parameter for every query that I
> do.

> Thanks,

> Alberto.



Sat, 06 Apr 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Clearing the Command Objects Parameter Collection for re-use

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

3. Reusing Command Object

4. Using the Command Object w/Parameters

5. Dynamic SQL Using Parameters in Command Object

6. ? Executing a Stored Procedure With Output Parameters and A Resul tset using a Command Object

7. Clearing a datagrid for reuse????

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

9. Using the ADO Command Object to send an Execute command

10. ADO Command Object using Multiple Connection Objects

11. Problem passing parameters to DataReport/command object

12. How to use DE command object with a parameter

 

 
Powered by phpBB® Forum Software