Calling stored proc with parameters ..

[Harish Mohanbab]

Hi,

How to call a stored proc with parameters from Excel VBA? Without parameters, it goes something like -

........................................................
Set Comm = New ADODB.Command
Comm.ActiveConnection = Conn
With Comm
.CommandText = <STORED PROC NAME>
.CommandType = adCmdStoredProc
.Execute
End With
........................................................

But I am not able to call SP with multiple parameters. Can someone help please.

Thanks in advance,

Harish Mohanbabu

[John.Greenan]

You need to create a parameter object and append it. See below for an
example. For each parameter you need on parameter object. Look at the ADO
examples code for further details.



dim myParameter as adodb.parameter

set myParameter = new adodb.parameter


with myParameter
.name ="whatever the parameter name is"
.type = whatever type
end with




Comm.parameters.append myParameter


--
www.alignment-systems.com


"Harish Mohanbab" wrote:

>
> Hi,
>
> How to call a stored proc with parameters from Excel VBA? Without
> parameters, it goes something like -
>
> ........................................................
> Set Comm = New ADODB.Command
> Comm.ActiveConnection = Conn
> With Comm
> .CommandText = <STORED PROC NAME>
> .CommandType = adCmdStoredProc
> .Execute
> End With
> ........................................................
>
> But I am not able to call SP with multiple parameters. Can someone
> help please.
>
> Thanks in advance,
>
> Harish Mohanbabu
>
>
> --
> Harish Mohanbab
>
>
> ------------------------------------------------------------------------
> Harish Mohanbab's Profile: http://www.excelforum.com/member.php...fo&userid=6332
> View this thread: http://www.excelforum.com/showthread...hreadid=468186
>
>

[Harish Mohanbabu]

Hi John,

Thanks to your pointer, I finished that code last week itself :-) I had to
tweak the code slightly though. Have posted the same below. Hopefully some
one some day finds this useful -
______________________________________________
......................
......................
Set Comm = New ADODB.Command
Comm.CommandText = <Name of Stored Proc>
Comm.CommandType = adCmdStoredProc

Set paramSO = Comm.CreateParameter("SO", adWChar, adParamInput, 8,
txtField.Value)
Comm.Parameters.Append paramSO
Comm.ActiveConnection = Conn
Comm.Execute
......................
......................
______________________________________________

Thanks,

Harish Mohanbabu
MBS Axapta - MVP
http://www.harishm.com/

"John.Greenan" wrote:

> You need to create a parameter object and append it. See below for an
> example. For each parameter you need on parameter object. Look at the ADO
> examples code for further details.
>
>
>
> dim myParameter as adodb.parameter
>
> set myParameter = new adodb.parameter
>
>
> with myParameter
> .name ="whatever the parameter name is"
> .type = whatever type
> end with
>
>
>
>
> Comm.parameters.append myParameter
>
>
> --
> www.alignment-systems.com