Change Crystal Reports Basic 2008 OLE DB Provider at Runtime in VB.NET

[Former Member]

We have been using Crystal Reports 8, 8.5, 9 & 10 RDC CRAXDRT in VB6 for many years to allow us to connect to different database providers at runtime using DatabaseTable.ConnectionProperties. This allows us to write one report that can be pointed to the same database structure on the two database platforms we support: Microsoft Access and Microsoft SQL Server.

We are in the process of upgrading to VB.Net 2008 and would like to migrate our RDC code to .Net so have replaced the CRAXDRT references with the Crystal Reports Basic 2008 ones included in Visual Studio 2008 Professional.

I have now spent at least 2 days searching the web and downloading APIs and Developer Help files etc. and cannot find a way to change the OLE DB Provider at runtime. I have tried using the following code to create new TableLogOnInfo and ConnectionInfo classes (with the Attributes set as they are when manually specified using the Crystal Reports Designer) and use the Table.ApplyLogOnInfo() function to apply them (see code below):

        objReportDocument.Load("ReportFileName.rpt", OpenReportMethod.OpenReportByDefault)  ' ENTER REPORT FILE NAME AND PATH HERE

        objReportDocument.Refresh()

        Dim objLogOnInfo As New CrystalDecisions.Shared.TableLogOnInfo
        Dim objConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
        Dim objDbConnectionAttributes As New DbConnectionAttributes
        Dim objEQ_LogonProperties As New DbConnectionAttributes
        Dim ServerName As String = "ServerName"     ' ENTER SERVER NAME HERE
        Dim DatabaseName As String = "DatabaseName"      ' ENTER DATABASE NAME HERE

        objEQ_LogonProperties.Collection.Add(New NameValuePair2("Data Source", ServerName))
        objEQ_LogonProperties.Collection.Add(New NameValuePair2("Initial Catalog", DatabaseName))
        objEQ_LogonProperties.Collection.Add(New NameValuePair2("Integrated Secruity", DbConnectionAttributes.LOGONPROP_INTEGRATED_SECURITY_SSPI))
        objEQ_LogonProperties.Collection.Add(New NameValuePair2("Provider", "SQLOLEDB"))

        objDbConnectionAttributes.Collection.Add(New NameValuePair2(DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ADO))
        objDbConnectionAttributes.Collection.Add(New NameValuePair2(DbConnectionAttributes.QE_DATABASE_NAME, DatabaseName))
        objDbConnectionAttributes.Collection.Add(New NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"))
        objDbConnectionAttributes.Collection.Add(New NameValuePair2(DbConnectionAttributes.QE_LOGON_PROPERTIES, objEQ_LogonProperties))
        objDbConnectionAttributes.Collection.Add(New NameValuePair2(DbConnectionAttributes.QE_SERVER_DESCRIPTION, ServerName))
        objDbConnectionAttributes.Collection.Add(New NameValuePair2("QE_SQLDB", "true"))
        objDbConnectionAttributes.Collection.Add(New NameValuePair2(DbConnectionAttributes.CONNINFO_SSO_ENABLED, "false"))

        objConnectionInfo.Type = CrystalDecisions.Shared.ConnectionInfoType.CRQE
        objConnectionInfo.AllowCustomConnection = True
        objConnectionInfo.Attributes = objDbConnectionAttributes
        objConnectionInfo.ServerName = ServerName
        objConnectionInfo.DatabaseName = DatabaseName
        objConnectionInfo.IntegratedSecurity = True
        objConnectionInfo.UserID = ""
        objConnectionInfo.Password = ""

        objLogOnInfo.ConnectionInfo = objConnectionInfo

        For Each objTable As CrystalDecisions.CrystalReports.Engine.Table In objReportDocument.Database.Tables
            objTable.ApplyLogOnInfo(objLogOnInfo)
            objTable.Location = objTable.Location    ' THIS IS WHERE IT FAILS
        Next

        For Each objSubReport As CrystalDecisions.CrystalReports.Engine.ReportDocument In objReportDocument.Subreports
            For Each objTable As CrystalDecisions.CrystalReports.Engine.Table In objSubReport.Database.Tables
                objTable.ApplyLogOnInfo(objLogOnInfo)
                objTable.Location = objTable.Location
            Next
        Next

        CrystalReportViewer1.ReportSource = objReportDocument
        CrystalReportViewer1.RefreshReport()

However, as soon as Table.Location = Table.Location is called to refresh the connection details it always comes back with the same "Unable to connect: incorrect log on parameters" error:

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2147217393
  Message="Logon failed. Details: ADO Error Code: 0x Source: Microsoft OLE DB Service Components Description: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Error in File C:\Users\GJT.TMS\AppData\Local\Temp\CISCertificatesBatch {260C6157-2AB4-49CE-B6DD-D76E969F9244}.rpt: Unable to connect: incorrect log on parameters."
  Source="RptControllers.dll"
  StackTrace:
       at CrystalDecisions.ReportAppServer.Controllers.DatabaseControllerClass.SetTableLocation(ISCRTable CurTable, ISCRTable NewTable)
       at CrystalDecisions.CrystalReports.Engine.Table.set_Location(String value)
       at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e) in C:\Users\GJT.TMS\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb:line 110
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.Form.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
       at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
       at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
       at System.Windows.Forms.Control.set_Visible(Boolean value)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

