Passing an adodb.command (which calls a stored procedure)to a func

[RJ]

Dim CMD As ADODB.Command

Set CMD = Build_SP_Command("spfm_User_SalesLeadSearch_Dynamic")
Execute_SQL_GetRows_Command (CMD)
Set CMD = Nothing


Public Function Execute_SQL_GetRows_Command(CMD As ADODB.Command)
......
End Function

When the above code executes it blows immediately on the
Execute_SQL_GetRows_Command giving a type mismatch error using the
adodb.command as a passed parameter. I know that the Set CMD = ... is
functioning properly; I use it throughout the project without any problem. I
tried throwing a ByRef/Byval at the function but that didn't help. I have
several functions that return an adodb.command from a function , however this
is the first time I am trying to pass an ADODB.Command to a function.

Any ideas on the correct syntax for passing an ADODB.Command to a
sub/function?

Thanks for your help!

 

[Sylvain Lafontaine]

How is the code inside Build_SP_Command()? How is the command object and
any eventual parameters created inside this function?

Did you try with another command object created explicitely before the call
to Execute_SQL_GetRows_Command?

If CMD As ADODB.Command doesn't work, try to Dim it as an object.

 

[RJ]

This is becoming a head scratacher! I tried your good suggestion about
passing it as an object however that didn't work either.

If I change the scope of the CMD variable and NOT pass it to the routine
(just use the CMD variable direct) it works just fine! For this app its okay
to change the scope but there has to be a way to pass an adodb.command to a
function!

 

[Robert Morley]

Unless you're doing this in VB.NET (which is hard to say for sure from your
example), try removing the parentheses from CMD in your function call:

Execute_SQL_GetRows_Command CMD



Rob

 

[RJ]

We have a winner! That worked and yes I am working on an ADP.

Thank you for your response

 

[Robert Morley]

Happy to be of service. IIRC, adding parentheses around an object type
will try to pass the default property instead of the object itself. That's
why you were getting an error message.



Rob

 

[RJ]

Thanks Robert for the clarification. I truly do appreciate it.

 

[Douglas J. Steele]

I believe it passes "by value", as opposed to the default "by reference".

You could also use

Call Execute_SQL_GetRows_Command(CMD)

 

[Robert Morley]

Ah, yes, you're right...that's what I was trying to think of earlier. Am I
right in thinking that when it tries to pass the object By Value, though,
that the "value" of the object will be its default property? Or am I just
confused on this one?


Rob

 

[Douglas J. Steele]

To be honest, I'm not sure what passing an object "by value" results in
(other than not what you want!)