How do i change logon info dynamically in crystal report and ms access?

Question

I have my crystal reports file accessing data from msaccess database.

now while loading report i need to pass the logon info of the msaccess database along with the database name from vb.net.

I tried using

    Dim ConnInfo As ConnectionInfo = New ConnectionInfo()

    CRpt.ReportOptions.EnableSaveDataWithReport = False

    ConnInfo.IntegratedSecurity = False
    ConnInfo.ServerName = ""
    ConnInfo.UserID = ""
    ConnInfo.Password = ""
    ConnInfo.DatabaseName = OLEDBLayer.GetDBLocation()
    ConnInfo.Type = ConnectionInfoType.DBFile

    'CCINFo.ServerName = 
    For Each CTable As Table In CRpt.Database.Tables
        CTableLogInfo = CTable.LogOnInfo
        CTableLogInfo.ConnectionInfo = ConnInfo
        'CTable.Location = OLEDBLayer.GetDBLocation
        CTable.ApplyLogOnInfo(CTableLogInfo)
    Next

But not working. What am i missing?

Solution 1

I searched alot and finally after testing my self by creating new report using static data i got the solution for dynamic one.

My test Procedure:
1.) Created new Crystal Report file:
2.) Created new Database Connection to the .accdb access 2007 file
3.) Created new Form
4.) Added Crystal Report Viewer control on the form
5.) Assigned the report document to the previously created report.
6.) set break point on load of the form
7.) Read all the settings
8.) Copied the settings of the report document, document tables logon info.
9.) Pasted the settings read to my project.
10.) Worked fine... :)

My Code :

    //here crpt is a sample report document
    Dim CTableLogInfo As TableLogOnInfo
    Dim ConnInfo As CrystalDecisions.Shared.ConnectionInfo = New ConnectionInfo()
    ConnInfo.Type = ConnectionInfoType.CRQE
    ConnInfo.ServerName = DBLayer.GetAbsoluteDBPath()
    ConnInfo.DatabaseName = ""
    ConnInfo.UserID = "Admin"
    ConnInfo.AllowCustomConnection = False
    ConnInfo.IntegratedSecurity = False

    For Each CTable As Table In CRpt.Database.Tables
        CTable.LogOnInfo.ConnectionInfo = ConnInfo
        CTableLogInfo = CTable.LogOnInfo
        CTableLogInfo.ReportName = CRpt.Name
        CTableLogInfo.TableName = CTable.Name
        CTable.ApplyLogOnInfo(CTableLogInfo)
    Next

    CrystalReportViewer1.ReportSource = CRpt
    CrystalReportViewer1.RefreshReport()

I got the point that setting the database path to the servername would resolve that

Solution 2

You can use the following code to apply certain connection details for a report at run time.
Sorry, code in c#.
Please use that method just after loading report rpt file, and before printing/exporting/viewing it.

    public static void CrystalReportLogOn(ReportDocument reportParameters,
                                          string serverName,
                                          string databaseName,
                                          string userName,
                                          string password)
    {
        TableLogOnInfo logOnInfo;
        ReportDocument subRd;
        Sections sects;
        ReportObjects ros;
        SubreportObject sro;

        if (reportParameters == null)
        {
            throw new ArgumentNullException("reportParameters");
        }

        try
        {
            foreach (CrystalDecisions.CrystalReports.Engine.Table t in reportParameters.Database.Tables)
            {
                logOnInfo = t.LogOnInfo;
                logOnInfo.ReportName = reportParameters.Name;
                logOnInfo.ConnectionInfo.ServerName = serverName;
                logOnInfo.ConnectionInfo.DatabaseName = databaseName;
                logOnInfo.ConnectionInfo.UserID = userName;
                logOnInfo.ConnectionInfo.Password = password;
                logOnInfo.TableName = t.Name;
                t.ApplyLogOnInfo(logOnInfo);
                t.Location = t.Name;
            }
        }
        catch
        {
            throw;
        }

        sects = reportParameters.ReportDefinition.Sections;
        foreach (Section sect in sects)
        {
            ros = sect.ReportObjects;
            foreach (ReportObject ro in ros)
            {
                if (ro.Kind == ReportObjectKind.SubreportObject)
                {
                    sro = (SubreportObject)ro;
                    subRd = sro.OpenSubreport(sro.SubreportName);
                    try
                    {
                        foreach (CrystalDecisions.CrystalReports.Engine.Table t in subRd.Database.Tables)
                        {
                            logOnInfo = t.LogOnInfo;
                            logOnInfo.ReportName = reportParameters.Name;
                            logOnInfo.ConnectionInfo.ServerName = serverName;
                            logOnInfo.ConnectionInfo.DatabaseName = databaseName;
                            logOnInfo.ConnectionInfo.UserID = userName;
                            logOnInfo.ConnectionInfo.Password = password;
                            logOnInfo.TableName = t.Name;
                            t.ApplyLogOnInfo(logOnInfo);
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }
    }

Solution 3

Try this out:

        Dim CTableLogInfo As TableLogOnInfo
        Dim ConnInfo As CrystalDecisions.Shared.ConnectionInfo = New ConnectionInfo()
        Dim  CRpt As New ReportDocument
        String filename = "rptSales.rpt"

        ConnInfo.Type = ConnectionInfoType.CRQE
        ConnInfo.ServerName = AppSettings("server")
        ConnInfo.DatabaseName = AppSettings("dbNm")
        ConnInfo.UserID = AppSettings("username")
        ConnInfo.Password = AppSettings("pas")
        ConnInfo.AllowCustomConnection = False
        ConnInfo.IntegratedSecurity = False

        CRpt.Load(AppSettings("reppath") & filename)

        For Each CTable As Table In CRpt.Database.Tables
            CTable.LogOnInfo.ConnectionInfo = ConnInfo
            CTableLogInfo = CTable.LogOnInfo
            CTableLogInfo.ReportName = CRpt.Name
            CTableLogInfo.TableName = CTable.Name
            CTable.ApplCTableLogInfo)
        Next

Comments

[KoolKabin]

Since my database is access what should i use in place of servername, databasename, userid and password

[Pinoy2015]

For Access database, shall you use set logOnInfo.ConnectionInfo to your connection details, which I think it looks like this one "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;" or "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB:Database Password=MyDbPassword;" if database with password, and don't provide any details for username/passwors/servername/databasename, just use connectioninfo property.

[KoolKabin]

I tried setting logOnInfo.connectionInfo to similar one like you provided but it says can't assign string value to connectionInfo object.. data type mismatched

[roncansan]

After 5 hours of debugging, I found that this wasn't working because the report has the SAVE DATA option enabled. After disabling this option this method works. Thanks

[Ashi]

Oracle database and working perfectly, I did not have to do any modification to the code! Thanks

[GrafixMastaMD]

I don't like resurecting old threads. But is there any possiblity you might be able to assist me. I tried what you have mentioned (converted your code to C#. But my problem is that my database has a password. What I find interesting is that I can connect in Crystal Reports no prob. Access no problem. But when I try to do it through C# says my password is in correct my login is incorrect. It's weird.

[GrafixMastaMD]

I have one problem. I used your method. It got me connected to MDB. all the reports are developed in MDB with crystal. But some users use SQL. Also some of the reports have sub reports.. Any help would be appreciated.