setting stored proc parameter in VB code

[jacktek]

I'm running Crystal 7.0, and writing stored procedures in MS SQL 2000. This is my first attempt (or anyone else's here) to create a report that uses a parameterized stored procedure, and I can't get it to work.

I've written a simple report. One parameter. I display the parameter on the report, and the stored procedure uses it in data selection.

If I run the report using Crystal, it asks for the parameter, I enter a 'C' (without the quotes) and it displays the value on the report AND the result set from the stored procedure does contains records (i.e, the stored procedure is getting the parameter passed from Crystal.

If I run it using VB6, with the below code

crpt_Stupid_Report.ParameterFields(0) = "passedType;C;TRUE"
crpt_Stupid_Report.Action = 1

I get a "C" printed on the report, but no records are returned from the stored procedure. I can change the C in above code to an X, run it and get an X printed on the report.

If I run it using VB6, with the below code:

crpt_Stupid_Report.StoredProcParam(0) = "C"
crpt_Stupid_Report.Action = 1

I get Crystal error 20553 - Invalid parameter name.

Below is the stored procedure

CREATE PROCEDURE usp_r_AR_Stupid_Report
@passedType varchar(1)
AS
DECLARE @AsOf_Date as smalldatetime
DECLARE @Type as nvarchar(1)
SET @AsOf_Date = GetDate()
SET @Type = @passedType
SELECT * FROM uf_AR_Stupid_Records('Detail', @AsOf_Date, @Type)
GO

I'm at a loss as to why I can't do something as simple as pass a C to a stored procedure thru VB6. It should be easy. Anyone have any suggestions ??

 

[jacktek]

I changed the VB code as below:

crpt_Stupid_Report.ParameterFields(0) = "@passedType;C;TRUE"
crpt_Stupid_Report.Action = 1

i.e, I had to change the name of the parameter from passedType to @passedType in VB and Crystal (like the stored procedure has it) in order to successfully send it from VB to stored procedure thru Crystal. None of the documentation I've seen mentioned this, and you don't need to name it with a @ if you run it straight from Crystal. Weird.

Now I'm having fun LOL trying to pass a date to the stored procedure. Wish me luck......

 

[idle]

See the FAQ faq149-562 on passing stored procedures for info on the special case for dates, unless you really want to develop your sense of humor...

 

[jacktek]

Thanks for replying to my post. I passed the date as a string, as below:

crpt_Stupid_Report.ParameterFields(0) = "stupidParameter;the stupid parameter value;TRUE"
crpt_Stupid_Report.ParameterFields(1) = "@passedType;C;TRUE"
crpt_Stupid_Report.ParameterFields(2) = "@passedDate;2002-09-15;TRUE"
crpt_Stupid_Report.Action = 1

And let the stored procedure 'convert' the string to a date:

CREATE PROCEDURE usp_r_AR_Stupid_Report

@passedType char(1),
@passedDate varchar(10)

AS

DECLARE @AsOf_Date smalldatetime
DECLARE @Type as nvarchar(1)

SET @AsOf_Date=CONVERT(smalldatetime, @passedDate)
SET @Type = @passedType

SELECT * FROM uf_AR_Stupid_Records('Detail', @AsOf_Date, @Type)
GO

It works. I added that FAQ to 'favorites' so I can study it tomorrow when I'm not so tired...

Thanks again