?
Solved

Can I programmatically change a crystal report with datasource ODBC (RDO) to OLE DB (ADO)?

Posted on 2014-08-18
5
Medium Priority
?
1,220 Views
Last Modified: 2015-02-08
Hi,
My question is kind of like this pretty much unanswered one on stack overflow: http://stackoverflow.com/questions/1048768/how-to-change-the-datasource-type-on-a-crystal-reports-file.

I have about 400 reports that needed to have their datasource changed. Fortunately, I had a little program that did the bulk of them. But I wanted to change them all to ole db (ado), and those that were originally odbc rdo changed the server, database, and username, but didn't change the connection type from rdo to ado. Or,if it did, it didn't save the report.  So is this possible to do programmatically? My vb.net code is below: Using crystal version xi release 2, 11.5.0.313.

 
        'create a reportdocument and load a reportfilename into it; the filenames are in a listbox 
        'populated(from) 'reportsOriginal' folder.
        'When done, Copy (using saveas method) to 'reportsFinished' folder.
        'But how do we Programmatically change the datasource from ODBC (RDO) to OLE DB (ADO)?
        'We Can determine if it is RDO, but cannot figure out how to change it.
        Dim ciReportConnection As New ConnectionInfo
        Dim rptServerName = ""
        Dim fname As String = ""
        Dim OutPutDir As String = ""
        Dim knt As Integer = 0
        Const cstAdo As String = "OLE DB (ADO)"
        
        'populate using controls on the form
        With ciReportConnection
            'First attempt to change the report to ADO
            'This causes no error in the report but doesn't work
             .AllowCustomConnection = True
            'First Attempt
            'If the report was  RDO, this causes the report to have no datasource. but causes no error if the report was ADO anyway.
            ''.Attributes.Collection.Add(New NameValuePair2("QE_DatabaseType", cstAdo))
            'second attempt-worthless too
            '.LogonProperties.Add(New NameValuePair2("QE_DatabaseType", cstAdo))
            .Type = ConnectionInfoType.CRQE
            .ServerName = cboServer.Text
            .DatabaseName = txtDatabase.Text
            .UserID = txtuserId.Text
            'note The only way to save a password in a report anyway is to use an odbc connection with a saved password.
            ' But just for fun include password. It is best to use integrated security if possible.
            .Password = txtPassword.Text
            .IntegratedSecurity = chkIntegrated.Checked
            
            End With
        Try
            For Each item As String In lstReports.SelectedItems
                Dim str() As String
                str = Split(item, "\")
                fname = str(UBound(str))

                Dim crReportDocument As New ReportDocument
                crReportDocument.Load(item)
                'another attempt to set everything to ADO
                ciReportConnection.LogonProperties.Add(New NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"))
                If crReportDocument.DataSourceConnections.Count > 0 Then
                    rptServerName = crReportDocument.DataSourceConnections(0).ServerName
                    'this at least alerts us if the report is RDO
                    MessageBox.Show(GetRdoReportName(crReportDocument, fname))
                End If

                rptServerName = UCase(rptServerName)
                lblReportCopy.Text = "Currently Working on " & fname
                'Assign data source details to tables if original file servername doesn't match the desired value
                If rptServerName <> "" And rptServerName <> UCase(cboServer.Text) Then
                    ' (copied from stackoverflow) The meat of the program is right here; this is what changes the logon info
                    For Each table As Table In crReportDocument.Database.Tables
                        table.LogOnInfo.ConnectionInfo = ciReportConnection
                        table.ApplyLogOnInfo(table.LogOnInfo)
                    Next
                    For Each subrep As ReportDocument In crReportDocument.Subreports
                        For Each table As Table In subrep.Database.Tables
                            table.LogOnInfo.ConnectionInfo = ciReportConnection
                            table.ApplyLogOnInfo(table.LogOnInfo)
                        Next
                    Next
                End If

                crReportDocument.Refresh()
                'This shows a logon error for any originally (and unchanged) RDO report, but no error for an originally ADO report.
                'crReportDocument.VerifyDatabase()
                crReportDocument.Refresh()
                OutPutDir = Application.StartupPath + "\ReportsFinished\"
                'all 'ADO' changes seem to be lost when saved. Maybe they work when using a crystal viewer
                crReportDocument.SaveAs(OutPutDir + fname)
                knt += 1
            Next
        Catch ex As Exception
            lstErrors.Items.Add(ex.Message)
        End Try
        MessageBox.Show(knt & " New files created in " & OutPutDir)
    End Sub
    Private Function GetRdoReportName(ByRef CrReportDocument As ReportDocument, ByVal fname As String) As String
        Const cstAdo As String = "OLE DB (ADO)"
        Dim test As ArrayList = CrReportDocument.DataSourceConnections(0).Attributes.Collection
        Dim i As Integer
        Dim Substr As String
        For i = 0 To test.Count - 1
            If UCase(test(i).name) = "QE_DATABASETYPE" Then
                Substr = (test(i).value)
                If InStr(UCase(Substr), "RDO") > 0 Then
                    'Third attempt to change the report to ADO
                    'code below causes no error but does not change the report to ado..
                    test(i).value = cstado
                    Return fname
                End If
            End If
        Next
        Return ""
    End Function

Open in new window

0
Comment
Question by:OutOnALimbAlways
2 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 40269421
According to this question from the SAP knowledge base you have to use the  InProc RAS SDK.

https://scn.sap.com/thread/3184424

They also recommend looking at a blog on the same subject
When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET
http://scn.sap.com/community/crystal-reports-for-visual-studio/blog/2011/09/02/when-to-use-the-replace-connection-method-using-the-crystal-reports-or-inproc-ras-sdk-for-net

Another article worth reading is the white paper from SAP on Troubleshooting Database Connection Problems
http://scn.sap.com/docs/DOC-21985

mlmcc
0
 

Author Comment

by:OutOnALimbAlways
ID: 40270325
Thanks mlm, at least that gives me a start.  Fortunately it's not critical [yet] so I do have some time to play around with it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question