Using CR8.5 with VB6.0 app and SQL-Server stored procedure 1

[graabein]

Hi,

I need tips on running Crystal Reports 8.5 reports from my VB6.0 application. I want to run a stored procdure with input parameters specifying year and department, along with which database to use on the server (the same SPs are located on each database table). How do I combine all these things?

I tried the Crystal SQL Designer but had problems with the parameter fields, and besides, does this mean the CR report will query for input parameter values? The parameters are set in the VB application GUI.

I have already designed the reports with the VB plug-in, but the server/table name and database structure has changed since then, so they do not work like they are supposed to. I passed a SQL-query to the reports, so if I could just change which server + database to open before I ran the report, this would be the best solution!

Where do I design the report and how do I specify which output parameters are being used in the report?

Hints and guidelines are most welcome!


graabein

 

[FVTrainer]

Rule #1: Don't use the Crystal SQL Designer as the datasource for a report.

Secondly, I'd like to answer your question, but first I need to clarify a couple of things:

1) When you say you've designed the reports using the VB plug-in, do you mean you've created them using the Crystal Designer in vb (which creates a dsr file for your report)?

2) What do you mean by passing a sql-query to the report? Are you passing an ADO recordset, or are you modifying the Report's SqlQueryString property?

3) If you've created the reports in the Crystal Designer in vb, why are you asking "Where do I design the report"?

 

[graabein]

1) Yes, I used the designer to create dsr-files.

2) I'd like to modify the report's query-string.

3) I thought it might be difficult to restructure the report and if so where should I design it? I assume it's okay to use Crystal Reports and still call the report from my vb app?

 

[FVTrainer]

Ok, you can use Crystal Reports Designer (the app) 8.5 to build the report or you can build it using the designer inside VB. I find using Crystal Reports to be easier than the vb designer. After building the report, you can, if you choose, import it into a dsr file. Or you can leave it external to your vb app. The plus side to leaving external is that you can later modify the report without having to recompile your vb app. The downside is the same thing...anyone can modify your report, which could cause you a maintenance issue in the future if someone makes a change that causes your report to "break".

If you choose to use the dsr approach, you create your report object using the following code:

Dim myReport as new CrystalReport1

If you choose to use the external approach, you create your report object using code similar to:

Dim crAppl as New CRAXDRT.Application
Dim crRpt as CRAXDRT.Report

Set crRpt = crAppl.OpenReport(app.Path & "\" & "myReport.rpt&quot

What I don't understand is why you would pass a query string to the report when you say you want to use a stored procedure with input parameters? Those are, generally, mutually exclusive approaches to collecting your data. But, be that as it may, if you're looking for the easiest way to deal with the fact that your data source will change, then you should consider the following:

Build your reports to use an ADO dataset. At design time, start building your report by selecting the Active Data (ADO) option in the Data Explorer (located under More Data Sources | Active Data). After supplying the OLEDBD connection info, you will provide a SQL statement that returns the field list the report will use along with sample records that will allow you to accurately design the report. Make sure you will include all the fields necessary for your report, and note their names, data types, and specific order.

Once you are done with the report, you will do the following in your vb app: Prior to calling the report, you will connect to your database and create an ADO recordset. You can do this however you like, including using a stored proc. The important thing here is that the schema of the ADO recordset has to match the recordset you used when building the report (i.e., the fields have to have the same name, be in the same order, and have the same data types). After you have the ADO recordset, you pass it to the Crystal report. The report itself has no connectivity to the database. It's just a recipient of the recordset you create at runtime in your vb app. So you have complete control over the connectivity using ADO, which is really much easier to manage than Crystal's connectivity.

If this approach interests you, look at the following link for sample code:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/ADO_ConnectionMethods.zip.asp

 

[graabein]

Hi,
thanks for your help! I'll look into it right away. You got me right about the parameters.
My english is not the best...

Cheers,
graabein