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. command object with recordset and in/out parameters

12. Problem passing parameters to DataReport/command object

 

 
Powered by phpBB® Forum Software