Passed parameter in stored procedure 1

[mmayo]

Using pervasive SQL 2000 I have created:

CREATE PROCEDURE spINTXCT(in :UpperDate date)
RETURNS(chrItem char (20),
chrLoc char(7), chrLayer chr(3),
numStock numeric, numQtyRemn numeric,
dtApplDate date)

AS

BEGIN

SELECT INTXCT.Itemkey, INTXCT.Location,
INTXCT.Layertyp, INTXCT.Stockqty,
INTXCT.Qtyrmn, INTXCT.Apldate
FROM INTXCT
WHERE (INTXCT.Layertyp='0')
AND (INTXCT.Apldate < :UpperDate)
ORDER BY INTXCT.Itemkey, INTXCT.Apldate;

END

...

which I invoke using:

Private Sub cmdPass_Click()
Dim rsPass As New ADODB.Recordset
Dim comPass As New ADODB.Command
Dim i As Integer

On Error GoTo PassErrHand

With comPass
.ActiveConnection = cn
.Parameters = &quot;2002-03-01&quot;
.CommandText = &quot;call spINTXCT()&quot;
End With

Set rsPass = comPass.Execute()
Set rsPass.ActiveConnection = Nothing

....

which of course fails (although, of course, it runs as a query. I assume the fault is with how I am passing the parameter, but what am I doing wrong?

 

[mirtheil]

If you are using the CALL statement, you should have the parameter in the CALL statement:
.CommandText = &quot;call spINTXCT('2002-03-01')&quot;
Hope this helps.

mirtheil@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[mmayo]

mirtheil -

Thank-you so very much! (Obviously, it works now, thanks to you!)

I'll try not to overtax this site, but I'm the only programmer here. I have the joy of marrying Pervasive and Oracle along with a number of access apps. My goal is to move the majority to VB. So...you will probably see a post or two more from me.

I really appreciate your help!

 

[mirtheil]

No problem. Glad that it worked for you. Post away. We'll be here to answer questions..
mirtheil@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician