displaying stored proc parms in reports

[Hubajube]

I'm using the Crystal OCX to generate a report that's getting its data from a SQL Server stored proc with parameters. I'm connecting to the db via ODBC. I'm using the StoredProcParam property array to pass the SP parameters and that's working fine. I'd like to display these parameters on the report, though, and that's where my problem is. Instead of the assigned value, it's just displaying the default value that was saved with the report.

I've tried assigning values to the ParameterFields property array, but get an error. I could create a formula and pass the values in through the Formulas property array, but that would require changing dozens of reports that we have. (If you pass a non-existant formula, you get an error.)

Any suggestions or pointers would be helpful.

 

[Hubajube]

Oh yeah, more info. If I run this in the report designer, filling in the parameter values in the dialog that pops up, the values do show up. Strange.

So if I fill in 1/1/2000 for the @start_dt parameter, then when I run the report in report designer, I get a 1/1/2000 displayed for the @start_dt that I dropped on the report. Now I save the report, and access it with the OCX, setting start_dt like this:

CrystalReport1.StoredProcParam(0) = "6/1/2002"

The stored proc sends back data based on 6/1/2000, no problem, but the @start_dt on the report is still 1/1/2000.

This is weird. It's not just me, right?

 

[Hubajube]

Well, this one seems to have stumped everyone, but I figured it out on my own. This bug will only occur if the stored procedure name does not match the alias name.

For my report, the SP name was P_Util. If I go to Database\Set Alias on the designer menu and click Set Alias, I see a dialog that says "Please select an alias to refer to: dbo.Proc(P_Util;1)". If my alias is "P_Util;1", I get the correct value for my SP parm. If my alias is anything else, I get the incorrect (default) value for my SP parm.

Hope this helps someone else somewhere down the line!

 

[Strannik]

Does anybody know how to process situation when report contains more than one stored procedure ? How should I pass sp parameters through StoredProcParam property ?
I see in CR designer that all parameters are displaying under the same node &quot;Parameter Fields&quot; but when I assign StoredProcParam = <some value> I get an error - &quot;Invalid parameter name&quot;.

 

[synapsevampire]

You should start a new thread for this.

In CR 9 you can join SP's using the Database Expert, previous versions do not support more than 1 SP per report.

-k

 

[Strannik]

What do you mean ?

I'm using CR 8.5. I've broken one sp into two sp's and can manage their parameters in Report Designer. But VB generates error when I try to access parameters of the second sp.