Cystal Reports 7 MR1 and OCX StoredProcParam()

I'm writing several reports in Crystal Reports, that retrieve data from a SQL Server 7 database by calling stored procedures.

I have a VB6 app that fires my stored-procerude based reports.  The VB app prompts for the parameters and then uses StoredProcParam() to pass them to the ActiveX.  The code I'm using looks like this:

   rpt.StoredProcParam(0) = cmbDial.ListIndex
   rpt.StoredProcParam(1) = iShiftID
   rpt.StoredProcParam(2) = txtStartDateTime.Text
   rpt.StoredProcParam(3) = txtEndDateTime.Text

This was working fine in Crystal version 7, with SQL Server 7 (I'm using SQL Server driver).  When I upgraded to Crystal's MR1, all my reports started to show up empty.  A little research and I discovered that the stored procedures are not receiving the parameters (they came as empty strings).

Any clue on this problem ?  (please, write a copy to my email addr)

Thanks a lot !
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The reports which are created by you are they tied to the database or not.In other words when designing the report did you give the database name userid and stuff.
I could help you with the solution if i know how the report was designed.


ca_hecklerAuthor Commented:
The report is initially tied to the database, but since it can change, I redefine it in the VB app with the following:

   rpt.Connect = g_tConnectionString
   'in the form "DSN=server; UID=sa; PWD=; DSQ=database"

This is done after setting the filename and before the stored proc parameters.

Please note that the stored procedure gets executed, only the parameters don't get there.  I've made a test, with a SP that returns a recordset of the parameters, and I've got only empty strings. If I remove the WHERE clauses in my SPs, I get data back to the reports.
I had the same problem.Actually SQL Server does not highlight the problem.The way you should do this is when you design the report you follow the foll steps

1. You choose custom reports and then the a box appears with the list by which you can connect to the database you have to choose SQLODBC and then select Active Field Defination only

2. The above step will pop another form which has data defination select data source you say new that will get another sceen up where you feed all the reporting fields and their data type and save it.It gets saved as a ttx file and that you use to design the report.The Fields should be in order of the recordset fields.
Save the report.

3. Now in the Vb App say add crystal reports and then say export report and call the report in the vb app.

4. Now add the foll code to the app

 strCnn = "Data Source=blackhawk;User=dba;Password=chelios; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn
  If msel = 1 Then
  sql = "select us.user_guid,us.user_last_name, us.user_first_name,us.user_middle_name,ir.lilevel,ir.listatus," & _
    " ir.palevel,ir.pastatus,ir.swlevel,ir.swstatus,ir.wclevel,ir.wcstatus,ir.scon,ir.pclevel,ir.pcstatus" & _
      " from blackhawk01.dba.users us,blackhawk01.dba.ir_history ir where us.user_guid = ir.student_guid "
   Set rstUser = New ADODB.Recordset
   rstUser.CursorType = adOpenKeyset
   rstUser.LockType = adLockOptimistic
   rstUser.Open sql, cnn1, adOpenKeyset, adLockOptimistic

Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
Report.Database.SetDataSource rstUser
'Report.Section5.Suppress = True
Screen.MousePointer = vbDefault

With this your crystal app will take the value in the recorset and display in the report.If your sp is not returning the recordset then there is some problem with your stored Proc and the crystal app is got nothing to do with it.

By doing this way you can connect to any database and pass the recordset to crystal at runtime.This gives you a lot of flexibility

I hope this solves your problem

if you have any questions you can add a comment and i will try and solve that


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ca_hecklerAuthor Commented:
The way you suggest seem to me very flexible.  I've noticed that you are using the Crystal Report Smart Viewer, while I was using the Crystal Report Control.

The only thing that is not clear to me is the "Report" variable in your code. It is of type "CrystalReport", right ? But I don't have the property Database in a CrystalReport object ...  I'm probably missing something from your explanation.

Btw, I'm using Crystal 7 Pro MR1.

I am not sure as to what crystal7 pro MR1 is. I am using Crystal 7.0 Professional and i have implemented this with 6.0.if you have crystal 7.0 professional edition then you can see in VB6 that you can add crystal reports in the menu.What that does is it adds a viewer and a report designer.For the first time you can add the viewer and import the report you want and if you are adding multiple reports then you can add only the designer by setting the options to NO for the viewer.Instead of using the control this is a very good method because it is part of your application itself so when you create a set up you need not add the reports separately.
When you do it by this method you can see that it adds crystal report as an object and you can see the database property also.
This is the way to go in Crystal reports

If you need more info then you can send me a mail at

ca_hecklerAuthor Commented:

(please check the info in the beginning of the thread)

After playing a little on the options, I've came to the conclusion that the idea of building the report over Active Data may not be the best one, since it requires too much work to be done if the recordset changes, and as so it may lead to errors ...

So, I'm still building the reports over a call to a stored procedure (instead of a ADODB call in VB).  My VB application needs to fire the report and supply the parameters to the stored procedures.

In Crystal 7, all worked fine, I was using the Crystal Reports ActiveX Control, and the piece of code below to set the parameters:

   rpt.StoredProcParam(0) = cmbDial.ListIndex
   rpt.StoredProcParam(1) = iShiftID
   rpt.StoredProcParam(2) = txtStartDateTime.Text
   rpt.StoredProcParam(3) = txtEndDateTime.Text

In some point I decided to migrate to Maintenance Release 1, an upgrade to Crystal 7 (check the web) that solves tons of problems, some of them could benefit me.  After that upgrade, all the parameters came to my database server as empty strings.  I've built the stored procedure bellow to test this behavior; this SP just return an on-row recordset with the supplied parameter, as a way to test it:

/* ------------------------------------- */
Create Procedure TestParameters
   @MyParameter varchar(50),
   select 'MyParameter --> ', @MyParameter
/* ------------------------------------- */

If one runs this stored procedure from the Query Analyzer, or from the Management Console, or from any other app, it returns the correct recordset.  If one goes to Crystal Reports Designer and build a simple report for that SP, it also works fine.  The problem comes when VB need to feed that parameters.  As I noted, in Crystal 7 it works, but by upgrading to MR1 all we get is a empty string as @MyParameter.

My solution was to go back to Crystal 7, by removing MR1, which should up to be a hard and manual DLL removal process.  But as soon as I got the old Crystal 7 in place, everything started to work again.

Just wanted to post this last comment, since others may get stuck with the same problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.