SAME SUBREPORT/DATABASE PROBLEM

[kissarmi]

I am changing the database for my CR9 report at runtime using a Visual Basic application and this code:

For x = 1 To REPORTNAME.Database.Tables.Count
With REPORTNAME.Database.Tables(x).ConnectionProperties

.Item("Database&quot = "DATABASENAME"
.Item("Server&quot = "SERVERNAME"
.Item("User ID&quot = "USERID"
.Item("Password&quot = "PASSWORD"

End With

REPORTNAME.Database.Tables.Item(x).Location = REPORTNAME.Database.Tables.Item(x).Location

The database change is working for the main report, but I need some help in changing the database for the subreports.
I have gotten some responses from this request, but nothing has worked yet. The best answer seems to be with code that looks something like this:

For Each tcSection In REPORTNAME.Sections
For Each tcObj In tcSection.ReportObjects
If tcObj.Kind = crSubreportObject Then
Set tcSubReport = REPORTNAME.OpenSubreport(tcObj.Name)
End If
Next
Next

It took a while to find the correct reference just to get everything declared properly. Now when I run the application, I get an error 'Type Mismatch' within this piece of code (For Each tcSection In REPORTNAME.Sections).
I have been posting this question all over the place and haven't hit paydirt yet. Please Help.

Thanks.

 

[FVTrainer]

Is tcSection declared as CRAXDRT.Section?

It may be bad form, but if you know the subreport's name, you can simply create the subreport using something the following code:

Set crSubReport = crRpt.OpenSubreport("Top10Products&quot

Of course, if you are looking for a more generic approach, try:

Dim crSections As CRAXDRT.Sections
Dim crSection As CRAXDRT.Section
Dim crSubReport as CRAXDRT.Report
Dim intObjCount As Integer
Dim x As Integer
Dim strSubReportName as String

Set crSections = crRpt.Sections
For Each crSection In crSections
intObjCount = crSection.ReportObjects.Count
For x = 1 To intObjCount
If crSection.ReportObjects.Item(x).Kind = 5 Then
'this is a subreport
strSubReportName = crSection.ReportObjects.Item(x).SubreportName
Set crSubReport = crRpt.OpenSubreport(strSubReportName)
End If
Next x
Next

 

[kissarmi]

Thanks for the help. However, I am getting the same error message as before on the line 'Set crSections = crRpt.Sections'. Everything is declared like you suggested.
I'm starting to feel like I'm going to lose this battle.
Any ideas?

Thanks again.

 

[FVTrainer]

Can you please post the line(s) of code where you are declaring and setting the main report object in your code? At the least it will confirm that there is nothing amiss in that part of your code.

Another thought...prior to the line 'Set crSections = crRpt.Sections', enter the following line:

debug.print crRpt.Database.Tables(1).Name

What is the result (in the immediate window)? Note: All that should do is show the first table name in the main report, but I want to verify that you have a valid crystal report object before you create the crSections object.

 

[kissarmi]

Here is the code where I declare the report. 'clsCrystalConnection' is a class module I use to set the connection string for my reports.

'Dim Assmnt1cr9 As New crassmnt1cr9
'Dim crConnect As New clsCrystalCOnnection

'Private Sub Form_Load()
' Set Assmnt1cr9 = New crassmnt1cr9
' AddDataBase
' Assmnt1cr9.PaperOrientation = crLandscape
' Assmnt1cr9.ParameterFields(1).ClearCurrentValueAndRange
' 'Assmnt1cr9.ParameterFields'(1).AddCurrentValue '(frmAc'ctMaster.mskfrmAddrMasterAccountNo)
' Screen.MousePointer = vbHourglass
' CRViewer91.ReportSource = Assmnt1cr9
' Assmnt1cr9.PrinterSetup (0)
' CRViewer91.ViewReport
' Screen.MousePointer = vbDefault
'End Sub

'Private Sub Form_Resize()
' CRViewer91.Top = 0
' CRViewer91.Left = 0
' CRViewer91.Height = ScaleHeight
' CRViewer91.Width = ScaleWidth
'End Sub

This is the statement I connect with:

Assmnt1cr9.Database.LogOnServer strDLLName, strServerName, strDatabaseName, strUserName, strPassword

When I ran the debug, it returned the first table in the report.

Thanks for any help.

 

[FVTrainer]

Aack! Classes...normal vb stuff, but not something I do (not required in what I teach. Sorry). But let's talk about this anyway. I take it crassmnt1cr9 is a class that returns a report object. Is AddDataBase a sub that updates the connection information using the clsCrystalCOnnection class? Can you show me the code in AddDataBase and in clsCrystalCOnnection? Also, is AddDataBase a form level sub or is it stored in a module? (which is to say, is everything in scope?).

Just as an aside, if you manually set the subreport location outside of the class (as in, running the sample loop outside of the class), do you get the same error? I'm not suggesting that as a production solution...just curious. I tested the original loop I sent you on my CR 9/VB 6 box and it worked fine.

 

[kissarmi]

I am somewhat new to VB and CR so some of my code may actually be from tutorials and other help sources. Crassmnt1cr9 is the report object and declaring it this way might not be necessary, but the sources I use seem to take the class concept to the extreme. I'll give you my code for the report form and connection class. Also, I didn't have any luck setting the subreport location outside the class.

'''''''' REPORT FORM ''''''''''''''
Option Explicit

Dim Assmnt1cr9 As New crassmnt1cr9
Dim crConnect As New clsCrystalCOnnection

Private Sub Form_Load()
Set Assmnt1cr9 = New crassmnt1cr9
AddDataBase
Assmnt1cr9.PaperOrientation = crLandscape
Assmnt1cr9.ParameterFields(1).ClearCurrentValueAndRange
Assmnt1cr9.ParameterFields(1).AddCurrentValue (frmAcctMaster.mskfrmAddrMasterAccountNo)
Screen.MousePointer = vbHourglass
CRViewer91.ReportSource = Assmnt1cr9
Assmnt1cr9.PrinterSetup (0)
CRViewer91.ViewReport
Screen.MousePointer = vbDefault
End Sub

Private Sub Form_Resize()
CRViewer91.Top = 0
CRViewer91.Left = 0
CRViewer91.Height = ScaleHeight
CRViewer91.Width = ScaleWidth
End Sub

Public Sub AddDataBase()
'Dimension the variables that we need
Dim strDLLName As String
Dim strServerName As String
Dim strDatabaseName As String
Dim strUserName As String
Dim strPassword As String

Dim crSections As CRAXDRT.Sections
Dim crsection As CRAXDRT.Section
Dim crsubreport As CRAXDRT.Report
Dim intobjcount As Integer
Dim x As Integer
Dim strsubreportname As String

'Set the variables according to the Registry entries that
'clsCrystalConnection retrieves
strDLLName = crConnect.DLLName
strServerName = crConnect.ServerName
strDatabaseName = crConnect.DatabaseName
strUserName = crConnect.UserName
strPassword = crConnect.Password
'Log it on
Assmnt1cr9.Database.LogOnServer strDLLName, strServerName, strDatabaseName, strUserName, strPassword

For x = 1 To Assmnt1cr9.Database.Tables.Count
With Assmnt1cr9.Database.Tables(x).ConnectionProperties

.Item("Database&quot = strDatabaseName
.Item("Server&quot = "strDatabaseName"
.Item("User ID&quot = "strUserName"
.Item("Password&quot = "strPassword"

End With

Assmnt1cr9.Database.Tables.Item(x).Location = Assmnt1cr9.Database.Tables.Item(x).Location

Next

Set crSections = Assmnt1cr9.Sections
For Each crsection In crSections
intobjcount = crsection.ReportObjects.Count
For x = 1 To intobjcount
If crsection.ReportObjects.Item(x).Kind = 5 Then
strsubreportname = crsection.ReportObjects.Item(x).SubreportName
Set crsubreport = Assmnt1cr9.OpenSubreport(strsubreportname)
End If
Next x
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
Assmnt1cr9.ParameterFields(1).ClearCurrentValueAndRange
Set Assmnt1cr9 = Nothing
Set crConnect = Nothing
End Sub

'''''''''''' CONNECTION CLASS ''''''''''''

Option Explicit

Private mstrProvider As String
Private mstrServerName As String
Private mstrDatabaseName As String
Private mstrDLLName As String
Private mstrDLLConnectionString As String

Private gcnProp As ADODB.Connection
Private DBConnection As clsDatabaseConnection
Private crReport As CRAXDRT.Report

Private Sub Class_Initialize()
Dim strConnect As String
Set gcnProp = New ADODB.Connection
Set DBConnection = New clsDatabaseConnection
If DBConnection.LoginSucceeded Then
strConnect = DBConnection.ConnectionString
gcnProp.Open strConnect
End If
End Sub

Private Sub Class_Terminate()
gcnProp.Close
Set gcnProp = Nothing
End Sub

Public Property Get DLLName() As String
DLLName = GetSetting("sandw", App.Title, "CrystalDLL", "p2ssql.dll&quot
'DLLName = GetSetting("sandw", App.Title, "CrystalDLL", "p2soledb.dll&quot
End Property

Public Property Let DLLName(ByVal strDLLName As String)
mstrDLLName = strDLLName
SaveSetting "sandw", App.Title, "CrystalDLL", mstrDLLName
End Property

Public Property Get Provider() As String
Provider = GetSetting("sandw", App.Title, "Provider", "Provider=SQLOLEDB.1&quot
End Property

Public Property Let Provider(ByVal strProvider As String)
mstrProvider = strProvider
SaveSetting "sandw", App.Title, "Provider", mstrProvider
End Property

Public Property Get ServerName() As String
ServerName = GetSetting("sandw", App.Title, "ServerName&quot
End Property

Public Property Let ServerName(ByVal strServerName As String)
mstrServerName = strServerName
SaveSetting "sandw", App.Title, "ServerName", strServerName
End Property

Public Property Get UserName() As String
UserName = GetSetting("sandw", App.Title, "SQLUsername&quot
End Property

Public Property Let UserName(ByVal strUserName As String)
mstrUserName = strUserName
SaveSetting "sandw", App.Title, "SQLUserName", strUserName
End Property

Public Property Get Password() As String
Password = GetSetting("sandw", App.Title, "Password&quot
End Property

Public Property Let Password(ByVal strPassword As String)
mstrPassword = strPassword
SaveSetting "sandw", App.Title, "Password", strPassword
End Property

Public Property Get DatabaseName() As String
DatabaseName = GetSetting("sandw", App.Title, "DatabaseName&quot

End Property

Public Property Let DatabaseName(ByVal strDatabaseName As String)
mstrDatabaseName = strDatabaseName
SaveSetting "sandw", App.Title, "DatabaseName", strDatabaseName
End Property

Thanks again for any help.

 

[FVTrainer]

Well, I'm not sure if I have a good reply. After looking at your code, I don't know why you would be getting the "type mismatch" message. But I'll give you an entire sample app (minus the report) that is running succesfully on my pc. I would suggest modifying this app (as a test) to use your report, making the appropriate changes to the database logon info. If you continue to get the mismatch error, I would suggest you look to see if you have a configuration problem, such as multiple copies of CR on your system (generally, CR 9 plays well with previous versions, but maybe there is something unique on your box). You might also want to visit Crystal Decisions website and download the latest Hot Fixes for CR 9.

However, first I do have a couple of questions about the following lines of code:

For x = 1 To Assmnt1cr9.Database.Tables.Count
With Assmnt1cr9.Database.Tables(x).ConnectionProperties

.Item("Database&quot = strDatabaseName
.Item("Server&quot = "strDatabaseName"
.Item("User ID&quot = "strUserName"
.Item("Password&quot = "strPassword"

End With

Assmnt1cr9.Database.Tables.Item(x).Location = Assmnt1cr9.Database.Tables.Item(x).Location

Are you showing "strDatabaseName" in quotes just for this example, or is that how your code actually looks? Is your server actually called "strDatabaseName"?

Also, it looks like you are simply setting the location of the table to itself. Is there a purpose for that?


Ok, here's the sample app code. It uses a single form with the crystal viewer on the form:

*******REPORT FORM*********
Option Explicit

Dim crApp As New CRAXDRT.Application
Dim crRpt As CRAXDRT.Report

Private Sub Form_Load()

Dim crSection As CRAXDRT.Section
Dim crSections As CRAXDRT.Sections
Dim crSubReport As CRAXDRT.Report
Dim crDatabase As CRAXDRT.Database
Dim crTable As CRAXDRT.DatabaseTable
Dim crConnectionProps As CRAXDRT.ConnectionProperties

Dim strFileName As String
Dim intobjcount As Integer
Dim x As Integer
Dim strsubreportname As String

'strFileName = "C:\Program Files\Microsoft Visual Studio\VB98\Archived Apps\VB Integration Class Examples\SQL Subreport\TopPerformersOLEDB.rpt"
strFileName = "C:\Program Files\Microsoft Visual Studio\VB98\Archived Apps\VB Integration Class Examples\SQL Subreport\TopPerformers.rpt"

Set crRpt = crApp.OpenReport(strFileName)

Debug.Print crRpt.Database.Tables(1).DllName

Set crSections = crRpt.Sections
For Each crSection In crSections
intobjcount = crSection.ReportObjects.Count
For x = 1 To intobjcount
If crSection.ReportObjects.Item(x).Kind = 5 Then
strsubreportname = crSection.ReportObjects.Item(x).SubreportName
Debug.Print strsubreportname
Set crSubReport = crRpt.OpenSubreport(strsubreportname)
End If
Next x
Next

'Now setup the connections
'get the main report's database tables
Set crDatabase = crRpt.Database
For Each crTable In crDatabase.Tables
Set crConnectionProps = crTable.ConnectionProperties
crTable.DllName = "crdb_odbc.dll"
With crConnectionProps
.DeleteAll
.Add "DSN", "Northwind" 'This is the name of my odbc connection
.Add "Database", "Northwind" 'This is the name of my database on the sql server
.Add "User ID", "green"
.Add "Password", "password"
End With
'I don't need to change the location of the table, as it is the same as in the report, but if I did the code might look like:
'strNewLocation = "Northwind.dbo.NewTableName"
'crTable.Location = strNewLocation
Next

'Note: if I were using OLEDB, my code within the "With" statement above would look like:

'With crConnectionProps
' .Item("Provider&quot = "SQLOLEDB"
' .Item("Data Source&quot = "dell-laptop" 'my sql server name
' .Item("Initial Catalog&quot = "Northwind" 'the db on sql server that the table is in
' .Item("Integrated Security&quot = False 'no integrated security
' .Item("User ID&quot = "green"
' .Item("Password&quot = "password"
'End With

'Now logon for the subreport
Set crDatabase = crSubReport.Database
For Each crTable In crDatabase.Tables
Set crConnectionProps = crTable.ConnectionProperties
crTable.DllName = "crdb_odbc.dll"
With crConnectionProps
.DeleteAll
.Add "DSN", "Northwind" 'This is the name of my odbc connection
.Add "Database", "Northwind" 'This is the name of my database on the sql server
.Add "User ID", "green"
.Add "Password", "password"
End With
'I don't need to change the location of the table, as it is the same as in the report, but if I did the code might look like:
'strNewLocation = "Northwind.dbo.NewTableName"
'crTable.Location = strNewLocation
Next

Me.WindowState = 2 'maximize the report viewer
CRViewer91.ReportSource = crRpt
CRViewer91.ViewReport





End Sub


Private Sub Form_Resize()

CRViewer91.Top = 0
CRViewer91.Left = 0
CRViewer91.Height = ScaleHeight
CRViewer91.Width = ScaleWidth

End Sub

 

[GJParker]

Kissarmi

Looking at your code I cant see wher you have declared crassmnt1cr9 as a CRAXDRT.Report, may be I am missing something ?

Any way here is how I deal with Sub Reports

Dim WithEvents CRXReport As CRAXDRT.Report
Dim CRXSubReport As CRAXDRT.Report
Dim CRXSections As CRAXDRT.Sections
Dim CRXSection As CRAXDRT.Section
Dim CRXSubreportObj As CRAXDRT.SubreportObject
Dim CRXReportObjects As CRAXDRT.ReportObjects
Dim CRXReportObject As Object
Dim dbTable As CRAXDRT.DatabaseTable
Dim Appl As New CRAXDRT.Application



Private Sub SubReports()
Dim intTables As Integer

Set CRXSections = CRXReport.Sections

For Each CRXSection In CRXReport.Sections
'In each section, you get all the objects in the section.
Set CRXReportObjects = CRXSection.ReportObjects
'You cycle through the objects.
For Each CRXReportObject In CRXReportObjects
'You test the objects to see if they're subreports.
If CRXReportObject.Kind = crSubreportObject Then
'When you find a subreport, you get a hold of it.
Set CRXSubreportObj = CRXReportObject
'Finally, you open the subreport and treat it as you would any other report.
Set CRXSubReport = CRXSubreportObj.OpenSubreport

'Do whatever you need to do here

End If
Next CRXReportObject
Next CRXSection

End Sub

Hope this helps

Gary Parker
Systems Support Analyst
Manchester, England

 

[kissarmi]

I'll try the code and let you know how it goes.

As for your questions:
The quotes should not be around strDatabaseName.
And for some reason, the way I'm setting the table location is the only way it would actually change the location within the report. I got that hint from someone on the Crystal Decisions website.

Thanks.