Change Crystal Report Connection at Runtime in Visual studio 2005

Introduction

When i started using the crystal reprot i had a all time problem of having a dynamic dataconnection to the crystal reprot.

i was facing problem with crystal reprot when i run that report on to the other machine and the database source is not the same as the machine where the reprots where created.

When you are a professional programmer it is not good to trouble youe customer reagarding the connectionion making which was i had make my customer a ODBC connection and create DNS for my reprots ( earlier). Now when i have found a way to create a dynamic connection for my reprot or have the same connection as of the application

You need to add a Vb form that is Form1.vb

1. To start of with it make a crystal reprot and use the databse. ( i have using ADO connection or OLEDB)

2. Add the crystal reprot to the visual studio

3. Add the crystalreprot viewer on to the Vb form

4. You may use a button any other way to show the reprot ( i have done in form load )

Now You Need to Change Some Code in the form1.vb

Code as Follows : -

Imports System.Data.OleDb

Public Class Form1

    Dim rpt As New CrystalReport1
    Dim rpt1 As New CrystalReport2

    Private Sub Form1_Load(ByVal sender As System.Object, 
            ByVal e As System.EventArgs) Handles MyBase.Load
        Dim path1 As String = My.Application.Info.DirectoryPath '' path
        '' for normal ADO Concept
        '' The current DataSource is an ADO.
        Try
            '' change path of the database
            rpt.DataSourceConnections.Item(0). _
                SetConnection("", "" & path1 & "\Xtreme.mdb", False)
            '' if password is given then give the password
            '' if not give it will ask at runtime
            rpt.DataSourceConnections.Item(0).SetLogon("admin", "admin")
            cr.ReportSource = rpt '' assign the report to the crytal reprot viewr (cr)
        Catch exp As CrystalDecisions.ReportSource.EnterpriseLogonException
            MsgBox(exp.Message)
        End Try

 
''%$^%$^$%^$%^$%^$%^$^$^%$^^$%^$^$%^$%^$%^%$^%$^%$^%$^%$^%$^$^$

 
        '' for ADO.Net Concept Using DataSets
        '' OLEDB Connection
        Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=|DataDirectory|\xtreme.mdb;" & _
                "Persist Security Info=True;" & _
                "Jet OLEDB:Database Password=admin")
        '' SQL Command 
        '' This command should same as it is in the crystal report file so u just need to copy the
        '' code from there
        '' Option In crystal Report is Show SQL Query
        Dim com As New OleDbCommand("SELECT `Customer`.`Customer Name`, `Customer`.`Postal Code`, " & _
                "`Employee`.`FirstName`, `Orders`.`Order Amount`, `Orders`.`Order Date` " & _
                "FROM (`Employee` `Employee` " & _
                "INNER JOIN `Orders` `Orders` ON " & _
                "`Employee`.`EmployeeID`=`Orders`.`Employee ID`)" & _
                "INNER JOIN `Customer` `Customer` ON " & _
                " `Orders`.`Customer ID`=`Customer`.`Customer ID`")
        com.CommandType = CommandType.Text '' command type
        com.Connection = con '' give connection to command
        Dim adp As New OleDbDataAdapter '' declare adapter
        adp.SelectCommand = com '' select command for adpapter to work on
        Dim ds As New DataSet '' delcare dataset
        adp.Fill(ds, "Crystal") '' fill the dataset through adapter
        Try
            '' change path of the database for the reprot
            '' if not change it will take the old path (if that path exists)
            rpt1.DataSourceConnections.Item(0).SetConnection("", "" & path1 & "\Xtreme.mdb", False)
            '' if password is given then give the password
            '' if not give it will ask at runtime
            rpt1.DataSourceConnections.Item(0).SetLogon("admin", "admin")
        Catch exp As Exception
            MsgBox(exp.Message)
        End Try
        rpt1.SetDataSource(ds) '' select the Dataset to the source of the report
        cr1.ReportSource = rpt1 '' assign the report to the crytal reprot viewr (cr1)
    End Sub
End Class