VB6.0 - CR8 How to pass a recordset?

[Corinne]

Hello,

I'm working on a application in VB6.0 & trying to use CR8 for the report. I have the following SQL statement that produces a recordset that I wish to pass to the report.

"SELECT CombinedName, Extension, DeptName, Location, ID, Desg FROM tblEmployeeInfo "
"UNION ALL "
"SELECT CombinedName, Extension, DeptName, Location, ID, Desg FROM tblChase "
"UNION ALL "
"SELECT CombinedName, Extension, DeptName, Location, ID, Desg FROM tblFacilities "
"ORDER BY CombinedName"

This statement works just fine in VB. How do I pass it to Crystal - I had seen on another thread that you can not use union statements in Cyrstal so that why I'm looking to pass the recordset. I was researching this and "SetPrivateData" comand was mentioned but I'm not sure how to use it. Any help someone can give me would be great. Thanks in advance.

Corinne

 

[balves]

You can use UNIONs in Crystal, but using a recordset that executes on the server is also a good idea. Or a stored procedure, which is what I try to use as often as possible.

Assuming that your recordset is called adoRS and you've executed/populated it in VB, and your Crystal Report object is called Report1, then:

Report1.Database.SetDataSource adoRS Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[Corinne]

Brian,

How would I use that UNION above in Crystal? When I tried that before and checked the SQL statement it wouldn't accept it in crystal. The 3 tables that I'm trying to union do not have any links. I just want to pull 2 fields from each of the tables and order them. Thanks

Corinne

 

[balves]

After you use Crystal to build the original query with the first table, you can edit the SQL query via the Show SQL Query menu item.

When the Show SQL Query dialog box opens, you can type the UNION...statements below the original query that Crystal has created.

Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[NipsMG]

Ok.. here it is..

First thing.. unless you want to spend $100+ PER LICENSE of your software, you have to pass it this way..


In a module in VB, Declare this function:

Declare Function CreateFieldDefFile Lib "P2smon.dll" (lpUnk As Object, ByVal fileName As String, ByVal bOverWriteExistingFile As Long) As Long

In a function somewhere, do this:

 Dim hResult As Long
 hResult = CreateFieldDefFile(rstData, "C:\temp\mydef.ttx", True)

This creates a field definition file for your recordset...
Now Open CrystalReports8, and recreate your report using the Field Definition File as your database...

(You do this by selecting Add Database and picking "More Data Sources" -> "Active Data" -> "Active Data (Field Definitions Only)"

Save your report..

now, create a project w/ a Crystal Reports Designer component..

(If you need to add this to VB, go to Projects -> Components and click the "Designers" tab and select Crystal Reports 8, Then go to Projects -> Add Crystal Reports 8)"

Tell it to use a blank report...

Now, in your Viewer Form's Form_Load statement, do this:

    Dim CrApp1 As Application
    Dim CrRep As Report
    Dim CrDB As Database
    Dim CrTables As DatabaseTables
    Dim CrTable As DatabaseTable
    
    
    Set CrApp1 = New Application
    Set CrRep = CrApp1.OpenReport("c:\temp\myreport.rpt")
    Set CrDB = CrRep.Database
    Set CrTables = CrDB.Tables
    Set CrTable = CrTables.Item(1)
    rstRecordset.MoveFirst
    
    CrTable.SetPrivateData 3, rstRecordset
    CrRep.ReadRecords
    
    CRViewer1.ViewReport

Yeah i kno it's a pain in the a**, but do it once and store it in a module or something, and you're good to go.

Hope this did the trick for ya!