Override the design time connection settings of crystal reports

[TysonLPrice]

I’m converting some Crystal reporting functions from VB 6.0 to VB .Net 2008, have reports up and running, and I’m not quite sure how to approach one aspect. The issue I have is most of the Crystal reports connect to a database and run a stored procedure. The connection properties are set up in the report usually pointing at the development region. At run time I need to be pointing to either development, user acceptance, or production. I know what environment I’m in from a configuration file but don’t know how to go about telling Crystal what environment and stored procedure to point at. In VB 6.0 it was:

 CR1.Connect = "DSN=" & A.ReportServer & ";<<Use Integrated Security>>;DSQ=" & A.ReportManhattanDB

Where A.ReportServer and A.ReportManhattanDB contain the connection poperties. The stored procedure is passed via:

 cr1.DataFiles(0) = A.ReportManhattanDB & ".dbo.Proc(rptSI40SR;1)

Where rptSI40SR is the stored procedure. I’ve been Googling and most of the solutions seem to be working reports in Visual Studio with the Crystal and SP interfaces in the project. The reports I am working with are already developed and are in Crystal 8.5.
I’m hoping someone has already gone down this path or can point me in the right direction. This is what I have now but don’t know how to connect

 myConnection = New SqlConnection("Data Source=devsql\tpa1;Integrated Security=SSPI;" & _
                                             "Initial Catalog=Manhattan;")
MyCommand.Connection = myConnection
CryRepControl1.ReportFileName = "C:\CrystalReports\pkclaim.rpt"
CryRepControl1.ParameterFields(0) = "@pkEmployerAddress;3356342;true"
RptViewer1.ParameterFieldInfo = CryRepControl1.ParamFields

‘Need to override what’s  in the Crystal report

‘If multiple parameters the are strung here and fed to crystal
RptViewer1.ReportSource = CryRepControl1.CrySet()

 

[TysonLPrice]

This works for a basic Crystal reports so I'm posting it but will not work if there are sub reports also expecting stored procedure input. I'm still stuck at this point and this approach may go down the tubes. Still looking for help

1. Create a Windows form.
2. Paint on a button and the Crystal reports viewer.

Imports System.Data.SqlClient
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strConnection As String = "Data Source=YourServer;Integrated Security=SSPI;" & _
                                             "Initial Catalog=YourDatabase;"

        Dim Connection As New SqlConnection(strConnection)
        Dim strSQL As String = "Exec YourStoredProcedure"
        Dim DA As New SqlDataAdapter(strSQL, Connection)
        Dim DS As New DataSet
        Dim strReportName As String = ""

        DA.SelectCommand.CommandTimeout = 0

        DA.Fill(DS)

        Dim strReportPath As String = "C:\YourReport.rpt”

        If Not IO.File.Exists(strReportPath) Then

        Dim rptDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument

        rptDocument.Load(strReportPath)
        rptDocument.SetDataSource(DS.Tables(0))
        rptViewer.ShowRefreshButton = False
        rptViewer.ShowCloseButton = False
        rptViewer.ShowGroupTreeButton = False
        rptViewer.ReportSource = rptDocument
End Sub
End Class

 

[TysonLPrice]

This seems to work fine:

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class Form5

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cryRpt As New ReportDocument
Dim crtableLogoninfos As New TableLogOnInfos
Dim crtableLogoninfo As New TableLogOnInfo
Dim crConnectionInfo As New ConnectionInfo
Dim CrTables As Tables
Dim CrTable As Table

cryRpt.Load("c:\crystalreports\pkclaimiiSP.rpt")

Dim pvCollection As New CrystalDecisions.Shared.ParameterValues()
Dim pdvCustomerName As New CrystalDecisions.Shared.ParameterDiscreteValue()

With crConnectionInfo
.ServerName = "devsql\mco1"
.DatabaseName = "manhattan"
.UserID = ""
.Password = ""
.IntegratedSecurity = True
End With

'' Set the discreet value to the customers name.
pdvCustomerName.Value = "3356342"

'' Add it to the parameter collection.
pvCollection.Add(pdvCustomerName)

'' Apply the current parameter values.
cryRpt.DataDefinition.ParameterFields("@fkemployeraddress").ApplyCurrentValues(pvCollection)

CrTables = cryRpt.Database.Tables
For Each CrTable In CrTables
crtableLogoninfo = CrTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
CrTable.ApplyLogOnInfo(crtableLogoninfo)
Next

CrystalReportViewer1.ReportSource = cryRpt
CrystalReportViewer1.Refresh()


End Sub
End Class