I have also tried to change the ReportDocument.DataSourceConnections collection but got nowhere there either.

Can someone please let me know how to do this please?

(It would be ideal if you could just pass in an OLE DB connection string but things are never that easy...)

We are planning to get a copy of Crystal Reports 2008 Developer once we have evaluated the .Net migration process so if it is not possible with the version included with Visual Studio 2008 but will work with the Developer edition then let me know and I will download a trial to confirm it works.

I have also seen a lot of references to the "Report Application Server API" in the ReportAppServer namespace and the ReportDocument.ReportClientDocument. We do not actually want to use any "report server" features (Client Server databases with thick client reporting is all our clients require) so if this object model will provide this ability without the need of an actual "report server" it would be good to know.

On a side note, it is also annoying that according to the "Crystal Reports XI Release 2, Migrating Applications from the RDC to the .NET assemblies" document, there is now no way to access the old RDC PrintingStatus.Progress setting without the Report Application Server API.

Thanks in advance,

Gareth

[former_member184995 · Accepted solution]

You will need to use RAS to do both of the things you are wanting (though I dont know about the RDC PrintingStatus.Progress setting equivalent in RAS).

You can use RAS Inproc (ie RAS without a RAS server) if you have CR XI R2 SP2 or later (that includes CR 2008, which is what you will need if you are using VS.NET 2008).

I found this code to change database type to OLEDB using RAS, which may be helpful:

'Get all tables in the report
            Dim myTables As Tables = reportClientDoc.DatabaseController.Database.Tables

            'Loop through each table in the tables collection
            For Each myTable As Table In myTables
                'Save the old table
                Dim oldTable As Table = myTable
                'Set the new qualified name of the table
                myTable.QualifiedName = "Northwind.dbo." + myTable.Name
                Dim innerPropBag As New PropertyBag
                'Setup the Logon properties that we will be using
                innerPropBag.Add("Data Source", "vancsdb02")
                innerPropBag.Add("Initial Catalog", "Northwind")
                innerPropBag.Add("Provider", "SQLOLEDB")

                Dim myPropBag As New PropertyBag
                'Setup all other database properties we will be using
                'Set the database dll that we will use
                myPropBag.Add("Database DLL", "crdb_ado.dll")
                'Set the server name
                myPropBag.Add("QE_ServerDescription", "vancsdb02")
                'Set the connection type
                myPropBag.Add("QE_DatabaseType", "OLE DB (ADO)")
                'Pass in the Logon properties we created earlier
                myPropBag.Add("QE_LogonProperties", innerPropBag)
                'The database is a SQLDB
                myPropBag.Add("QE_SQLDB", True)
                'We are not using single-sign on
                myPropBag.Add("SSO_Enabled", False)

                Dim myconn As New ConnectionInfo
                'Pass the database properties to a connection info object
                myconn.Attributes = myPropBag
                'Set the connection kind to CRQE
                myconn.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
                'Set the user name and password
                myconn.UserName = "devtech"
                myconn.Password = "devtech"

                'Pass the connection information to the table
                myTable.ConnectionInfo = myconn

                'Set the table in the report to use the modified table
                reportClientDoc.DatabaseController.SetTableLocation(oldTable, myTable)
            Next

Best Regards,

Jason

[former_member11696 · Accepted solution]

Hi Gareth,

The problem with your code is you must set the user name and password, you have a variable to do so but then set them to empty strings:

objConnectionInfo.UserID = ""

objConnectionInfo.Password = ""

If you are using Trusted Authentication then it must also be set when designing the reports.

The problem with the progress indicator is because of WEB applications, no way to send the update back to the viewer and update a bar in a web browser, you would have to hit the refresh button to update the bar. So they did not impliment the same functionality the RDC had for the new viewer. There is a work around for a WEB app in 2008 but not for a Windows app. We have asked that R&D put this functionality back in but not sure what the status is of our request is.

As Jason suggested, RAS it the replacement for RDC and will have the same capability. It's close to the same now and also has the RCAPI functionality RDC has.

Go to our sample download site for more info on RAS and how to's:

https://www.sdn.sap.com/irj/boc/

Select the Business Objects Tab and then Code and Samples.

Thank you

Don