Change Crystal Report Connection at Runtime in Visual studio 2005

17:33 14 Jan '11

Not workin for me, bellow is the code the changes i made, in crystal report i am using OLEDB(ADO) Microsoft.Jet.OLEDB.4.0 MS Access .mdb, there is no password..
http://img43.imageshack.us/img43/75/40042195.png

it works on the system but when i transfer it to a new system, it checks the wrong database location,(the DB location in my PC) ..

Imports System.Data.OleDb
 
Public Class aging_report
    Dim rpt1 As New CrystalReport6
    Dim path1 As String = My.Application.Info.DirectoryPath '' path

    Private Sub aging_report_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Me.WindowState = FormWindowState.Maximized
        Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=|DataDirectory|\MOMDB.mdb;" & _
         "Persist Security Info=True;" & _
         "Jet OLEDB:Database Password=")
        Dim com As New OleDbCommand("SELECT `unpaid_trans`.`PatTranCode`, `unpaid_trans`.`PatCode`, `unpaid_trans`.`PatSurname`, `unpaid_trans`.`Total`, `unpaid_trans`.`TranDate` " & _
        "FROM   `unpaid_trans` `unpaid_trans`")
        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
            rpt1.DataSourceConnections.Item(0).SetConnection("", "" & path1 & "\MOMDB.mdb", False)
            rpt1.DataSourceConnections.Item(0).SetLogon("Admin", "")
        Catch exp As Exception
            MsgBox(exp.Message)
        End Try
        rpt1.SetDataSource(ds) '' select the Dataset to the source of the report
        CrystalReportViewer1.ReportSource = rpt1 '' assign the report to the crytal reprot viewr (cr1)

modified on Friday, January 14, 2011 11:47 PM

23:05 14 Jan '11

Hello, You have a wrong connection String. If you dont have password then dont use the attribute of password " Jet OLEDB:Database Password=admin " for more help on connection string check this website http://www.connectionstrings.com/[^] Imran A Momin Co.Software Developer
Sign In·View Thread·Permalink

1:30 15 Jan '11

I changed, still on other system, the report looks the wrong data-source location .

Imports System.Data.OleDb
Public Class aging_report
    Dim rpt1 As New CrystalReport6
    Dim path1 As String = My.Application.Info.DirectoryPath '' path

    Private Sub aging_report_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Me.WindowState = FormWindowState.Maximized
        Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=|DataDirectory|\MOMDB.mdb;" & _
        "Persist Security Info=False;")
        Dim com As New OleDbCommand("SELECT `unpaid_trans`.`PatTranCode`, `unpaid_trans`.`PatCode`, `unpaid_trans`.`PatSurname`, `unpaid_trans`.`Total`, `unpaid_trans`.`TranDate` " & _
        "FROM   `unpaid_trans` `unpaid_trans`")
        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
            rpt1.DataSourceConnections.Item(0).SetConnection("", "" & path1 & "\MOMDB.mdb", False)
            rpt1.DataSourceConnections.Item(0).SetLogon("", "")
        Catch exp As Exception
            MsgBox(exp.Message)
        End Try
        rpt1.SetDataSource(ds) '' select the Dataset to the source of the report
        CrystalReportViewer1.ReportSource = rpt1 '' assign the report to the crytal reprot viewr (cr1)
    End Sub

3:35 15 Jan '11

then set logon method rpt1.DataSourceConnections.Item(0).SetLogon("", "") is incorrect. u need to add the username to the method Imran A Momin Co.Software Developer
Sign In·View Thread·Permalink

4:15 15 Jan '11

i tried with Admin as the username, since that is the default one, no luck thats why i removed it, just checked again, no good.. any ideas.. rpt1.DataSourceConnections.Item(0).SetLogon("Admin", "")
Sign In·View Thread·Permalink

Server still ask me the password

3:09 16 Jan '11

ideas ??/
Sign In·View Thread·Permalink

Superb

svkmr_sivakumar

21:39 28 Dec '10

hey man, it's really helped me.... keep it up Thanks & Regards S. Sivakumar
Sign In·View Thread·Permalink

fapingo

1:09 11 Nov '10

Hello, I've followed your code but at the end the server still ask me the password, could you tell me why.

My code is:

private void Form1_Load(object sender, EventArgs e)
        {
            CrystalReport2  rpt =   new CrystalReport2();
            string  connectionstring    =   GetConnectionString();
            SqlConnection   con         =   new SqlConnection(connectionstring);
            SqlCommand      comm        =   new SqlCommand(GetQuery());
            comm.CommandType            =   CommandType.Text;
            comm.Connection             =   con;
            SqlDataAdapter  adp         =   new SqlDataAdapter();
            adp.SelectCommand           =   comm;
            DataSet         ds          =   new DataSet();
            adp.Fill(ds,    "Crystal");
            try
            {
                rpt.DataSourceConnections[0].SetConnection("Localhost",    "Test", false);
                rpt.DataSourceConnections[0].SetLogon("testUser",    "testUser");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
 
            rpt.SetDataSource(ds);
            crystalReportViewer1.ReportSource   =   rpt;
 
        }
 
        static private string GetConnectionString()
        {
            return "Data Source=Localhost; Initial Catalog= Test; User Id=testUser; Password=testUser;";
        }
 
        static private string GetQuery()
        {
            return  "SELECT AnFacAbCom.[Document No_],  " +
                    "AnFacAbCom.Name,                   " + 
                    "AnFacAbCom.Type,                   " +
                    "AnFacAbCom.Description             " +
                    "FROM   Analitica_Fact_Abonos_Compra as AnFacAbCom" ;
 
        }

Before the report will be showed, the server connection window appear.
Hope you can help me!
Regards

Re: Server still ask me the password

Crystal Report Datasourse at Client End

3:33 11 Nov '10

I tried ur code. and found out that the SQL Server does not allow to do so. Instead i had to use the DNS datasource for this. Sorry i couldn't help you out. If any time i get the solution i will do reply you back. Thank you Imran A Momin Co.Software Developer
Sign In·View Thread·Permalink

My vote of 1

meridyen

20:25 12 Apr '10

yetersiz
Sign In·View Thread·Permalink	1.00/5 (1 vote)

Thanks

adpatel7@gmail.com

6:56 16 Feb '10

I been looking for this for long time Worked for me! Great
Sign In·View Thread·Permalink

Thanx

infinitess

20:58 7 Jan '09

Thanx a lot. I used the same with Visual Studio 2008. Deepak Shah Infinite Software Solutions www.infinitess.com
Sign In·View Thread·Permalink

Member 3853345

3:11 14 Mar '08

I am working on Asp.net 2008 window project. When I design Crystal Report I am using DB from my bin\debug\ folder. I am using Access Function to design report.During Project deployment I am design Setup project and also add report file and DB file in Setup.

Using Application.StarupPath I can create my connection string and retrive dataset. Set this dataset as my report dataset.
Now problem is at runtime DB is differ then as design time so I get Err.
I am getting err while DB verification at report using verifyDataBase() function. If I am not use this function then report use my local DB address to design Report at Client End.

Re: Crystal Report Datasourse at Client End

A Blank Report appears after binding a dataset to the report [modified]

5:36 14 Mar '08

I got it .. I use to face the same kind of problem.. when i use to call the verifydatabase() method

Now this method check if any changes are made to the database, if any it does the changes to the report else well means .. it will most propably will delete the field or table if they dont exits.

So what i did is that .. tried to kept the same fields and tables that i used to design the reports.. so i wont get any errors ... and i was succesfull in that..

Hope u get the answer u need..

Imran A Momin
Co.Software Developer

DaRk_AsSaSSin

16:47 7 Nov '07

i am using Crystal Reports XI R2 and Visual Basic 2005, i'm having problem previewing the report after binding the ReportDocument.SetDatasource to a DataSet, but if i print directly or export there is no problem.

this is my code.

Dim SQL As String

        Dim CRXiReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument

        Try

 

            SQL = "SELECT  WFLotNo, ChipModelName, WFQty, Location, StockInDate, StockInUser FROM StockReport2"

            RptSource(SQL, "StockReport")'Subroutine to populate the RptDataset

            With CRXiReport

                .Load(Application.StartupPath & "\Reports\StockReport.rpt", OpenReportMethod.OpenReportByDefault)

                .SetDataSource(RptDataSet.Tables("StockReport"))

                .VerifyDatabase()

                '.PrintToPrinter(1, False, 1, 1) 'This works fine

            End With

 

            'Stock Report

            FrmViewer = Nothing

            FrmViewer = New FrmCRXi

            With FrmViewer

                .CrystalReportViewer1.ReportSource = CRXiReport

                .Show() 'Shows only the ReportViewer without any report!

            End With

 

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

Here's the Screenshot http://www.flickr.com/photos/16102763@N05/1912653386/
-- modified at 22:53 Wednesday 7th November, 2007

reportsource is readonly

stdywell

12:27 30 Sep '07

Sir I am having difficulty with this example, which I am sure is due to my being new to VB.
I am using VisualStudio 2005.
My goal is to create a standard CrystalReport, and allow the user to select the DB file to use, using an openfiledialog box.
When i create the report, it asks for the DB file to use. ( I assume this would be default DB) - I select the DB, & design a simple test report.

I cannot get this to compile, because I get the error: Error 1 Property 'ReportSource' is 'ReadOnly'
at the line: cr.ReportSource = rpt '' assign the report to the crytal reprot viewr (cr)

What am I doing wrong ? Thanks for any help you can give me.
Here is the code:



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

 

        OpenFileDialog1.Filter = "MS Access Files (*.mdb) | *.mdb"

        If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

        End If

 

        Try

            rpt.DataSourceConnections.Item(0).SetConnection("", "" & OpenFileDialog1.FileName & "", False)

 



            '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 ex As Exception

 

            MsgBox(ex.Message)

        End Try

 



    End Sub

 



End Class

Re: reportsource is readonly

Thank you. It was helpfull !

23:34 2 Nov '07

Hello I think the Problem lies in

OpenFileDialog1.Filter = "MS Access Files (*.mdb) | *.mdb"
If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
End If

Try
rpt.DataSourceConnections.Item(0).SetConnection("", "" & OpenFileDialog1.FileName & "", False)

Where the rpt.datasource, u have given only the filename and not the filepath, coz that could be the reason for the error u recive..

Another thing is ,, whether the Crystalreport1 file is ReadOnly. If it is,, do check it..

Imran Smile | :)

