ADO-Pass value to Stored procedure

[lb1]

I am using ADO to execute some stored procedure with success but I do have problems to pass a value to the following procedure.

I would like to pass the value “X” and execute the procedure using a command such as:

…….
With cmd
.CommandType = adCmdStoredProc
.CommandText = “SPbrand"
End With
…….

The above command works fine if there is no parameter but do not work with one.

How could I use a similar command and pass one or more variables to the stored procedure?

Any help would be greatly appreciated.
Thanks in advance.
Louis


For information this is the procedure:

CREATE PROCEDURE SPbrand

@ValLet varchar(50)

AS
SELECT TRADE_1.Trade FROM TRADE_1 GROUP BY TRADE_1.Trade HAVING (((TRADE_1.Trade) Like ‘@ValLet%’ )) ORDER BY TRADE_1.TradeName;

 

[cmmrfrds]

Here are a couple of ways - examples. First the way with most code but also most flexible.

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim param4 As Parameter, param5 As Parameter, param0 As Parameter
' Connect
Set cnn = CurrentProject.Connection

' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cnn
cmd.CommandText = "dbo.sp_costToDateClient"
cmd.CommandType = adCmdStoredProc

' Set up Return parameter.
Set param0 = cmd.CreateParameter("return", adInteger, adParamReturnValue)
cmd.Parameters.Append param0
param1.Value = 0

' Set up input parameters.
Set param1 = cmd.CreateParameter("Input", adDouble, adParamInput)
cmd.Parameters.Append param1
param1.Value = pubAlarisCaseID

' Set up output parameters.
Set param2 = cmd.CreateParameter("Output", adCurrency, adParamOutput)
cmd.Parameters.Append param2
Set param3 = cmd.CreateParameter("Output", adCurrency, adParamOutput)
cmd.Parameters.Append param3
Set param4 = cmd.CreateParameter("Output", adCurrency, adParamOutput)
cmd.Parameters.Append param4
Set param5 = cmd.CreateParameter("Output", adCurrency, adParamOutput)
cmd.Parameters.Append param5

' Execute command to run stored procedure
cmd.Execute

Another way.
cnn.[dbo.sp_costToDateClient] param1, param2, param3, etc..

 

[abdullauthan]

as simple as this...

With cmd
.CommandType = adCmdStoredProc
.CommandText = “SPbrand"
.Parameters(&quot;@ValLet&quot.Value = <your string to pass>
End With

 

[lb1]

Thank you for these answers. I can pass a parameter but I do have a problem to bind the variable in the store procedure.

For example the variable is defined as @ValLet and included in the query. This doesn't work!

How can I insert the @Vallet in the query to be able to return all values starting by F for example?


SELECT TRADE_1.Trade FROM TRADE_1 GROUP BY TRADE_1.Trade HAVING (((TRADE_1.Trade) like '@ValLet%' )) ORDER BY TRADE_1.Trade


Thank in advance
Louis

 

[cmmrfrds]

@ValLet this variable needs to be resolved. You could try creating the sql dynamically - like this.

Declare @sql varchar(4000)

Set @sql = 'SELECT TRADE_1.Trade FROM TRADE_1 GROUP BY ' +
'TRADE_1.Trade HAVING (((TRADE_1.Trade) ' + 'like ' + '' + @ValLet + '%' + '' + ')) ORDER BY TRADE_1.Trade'

EXEC (@sql)

 

[Guest_imported]

Your stored procedure should look like this:

CREATE PROCEDURE XXNAMEXX (@valLet VARCHAR(50)) AS
SELECT TRADE_1.Trade FROM TRADE_1 GROUP BY TRADE_1.Trade HAVING (((TRADE_1.Trade) like @ValLet + '%' )) ORDER BY TRADE_1.Trade

You can execute it with the SQL Statement &quot;EXECUTE XXNAMEXX @ValLet='&quot; & Textbox.Text & &quot;'&quot;

 

[Guest_imported]

Your stored procedure should look like this:

CREATE PROCEDURE XXNAMEXX (@valLet VARCHAR(50)) AS
SELECT TRADE_1.Trade FROM TRADE_1 GROUP BY TRADE_1.Trade HAVING (((TRADE_1.Trade) like @ValLet + '%' )) ORDER BY TRADE_1.Trade

You can execute it with the SQL Statement &quot;EXECUTE XXNAMEXX @ValLet='&quot; & Textbox.Text & &quot;'&quot;