Setting Crystal Report data source at runtime

Question

When creating my Crystal Report I obviously set up a database and server connection that I use for development.

What I want to do now in my VB application is to dynamically set the database and server name to use with the reports. I have these values as the strings varServer and varDatabase.

Anyone know how to go about doing this?

Thanks in advance.

P.S I have tried several online solutions but am having trouble with VB6.

Answer 1

This link has all information you want to know.

UPDATE: Here is a minimum working sample for integrated authentication with SQL Server. You should use the ConnectionProperties of the table object to set connection parameters.

Dim app As New CRAXDDRT.Application
Dim rpt As CRAXDDRT.Report
Dim tbl As CRAXDDRT.DatabaseTable
Dim tbls As CRAXDDRT.DatabaseTables

Set rpt = app.OpenReport("C:\report\repotest.rpt")

For Each tbl In rpt.Database.Tables
    tbl.ConnectionProperties.DeleteAll
    tbl.ConnectionProperties.Add "Provider", "SQLOLEDB"
    tbl.ConnectionProperties.Add "Data Source", "localhost"
    tbl.ConnectionProperties.Add "Initial Catalog", "testdb"
    tbl.ConnectionProperties.Add "Integrated Security", "True"   ' cut for sql authentication
    'tbl.ConnectionProperties.Add "User Id", "myuser"   ' add for sql authentication
    'tbl.ConnectionProperties.Add "Password", "mypass"  ' add for sql authentication
Next tbl

'This removes the schema from the Database Table's Location property.
Set tbls = rpt.Database.Tables
For Each tbl In tbls
    With tbl
        .Location = .Name
    End With
Next

'View the report
Viewer.ReportSource = rpt
Viewer.ViewReport

Answer 2

Great Work! thanks For the code! I change it a bit though because this part was not working:

'View the report
Viewer.ReportSource = rpt
Viewer.ViewReport

It had an error that could not find object

So I did it like that:

With Me.CRViewer91
    .ReportSource = rpt
    .ViewReport
End With

Answer 3

What version of crystal are you using?

In the .net world, I generally pass the dataset to the report as emoreau says here.

That way, your connection is set from code rather than crystal and can be stored in a global connection property. However, that's .net. I'm thinking that either the version of crystal you have should have similar functionality OR Emoreau may have an example of how to do it in VB6 in the version you're using.

Hope that get's you started.

Answer 4

One can also set the default provider for the report by adding this code bit.

 Dim MyProvider As Object = logOnInfo.ConnectionInfo.LogonProperties.LookupNameValuePair("Provider")
            If IsNothing(MyProvider) = False Then
                MyProvider.Value = "SQLOLEDB"
            End If