Changing ODBC in CrystalReportViewer at runtime 7

[LupoSolitario]

Hi!
I have a problem that is confusing me.
I have installed CR9 and RAS, to show report in internet.
All I wanna do is changing database at runtime for an existing report.
I have tried two ways:
1) assigning a ConnectioInfo to .DatabaseLogonInfos of the CrystalReportViewer; don't work, the report show old data and mantain old connection info(QE_ServerDescription,QE_LogonProperties and so on are not changed)
2) open a ReportClientDocument that take the report, get the tables, modify connection info for each table (using SetTableLocation of DatabaseController as see in the Report Application SServer SDK, or using ModifyTableConnectionInfo of DatabaseController or using Table.SetLogonInfo), then assign ReportClientDocument.ReportSource to HtmlViewer.ReportSource: a little forward step, because HtmlViewer now show NEW connection info, but again show old values.
Of course, I uncheck Save Data With Report and Discard Saved Data When Loading Report.
Can someone please help me ?
Thanks.

 

[cjlarue]

Take a look at

http://support.crystaldecisions.com/library/kbase/articles/c2011464.asp

Regards
Chuck LaRue
ADRS Computer Services

 

[LupoSolitario]

Great !!
Now it work fine.
Thank you very much.
Marco Negri

 

[ladylinet]

This was for .NET - how that helped you? Can you be kind and send working (asp) example?

Thanks.
Lady Linet

 

[LupoSolitario]

Hi!
Here is the code; work for mine.
If it doesn't work, check ALL the values of the properties Attributes.Item("propIdentifier" "propIdentifier" you find in the code are correct, because I check them all
via PropertyIds.
Hope that help (a little bit more of Seagate help ...)

Function ChangeRuntimeDB(ByVal strPath )
'************************************************************************************************
' Use of this code is free; only leave the information below (we all need to eat...)
' AUTHOR: Marco Negri
' CREATED: 18-jul-2003
' COPYRIGHT: Copyright 2003 Marco Negri
' EMAIL: marco@alteasrl.com
' USAGE: Call this function before setting viewer properties; then assign
' htmlViewer.ReportSource = Session("ConInfos&quot.ReportSource
'************************************************************************************************
Dim ReportDoc, objFactory, rptAppSession,ConInfos
Dim reportname, newSqlServerDescription
Dim newUSR, newPWD, newDSN, newDBQ,newConInfos
Dim rasTables, rasTable,oldConnectionInfo,newConnectionAttributes,newConnectionInfo,ConnectionProp,newTable

