Crystal report create connection to OLE DB database

Question

I am working with VS 2005 and using visual basic for coding.

How do I set up the connection object for my crystal report using vb code.

I have written some code

Dim strcon As String = ConfigurationManager.AppSettings("PhdConnectionString")
Dim getconn As SqlConnection = New SqlConnection(strcon)

Dim rpt As ReportDocument = New ReportDocument

        rpt.Load(Server.MapPath("aspirantCrystalReport.rpt"))

// I want to set the connection properties here. How do I do that ?

        CrystalReportViewer1.ReportSource = rpt
        CrystalReportViewer1.DataBind()

Answer 1

I have not done this using the Crystal Reports .NET API, but I do have a working piece of code written in VB6 that calls the COM API. The API class and member names can't be that different.

Private Sub SetDataConnections(ByVal oReport As CRAXDRT.Report, ByVal oConnection As ADODB.Connection)

    ' Do all tables in this report.
    Dim oTable As CRAXDRT.DatabaseTable
    For Each oTable In oReport.Database.Tables
        SetDataConnection oTable, oConnection
    Next

    ' Find all subreports and do them too.
    Dim oSection As CRAXDRT.Section
    For Each oSection In oReport.Sections
        Dim oObject As Object
        For Each oObject In oSection.ReportObjects
            If TypeOf oObject Is CRAXDRT.SubreportObject Then
                Dim oSubreportObject As CRAXDRT.SubreportObject
                Set oSubreportObject = oObject
                SetDataConnections oSubreportObject.OpenSubreport()
                Set oSubreportObject = Nothing
            End If
        Next
    Next

End Sub

Private Sub SetDataConnection(ByVal oTable As CRAXDRT.DatabaseTable, ByVal oConnection As ADODB.Connection)

    ' Extract the relevant data from the ADO connection.
    Dim sServer As String
    Dim sDatabase As String
    Dim bTrusted As String
    Dim sUserName As String
    Dim sPassword As String
    Dim nTimeout As Long
    With oConnection.Properties
        sServer = .Item("Data Source").Value
        sDatabase = .Item("Initial Catalog").Value
        Select Case UCase(.Item("Integrated Security").Value)
        Case "SSPI", "YES"
            bTrusted = True
        Case Else ' "NO", ""
            bTrusted = False
        End Select
        sUserName = .Item("User ID").Value
        sPassword = .Item("Password").Value
    End With
    nTimeout = oConnection.CommandTimeout

    ' Delete and re-create all connection information. This is the only way of getting it
    ' to work if the report contains subreports. Changing database drivers on the fly is
    ' not allowed, so we must re-create the connection using settings appropriate for the
    ' particular driver involved.
    Select Case oTable.DllName
    Case "crdb_ado.dll"
        With oTable.ConnectionProperties
            .DeleteAll
            .Add "Database Type", "OLE DB (ADO)"
            .Add "Provider", "SQLOLEDB"
            .Add "Data Source", sServer
            .Add "Initial Catalog", sDatabase
            .Add "Integrated Security", bTrusted
            .Add "User ID", sUserName
            .Add "Password", sPassword
            .Add "OLE DB Services", -1
            .Add "General Timeout", nTimeout
        End With
    Case Else
        ' TODO: Handle other drivers appropriately.
    End Select

End Sub