Change Connection / Database Location at runtime

[ChubbyArse]

Hi,

I'm having problems which may relate to my issue which I've posted earlier.
I am using global variables to change a report's data connection at runtime,
so that I don't need to have development and client copies of reports.

Seems a simple enough requirement?

The reports data, is from a single SQL Server stored procedure.
I am calling the report from a VB6 application, and the report is going
directly to the printer.
I am using Crystal Reports 9.
Here is the code I am using to change the properties:

***Code Start***

Set CRApp = New CRAXDDRT.Application
Set CRFax = CRApp.OpenReport(m_strFaxSheetPath)
Set CRTable = CRFax.Database.Tables(1)

CRFax.DiscardSavedData

CRTable.ConnectionProperties.Item("Provider&quot = "SQLOLEDB"
CRTable.ConnectionProperties.Item("Data Source&quot = m_strSQLServerName
CRTable.ConnectionProperties.Item("Initial Catalog&quot =
m_strSQLDatabaseName
CRTable.ConnectionProperties.Item("User ID&quot = m_strSQLUserID
CRTable.ConnectionProperties.Item("Password&quot = ""

CRTable.Location = m_strSQLDatabaseName & ".dbo.proc_Get_FaxDetail"

CRFax.ParameterFields.GetItemByName("@ServiceRequestID&quot.AddCurrentValue
lngServiceRequestID

***Code End***

When I set the CRTable.Location however, the reports parameters are wiped
out, gone!
If I check the CRFax.ParameterFields.Count it has changed from 1 to nil.

How can I change the database of a report at runtime that has parameters???

If this is not possible, how do I base a report on a ADO recordset??
This would the easier method as I could generate the recordset and just pass
it to the report.....?

Thanks

Alex Stevens

 

[drahcirtt]

Hi there...

I'm having the exact same problem. Also looking at ADO recordset being a possible solution, but am kinda new to the whole Crystal thing.

If you come up with a solution, please post here - I'll do the same if I find one!

 

[drahcirtt]

Ok, I've got it working for me... excuse the Delphi syntax if it's not what you're used to, but you should get the idea...

crReport.Database.LogOnServerEx('p2soledb.dll', 'SERVERNAME', '', '', '', 'OLE DB', 'ADOCONNECTIONSTRING');

NOTE:
- SERVERNAME needs to be the name of the server, or '(local)' for your local machine
- ADOCONNECTIONSTRING needs to be your complete ADO connection string

That works for me!

I even wrote a little function to extract the server name from the connection string as my app keeps the connection string in the registry of the client pc, so it's truly straight-forward.

I found the solution here:

http://support.businessobjects.com/fix/tsreccr.asp

in the "Connecting the RDC to a Data Source" article. Lots of useful stuff here ;o)