'Define the dsn and odbc login information to override the connection properties within the report.
newDSN = "myDsnName"
newDBQ = "myDbName"
newUSR = "myUserName"
newPWD = "myPwd"
newSqlServerDescription = "mySqlServerDescription "
'Use the OjectFactory object to abstract the version number
'to one location
Set objFactory = CreateObject("CrystalReports.ObjectFactory.2&quot
Set rptAppSession = objFactory.CreateObject("CrystalReports.ReportAppSession&quot
Set ConInfos = objFactory.CreateObject("CrystalReports.ConnectionInfos&quot
rptAppSession.Initialize
'Create a new ReportClientDocument object for this reportAppSession
Set ReportDoc = rptAppSession.CreateService("CrystalClientDoc.ReportClientDocument&quot
ReportDoc.Open strPath
Set rasTables = ReportDoc.Database.Tables
' code until here is needed to get the report to modify
For Each rasTable In rasTables
' connectioInfo must be provided for each table
Set newConnectionAttributes = Server.CreateObject("CrystalReports.PropertyBag&quot
set newTable = Server.CreateObject("CrystalReports.Table&quot
'£££££ SQL
' SQL is a bit more difficult
newConnectionAttributes.EnsureCapacity 5
newConnectionAttributes.item("QE_DatabaseType&quot = "OLE DB (ADO)"
newConnectionAttributes.item("Database DLL&quot = "crdb_ado.dll"
newConnectionAttributes.item("QE_ServerDescription&quot = newSqlServerDescription
newConnectionAttributes.item("QE_SQLDB&quot = true
' first four item are a single prop, the fifth is an object
' containing 10 single properties
Set ConnectionProp = Server.CreateObject("CrystalReports.PropertyBag&quot
ConnectionProp.EnsureCapacity 10
ConnectionProp.item("Connect Timeout&quot = "15"
' is the name of DBServer; I only try with the same server
ConnectionProp.item("Data Source&quot = newSqlServerDescription
ConnectionProp.item("General Timeout&quot = "15"
ConnectionProp.item("Initial Catalog&quot = newDBQ
ConnectionProp.item("Integrated Security&quot = False
ConnectionProp.item("Locale Identifier&quot = "1040"
ConnectionProp.item("OLE DB Services&quot = "-5"
ConnectionProp.item("Provider&quot = "SQLOLEDB"
ConnectionProp.item("Tag with column collation when possible&quot = "0"
ConnectionProp.item("Use Encryption for Data&quot = "0"
newConnectionAttributes.item("QE_logonProperties&quot = ConnectionProp
' ok, now new table contain the right ConnectionAttributes
'£££££££££

'£££££ ACCESS
' UNCOMMENT this section (and comment the SQL section) to set ACCESS logon
'newConnectionAttributes.EnsureCapacity 5
'newConnectionAttributes.item("QE_DatabaseType&quot = "ODBC(RDO)"
'newConnectionAttributes.item("Database DLL&quot = "Crdb_odbc.Dll"
'newConnectionAttributes.item("QE_ServerDescription&quot = newDBQ
'newConnectionAttributes.item("QE_SQLDB&quot = true
'Set ConnectionProp = Server.CreateObject("CrystalReports.PropertyBag&quot '= newDSN
'ConnectionProp.EnsureCapacity 2
'ConnectionProp.item("DSN&quot = newDSN
'ConnectionProp.item("DATABASE&quot = "newDbPath"
'newConnectionAttributes.item("QE_logonProperties&quot = ConnectionProp
'£££££££££

Set newConnectionInfo = Server.CreateObject("CrystalReports.ConnectionInfo&quot
' ALL properties must be properly set; if not, there are errors
newConnectionInfo.Attributes = newConnectionAttributes
newConnectionInfo.UserName = newUSR
newConnectionInfo.Password = newPWD
newConnectionInfo.Kind = 5'oldConnectionInfo.Kind
Set newTable = rasTable.Clone( true)
newTable.ConnectionInfo = newConnectionInfo
' The function underneath simply replace the old qualified name
' (see

http://support.crystaldecisions.com/library/kbase/articles/c2011464.asp)

newTable.QualifiedName = NewTableQualifiedName(rasTable,"newDbName&quot
ReportDoc.DatabaseController.SetTableLocation rasTable,newTable
Set newConnectionAttributes = nothing
Set newConnectionInfo = nothing
Next
Set Session("ConInfos&quot = ReportDoc
End Function

Hope indentation look better in your editor
Bye

 

[LupoSolitario]

Oh, I forget:
I have NOT tested it for report with subreport!
Perhaps later...
Bye

 

[ladylinet]

THANK YOU!
Finally working Problem was that _all_ properties has to be filled up, otherwise memory is not allocated properly.
"As a token of my gratitude" (ever played Diablo?), I will send you part for subreports
I took out allocation and dealocation of ConnectionInfo objects outside of the loop, so that I can reuse it for subreports - what do you think?
Happy Programming!

Lady Linet
'----------------------------------
' Subreports
'----------------------------------
Dim newDbOwner
Dim SubReportName
newDbOwner = "owner" 'full table name is dbname.ownername.tablename; this is to replace ownername
For Each Subreportname In ReportDoc.Subreportcontroller.Querysubreportnames
Set rasTables = ReportDoc.Subreportcontroller.Getsubreportdatabase(Subreportname).Tables
For Each rasTable In rasTables
Set newTable = Server.CreateObject("CrystalReports.Table&quot
Set newTable = rasTable.Clone( true)
newTable.ConnectionInfo = newConnectionInfo
newTable.QualifiedName = newDBQ & "." & newDbOwner & "." & newTable.Name
rcDoc.SubreportController.SetTableLocation SubReportName, rasTable, newTable
Set newTable = Nothing
Next
Next

 

[LupoSolitario]

Hello!

Thank you very much for the code; I modify my function to insert your code and it work well ( and save me a lot of time...).
I'm very glad to see that someone know the meaning of
"Gratitude" ).

Bye