Passing Parameters to Stored Procedures

[MattN]

Can anyone tell me how to execute a stored procedure with a parameter within VB6 code.
This code executed the procedure prior to the parameter being added.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Qy As New ADODB.Command

Set Qy.ActiveConnection = cn
Qy.CommandType = adCmdStoredProc
Qy.CommandText = "sp_insert_MainItems_1;1"

Set rs = Qy.Execute
CurrentSKU = rs!SKU
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

The stored procedure is as follows (SKU being the parameter).

CREATE PROCEDURE [sp_insert_MainItems_1] @SKU char
AS
Select * From MainItems
Where MainItems.SKU = @SKU

I need to pass a parameter of 143149 to the procedure.

If anyone can help I would be most greatful.

Regards

MattN

 

[paulbent]

Something like this should do it:

Dim Qy As New ADODB.Command
Dim prmQy As ADODB.Parameter

Set Qy.ActiveConnection = cn
Qy.CommandType = adCmdStoredProc
Qy.CommandText = "sp_insert_MainItems_1;1"
Set prmQy = New ADODB.Parameter
Set prmQy = Qy.CreateParameter("SKU", _
adChar, adParamInput)
Qy.Parameters.Append prmQy
prmQy.Value = "143149"
   
Set rs = Qy.Execute
CurrentSKU = rs!SKU

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk