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