Record Set

ssmony

16:19 20 Aug '07

Hi, Thank you very much for your code.
The question is can I use Record Set? Can you show me how?

For example:

rsCliList = New ADODB.Recordset
With rsCliList
.Fields.Append("ID", ADODB.DataTypeEnum.adBSTR)
. ....
.Open()
End With

Dim nn As Integer
rs.Open("Select * from Client", conn1,_
ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

Do Until rs.EOF
rsCliList.AddNew()
rsCliList.Fields("ID").Value = rs.Fields("Cl_ID").Value
Exit Do
rs.MoveNext()
Loop
rs.Close()
rpt1.SetDataSource(rsCliList)
crViewer1.ReportSource = rpt1

George Mamaladze

20:29 19 Jun '07

Thank you. It was helpfull! I gave you 5.
Sign In·View Thread·Permalink

Re: Thank you. It was helpfull !

Crystal reports .datasourceconnections.item(0).setlogin( [modified]

21:44 19 Jun '07

Thanx for your support...
Sign In·View Thread·Permalink

i340

22:24 28 Mar '07

hi,

i set dynemically change access database of crystal reports with vb.net.

i have not given any password to database.

i have problem for at,

rpt.DataSourceConnections.Item(0).SetLogon("admin", "admin)

at runtime i have problem for password, i give user="Admin" and password "" or user="admin" and password "admin".

it not accept any password and user.

rgds,

irfan

-- modified at 7:08 Thursday 29th March, 2007

-- modified at 7:11 Thursday 29th March, 2007

irfan pathan

Re: Crystal reports .datasourceconnections.item(0).setlogin(

doesn´t work with odbc connection

9:02 29 Mar '07

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
Sign In·View Thread·Permalink	3.00/5 (1 vote)

Cristina Rodriguez Garcia

23:23 7 Nov '06

Hello,

First, thanks for your article! it´s good!

But i have a problem. I have a report that in design time link with database with an odbc (Oracle provider). In runtime if i don´t change connection it works fine. My problem is that if i change de odbc (i try other odbc with other name but with same dns) at runtime, it doesn´t work.

Only works if i change odbc connection in report at design time...but i need do it programtilly because i need user choose between two equals databases: real and testing.

I try your code and other codes like that:

Dim crConnectionInfo As ConnectionInfo
Dim crDatabase As Database
Dim crTables As Tables
Dim crTableLogOnInfo As TableLogOnInfo
Dim crReportDocument As ReportDocument = Me.rptDoc

crDatabase = crReportDocument.Database
crTables = crDatabase.Tables
crConnectionInfo = New ConnectionInfo()
crConnectionInfo.ServerName = "ODBCNAME"
crConnectionInfo.DatabaseName = ""
crConnectionInfo.UserID = "USER"
crConnectionInfo.Password = "PASSWORD"
For Each aTable As CrystalDecisions.CrystalReports.Engine.Table In crTables
crTableLogOnInfo = aTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo.AllowCustomConnection = True
crTableLogOnInfo.ConnectionInfo = crConnectionInfo
aTable.ApplyLogOnInfo(crTableLogOnInfo)
Next

Could you help me?, thanks in advance
Cristina

Re: doesn´t work with odbc connection