Subreport: cannot/don't know how to refresh subreport dbname thru VB 1

[thomasyu]

Environment
==========
I have a CRW6 report with 2 sub-report that retrieves data from SQL server. The report is running as a .rpt file and is triggered by a Visual Basic exe.

Symptom
=======
The report produces error stating that dbname.table.field not found when the sub-report is present because the dbname not longer exist on the client site.

Problem Description
================
If we are going to run a report that is developed under another database (different name but identical structure) such as one for unit test, one for UAT and one for production, I have to refresh the connect property thru the Visual Basic code before running it. This approach works fine for those without sub-report.

Investigation Made
===============
1. Delete sub-report from the problem report
No more errors. It looks like that the sub-report is not refreshed.
2. Check connection info
I have downloaded a update60.exe in crystal decision site that allow me to change the connection info such as db name without the crystal report designer. Its seems that CRW has extracted the dbname info from my DSN and put it into the rpt itself. Not improvement if I change the dbname thru the utility.
N.B. the utility itself is buggy as it overrides my page setup from landscape to portrait and all fields are resized and eventually truncated.

 

[balves]

Go into each sub-report and use the Set Location menu item (under the Database menu) to fix the table references. If the path of each table includes the database name, you can remove it. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[thomasyu]

The setLocation has to be done on the fly and not at the crystal report designer because the db connection to determined by the login page of a VB exe. How can I arhieve that?

Also, I cannot perform SetLocation with crw designer as it seems to be embedded inside the master report and not separate rpt file.

 

[kenhamady]

What is the line of code that you are using to connect to the database of the main report? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[balves]

Right-click on the sub-report, choose "Edit SubReport" and then you'll be in the sub-report and the Database menu will be active for that sub-report.

Are you using the OCX or RDC from Visual Basic? Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[thomasyu]

The line of code that I am using to connect to the database of the main report are as follows

abstract of crystal report invoke common routine
With MyCrystalReport
.ReportFileName = strFileName
.Connect = gstrConnect 'i.e. DSN = ...;UID= ...;PWD= ...;DSQ=...
intRptCnt = .GetNSubreports
If intRptCnt > 0 Then
For intCnt = 0 To intRptCnt - 1
.SubreportToChange = .GetNthSubreportName(intCnt)
.Connect = gstrConnect
Next
End If

' go back to main report
.SubreportToChange = ""

.Destination = pintOutputType
.ReplaceSelectionFormula (pstrWhere)
.
.
other windowshow settings ...
.
.
.WindowState = crptNormal

.Formulas(0) = ....
.Formulas(1) = ....

.PrintReport
.PageZoom (150)
end with

More info on login page
My login form has 3 fields and my application is a multi-company application
- User id (text box)
- password (text box)
- company code (combo box)
(company XXX will connect to dbXXX,
company YYY will connect to dbYYY)
i.e. Each company has its own database
That's why the connection has to be refreshed by code and not by CRW designer by database menu then setLocation.

 

[kenhamady]

You are using the same string to connect both main and subreport? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[olichap]

If part of your problem is changing/updating the sub-report's data location on the fly, here is an example showing the location change and modification of sub-report parameters:

Dim rptSubRpt As New CRAXDRT.Report


For iint = 2 To 5
Set rptSubRpt = Report.OpenSubreport("rptSavPatientSumm" & iint)
With rptSubRpt
If ParNPar = True Then
.Database.Tables(1).Location = "PICKSQL.dbo.Proc(rptSavPatientSum_pg" & iint & "NPar;1)"
End If
.ParameterFields(1).AddCurrentValue (sClient)
.ParameterFields(2).AddCurrentValue (txtCodeVar.Text)
.ParameterFields(3).AddCurrentValue (dteFromTo(0).Value)
.ParameterFields(4).AddCurrentValue (dteFromTo(1).Value)
.ParameterFields(5).AddCurrentValue chkUmbrella.Value)
End With
Next iint

My example is based upon stored procs.

Oliver