Crystal 7.0, VB6, and SQL2000 Stored Proc 1

[cruford]

This is also posted in the VB5/6 forum but they said I would have better luck here...

I am re-writing a customer tracking database in VB/SQL2000 and using crystal reports 7.0.

I have managed to get a report running passing one parameter to the stored proc and it returns the correct data to the report and displays it. Here is the code:

crView.ReportFileName = App.Path & "\reports\PatientInformation.rpt"
crView.StoredProcParam(0) = frmDats.datPatient.Recordset.Fields("CustID&quot
crView.Action = 1

Now my problem is I have another report that I got the stored proc working and when in design mode in Crystal Report builder it returns correct data. However, when I call it in VB I get an error and cannot figure out why...

Run-time error '20553':

Error in file <path to report>: Invalid Parameter Name

This is the code I am using to call it:

crReports.ReportFileName = App.Path & &quot;\reports\TotalNewSetups.rpt&quot;
crReports.StoredProcParam(0) = txtStartDate.Text
crReports.StoredProcParam(1) = txtEndDate.Text
crReports.StoredProcParam(2) = cboSalesRep.Text
crReports.Action = 1

Yes all the text/combo boxes are filled with the same data I used to test the stored proc in SQL enterprise manager which returns data. I have searched and found lots of hits but nothing that answers why my parameters aren't passing.... I have also found if I dont specify the parameters in VB (rem out the .storedprocparam lines) a dialog boxes opens asking for all the parameter values, once I type in the same information as I would in VB, the report runs.

Here is my stored proc code to incase I have something wrong there, I can run this procedure with values instead of parameters and get data back.

CREATE PROCEDURE sp_TotalNewSetups
@StartDate NVARCHAR(50),
@EndDate NVARCHAR(50),
@SalesRep NVARCHAR(50)
AS
SELECT <fields>
FROM <tables>
WHERE
([ztblPatientInfo].[SalesRep] = @SalesRep AND
[ztblPhysician].[ShippedDate]>= @StartDate And [ztblPhysician].[ShippedDate]<= @EndDate AND
([ztblPatientInfo].[OrderStatus]='shipped' Or [ztblPatientInfo].[OrderStatus]='completed'))
GO

 

[vidru]

This might be what you're looking for:

http://support.crystaldecisions.com/library/kbase/articles/c2003660.asp

-dave

 

[cruford]

Well I know it's the dates I'm trying to pass now. If I remove the date range from the stored proc the report runs fine passing a sales rep name in. So I added the dates back and didn't set values for them and a dialog box pops up when running the report through VB asking for those values. If I type in a date mm/dd/yyyy and click OK, I get records. If I try to set the parameters in code like this, I get an error stating &quot;Invalid parameter name&quot;

crReports.StoredProcParam(0) = cboSalesRep.Text
crReports.StoredProcParam(1) = &quot;10/01/2003&quot;
crReports.StoredProcParam(2) = &quot;10/31/2003&quot;

OR

crReports.StoredProcParam(0) = cboSalesRep.Text
crReports.StoredProcParam(1) = txtStartDate.Text
crReports.StoredProcParam(2) = txtEndDate.Text


Anyone ever had any trouble with this and if so how did you fix it?

 

[vidru]

Try changing your stored proc params from NVARCHAR to VARCHAR. I set up a test scenario here using the OCX, and got the same error. Once I changed the param to a VARCHAR, no more error.

Good luck,

-dave

 

[cruford]

Awesome vidru thanks, I have been trying to fix that for 3 days now. Here's a star, thanks for the help

 

[vidru]

No problem... it was a learning experience for me as well (never used the OCX before).

-dave