Click here to Skip to main content
15,867,862 members
Articles / Programming Languages / SQL
Article

Change Crystal Report Connection at Runtime in Visual studio 2005

Rate me:
Please Sign up or sign in to vote.
4.70/5 (21 votes)
23 Feb 2008CPOL1 min read 265.4K   48   39
This project help you to change ur database at the runtime

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 : -

VB.NET
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Junior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
AnswerWorks like a charm! Pin
Francesco Giossi28-Jul-16 3:18
Francesco Giossi28-Jul-16 3:18 
GeneralThere is little change in the C# code given Pin
dosh_harshal3-Jul-12 21:47
dosh_harshal3-Jul-12 21:47 
SuggestionThanks For your help Pin
apprabhu12-Jul-11 19:35
apprabhu12-Jul-11 19:35 
GeneralPlease help [modified] Pin
muhammadhamin14-Jan-11 17:33
muhammadhamin14-Jan-11 17:33 
GeneralRe: Please help Pin
Imran A Momin14-Jan-11 23:05
Imran A Momin14-Jan-11 23:05 
GeneralRe: Please help Pin
muhammadhamin15-Jan-11 1:30
muhammadhamin15-Jan-11 1:30 
GeneralRe: Please help Pin
Imran A Momin15-Jan-11 3:35
Imran A Momin15-Jan-11 3:35 
GeneralRe: Please help Pin
muhammadhamin15-Jan-11 4:15
muhammadhamin15-Jan-11 4:15 
GeneralRe: Please help Pin
muhammadhamin16-Jan-11 3:09
muhammadhamin16-Jan-11 3:09 
GeneralSuperb Pin
svkmr_sivakumar28-Dec-10 21:39
svkmr_sivakumar28-Dec-10 21:39 
QuestionServer still ask me the password Pin
fapingo11-Nov-10 1:09
fapingo11-Nov-10 1:09 
AnswerRe: Server still ask me the password Pin
Imran A Momin11-Nov-10 3:33
Imran A Momin11-Nov-10 3:33 
GeneralMy vote of 1 Pin
meridyen12-Apr-10 20:25
meridyen12-Apr-10 20:25 
GeneralThanks Pin
adpatel7@gmail.com16-Feb-10 6:56
adpatel7@gmail.com16-Feb-10 6:56 
GeneralThanx Pin
infinitess7-Jan-09 20:58
infinitess7-Jan-09 20:58 
QuestionCrystal Report Datasourse at Client End Pin
Member 385334514-Mar-08 3:11
Member 385334514-Mar-08 3:11 
AnswerRe: Crystal Report Datasourse at Client End Pin
Imran A Momin14-Mar-08 5:36
Imran A Momin14-Mar-08 5:36 
QuestionA Blank Report appears after binding a dataset to the report [modified] Pin
DaRk_AsSaSSin7-Nov-07 16:47
DaRk_AsSaSSin7-Nov-07 16:47 
Generalreportsource is readonly Pin
stdywell30-Sep-07 12:27
stdywell30-Sep-07 12:27 
GeneralRe: reportsource is readonly Pin
Imran A Momin2-Nov-07 23:34
Imran A Momin2-Nov-07 23:34 
GeneralRecord Set Pin
ssmony20-Aug-07 16:19
ssmony20-Aug-07 16:19 
GeneralThank you. It was helpfull ! Pin
George Mamaladze19-Jun-07 20:29
George Mamaladze19-Jun-07 20:29 
GeneralRe: Thank you. It was helpfull ! Pin
Imran A Momin19-Jun-07 21:44
Imran A Momin19-Jun-07 21:44 
QuestionCrystal reports .datasourceconnections.item(0).setlogin( [modified] Pin
i34028-Mar-07 22:24
i34028-Mar-07 22:24 
AnswerRe: Crystal reports .datasourceconnections.item(0).setlogin( Pin
Imran A Momin29-Mar-07 9:02
Imran A Momin29-Mar-07 9:02 
U could try this out... Coz i am using it...
rpt.Datasourceconnections.item(0).setlogon("Admin","")


I guess it should work...

Hope u found it usefull
Smile | :)

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.