DOCUMENT #764     
     Integrating Crystal Reports into Visual Basic Applications
     PRODUCT:  R:BASE                           VERSION:  3.1 or higher
     CATALOG:  Programming                      Area   :  Reports
     It's easy to integrate a Crystal report into a Visual Basic application. 
     And using the SQL Engine you can also access your R:BASE database from 
     Visual Basic creating a complete Windows application. There are three 
     different ways to print a Crystal Report from Visual Basic:
     Printing a compiled report
     Using the Crystal custom control
     Using the Crystal print engine
     Printing a compiled report
     A compiled Crystal report is an .EXE file that runs as an independent 
     application.  You launch a compiled report using the Visual Basic SHELL 
     command. The SHELL command is used in Visual Basic to execute an external 
     .EXE file.
     In your application, first move to the location of the compiled report and
     the associated .RPT and .MKX files. You must be in this directory to print
     the report.  Put an RBASE.CFG file with the appropriate settings, e.g. 
     MULTI ON, in this directory also. Then, execute the report using the SHELL 
     The code in your Visual Basic application might look like this:
     ChDrive "c:"
     ChDir "c:\crw\reports"
     result = Shell("test.exe", 1)
     Use a drive list box, directory list box and file list box to let the user
     select the report to print. You then have the drive, directory and report 
     name stored in properties for reference by the SHELL command. Or use the 
     Visual Basic common dialog box to prompt for the file name and then use 
     other Visual Basic commands to parse the report name into drive, directory
     and report name pieces.
     See Appendix B of the Crystal Reports for R:BASE User's Manual for a 
     listing of the necessary files to distribute a compiled report. A compiled
     Crystal report can be distributed with your Visual Basic application.
     Using the Crystal custom control
     Using the Crystal custom control is an easy way to incorporate a Crystal 
     report in your Visual Basic application. You can let the users select the 
     report to print, and modify print conditions at runtime.
     The Crystal Reports for R:BASE User's Manual has complete information about 
     the Visual Basic custom control in Chapter 12. The Visual Basic custom 
     control, CRYSTAL.VBX, is included with Crystal Reports for R:BASE. You can
     use it with Visual Basic 2.0 or 3.0, standard edition or professional 
     edition. The professional edition of Visual Basic 3.0 comes with Crystal 
     Reports 1.0 (Beware! this is not the same as Crystal Reports for R:BASE) 
     and a Crystal custom control.  The custom controls are slightly different;
     you can use either one. You can't interchange any of the other Crystal 
     program files. Note that the error "Unable to load report" indicates an 
     incompatible version of the file CRPE.DLL. Even if you choose not to 
     install the Crystal Reports from Visual Basic 3.0, the 1.0 version of the 
     file CRPE.DLL may be installed into your WINDOWS\SYSTEM directory. 
     Use the Visual Basic common dialog box to select the report to print. Both
     the Visual Basic common dialog box and the Crystal custom control are 
     hidden controls. They are visible at design time but hidden at runtime. 
     Thus you place command buttons or other objects on your form to activate 
     For example, place the following commands as the "Click" action on a 
     command button to select a report:
     CMDialog1.Filter = "Crystal Reports (*.rpt)|*.rpt"
     CMDialog1.FilterIndex = 1
     CMDialog1.DialogTitle = "Reports"
     CMDialog1.Action = 1
     ReportName = CMDialog1.Filename
     The selected report is stored in the FileName property of the common dialog 
     box.  That property is then referenced by the Crystal Reports custom 
     Report1.ReportFileName = CMDialog1.FileName
     Print the report either by setting the Action property to 1 or by using the 
     PrintReport function. The difference between the two is in how errors are 
     handled. Using the Action property you want to be sure to include error 
     handling so that your application will not abort. Use the print method that 
     best fits your application.
     1. Result% = Report1.PrintReport
     2. Report1.Action = 1
        If Report1.LastErrorNumber <> 0 Then
           MsgBox Report1.LastErrorString
        End If
     You can prompt for SORT and WHERE clauses in your Visual Basic application
     but you must structure them exactly as they would be in Crystal Reports. 
     For example, your WHERE clause must be sent like this:
     Report1.SelectionFormula = 
     {transmaster.transdate} in Date (1993, 01, 31) to Date (1993, 12, 31)
     A WHERE clause adds on to any where clause defined in the report. A SORT 
     clause replaces any sort clause defined in the report. You cannot query the 
     R:BASE database or the report unless you use the print engine. Refer to 
     Chapter 12 of the Crystal Reports for R:BASE User's Manual for more 
     If you have a password protected database you must grant SELECT access to 
     PUBLIC on SYS_TABLES, SYS_TYPES and the table(s) the report is based on. If 
     you get the error "SQL Server error" it indicates that passwords kept the 
 	 database from being opened. 
     Use the Crystal Reports Print Engine to print a Crystal report
     You have the most flexibility when using the Crystal Reports print engine 
     to print a report. Using the print engine you can query the selected report 
     for the database name, then use the R:BASE SQL Engine to query the database 
     for SORT and WHERE conditions.
     Here's sample code that will select a report, then query it to find the 
     database name and location. A command button brings up the common dialog 
     box for selecting the report name. If no report is selected, the general 
     procedure ResetReport is called; if a report is selected, the general 
     procedure GetInfo is called with the selected report name. The selected 
     report and database are displayed in list boxes.
     Variables are declared in the declarations procedure:
     Dim ReportName As String
     Dim ReportHandle As Integer
     Dim LogOnInfo As PELogOnInfo
     Dim TableNames() As String
     Dim DBName As String
     The click procedure for the command button to select a report:
     Sub Command1_Click ()
     On Error GoTo ErrHandler
     CMDialog1.Filter = "Crystal Reports (*.rpt)|*.rpt"
     CMDialog1.FilterIndex = 1
     CMDialog1.DialogTitle = "Reports"
     CMDialog1.Action = 1
     If (Not ReportName = "") Then ResetReport
     ReportName = CMDialog1.Filename
     End Sub
     The general procedure GetInfo retrieves the database name and table names 
     from the selected report. It also opens a print job. The Location and 
     LogOnInfo structures also need to be sized here.
     Sub GetInfo ()
     Dim N As Integer
     Dim i As Integer
     Dim Worked As Integer
     Dim Location As PETableLocation
     Location.Structsize = 258
     LogOnInfo.Structsize = 514
     lblReport.Caption = ReportName
     ReportHandle = PEOpenPrintJob(ReportName)
     N = PEGetNTables(ReportHandle)
     If N > 0 Then
      If PEGetNthTableLogOnInfo(ReportHandle, 0, LogOnInfo) = False Then
        DBName = "ERROR!!!!"
        DBName = LogOnInfo.DatabaseName
      End If
      lblDatabase.Caption = DBName
      ReDim TableNames(0 To N - 1)
      For i = 0 To (N - 1)
        If PEGetNthTableLocation(ReportHandle, i, Location) = False Then
          Location.Location = "ERROR!!"
        End If
        TableNames(i) = Location.Location
        List1.AddItem TableNames(i)
      Next i
      lblDatabase.Caption = "Error Retrieving Table Info"
     End If
     End Sub
     The ResetReport procedure clears out the list boxes and closes the print 
     Sub ResetReport ()
       PEClosePrintJob (ReportHandle)
       lblReport.Caption = ""
       lblDatabase.Caption = ""
     End Sub
     Once the report name is selected, and the database and table names 
     retrieved, you can print the report, or query the database using the 
     R:BASE SQL Engine.  As with the custom control, you can customize SORT and
     WHERE clauses. Using the print engine gives you more capabilities, however. 
     It lets you retrieve the existing selection formula and change it; you are
     not limited to just adding on to it. 
     See Chapter 10 of the Crystal Reports for R:BASE User's Manual for a 
     complete description of all the print engine functions. The Crystal Reports 
     on-line Help has additional code examples, particularly for Visual Basic. 
     Here's an example of how you can connect to an R:BASE database using the 
     SQL Engine and the retrieved database name. Be sure to include error 
     checking in your code. 
     'allocate the environment
     retcode = SQLAllocEnv(phenv&)
     retcode = SQLAllocConnect(phenv&, hdbc&)
     'database settings
     'transaction processing off
      scoOption = 1006  
      scostatus = 0
      retcode = SQLSetConnectOption(hdbc&, scoOption, scostatus)
     'multi user off
      scoOption = 1004
      scostatus = 0
      retcode = SQLSetConnectOption(hdbc&, scoOption, scostatus)
    'connect the database
     szUID$ = "PUBLIC"
     cbUID% = Len(szUID$)
     szAuthStr$ = "PUBLIC"
     cbAuthStr% = Len(szAuthStr$)
     retcode = SQLConnect(hdbc&, DBName, -3, szUID$, cbUID%, szAuthStr$, 
     Because the database name is stored in a variable that has been declared 
     with a length of 128, you don't compute and send the length of the database 
     name as you normally would with the SQL Engine. Instead of the length, send 
     a -3. That tells the SQL Engine the string is terminated by a null (Visual
     Basic fills the variable with nulls, not blanks). Note that if your 
     database name, including path, is exactly 128 characters, this won't work.
     There is not room at the end of the variable for the null terminator. 
     To print the report you need to set the display parameters and then call 
     the PrintReport function. For example:
      prtr% = 0
      window% = 1
      title$ = "Test Report"
      lft% = 100
      ttop% = 100
      wdth% = 480
      hght% = 300
      style% = 0
      pwindow% = 0
      result% = PEPrintReport(ReportName, prtr%, window%, title$, lft%, ttop%, 
      wdth%, hght%, style%, pwindow%)
     For more information about specific Visual Basic commands or controls refer 
     to your Visual Basic documentation or contact Microsoft Visual Basic 
     support. Chapters 10, 11 and 12 in the Crystal Reports for R:BASE User's 
     Manual have information about the print engine, the custom control and 
     creating new functions using C. The Crystal on-line Help has additional 
     code examples. Another Visual Basic - SQL Engine example is available on 
     the Microrim forum on CompuServe and on the Microrim BBS. Look for the 
     file SQLEAPP.ZIP.
     Integrating Visual Basic, Crystal Reports and the SQL Engine you can easily 
     and quickly write Windows applications for your users. Currently, Crystal 
     Reports and the SQL Engine have the same database limits as the 16-bit 
     (286) version of R:BASE 4.5 -- a maximum of 1000 tables and 2000 columns 
     depending on available memory.
     Visual Basic is a trademark of and available from Microsoft Corporation, 
     One Microsoft Way, Redmond, WA 98052, 206-882-8080