Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ADO - Pass value to stored procedure

Status
Not open for further replies.

lb1

Technical User
Apr 19, 2002
109
US

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;
 
You need to create a parameter and add it to the parameters collection of the command object.

The code below should give you a clearer picture...

Set cmPhotos = New ADODB.Command
With cmPhotos
.ActiveConnection = cnPhotoConnection
.CommandText = "qryKeywordsPictures"
.CommandType = adCmdStoredProc

'set up parameter
Set parKeyword = New ADODB.Parameter
With parKeyword
.Value = CLng(Right$(strNodeKey, Len(CStr(strNodeKey)) - 1))
.Direction = adParamInput
.Name = "Key"
.Type = adNumeric
End With

'add parameter to parameters collection
.Parameters.Append parKeyword

'run the command sending the recordset to rsphotos
Set KeywordsRecordset = .Execute
End With
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top