Changing database at runtime

joerage · April 19th, 2005, 08:41 AM

Hi,
i try to have a crystal report printed from my windows app that can accept different database name at runtime.

here is my code:

Code:

ReportDocument report = new ReportDocument();

report.Load(XML_DOCUMENT + "ICCLabelsByItems.rpt");

// Set paramaters here.
report.SetParameterValue("@item1", ((MTGCComboBoxItem)cbItem1.SelectedItem).Col1);
                    report.SetParameterValue("@item2", ((MTGCComboBoxItem)cbItem2.SelectedItem).Col1);

                    report.SetParameterValue("@quantity1", Convert.ToInt32(nudQuantity1.Value));
                    report.SetParameterValue("@quantity2", Convert.ToInt32(nudQuantity2.Value));


for (int i = 0; i <= report.Database.Tables.Count - 1; i++) 
{    
    TableLogOnInfo logOnInfos = new TableLogOnInfo();

    // Information needed to log into sql server.
    logOnInfos.ConnectionInfo.ServerName = DATA_SOURCE;
    logOnInfos.ConnectionInfo.DatabaseName = DATABASE;
    logOnInfos.ConnectionInfo.UserID = DB_USER_ID;
    logOnInfos.ConnectionInfo.Password = DB_PASSWORD;

    // Apply info on the current table.
    report.Database.Tables[i].ApplyLogOnInfo(logOnInfos);     

        // *********************************************//
        report.Database.Tables[i].Location = DATABASE + ".dbo." +   report.Database.Tables[i].Location.Substring(report.Database.Tables[i].Location.LastIndexOf (".") + 1); 

    }

                    report.SetDatabaseLogon(DB_USER_ID, DB_PASSWORD, DATA_SOURCE, DATABASE);
                    report.PrintOptions.PrinterName = printerName;

// Send to the printer.
report.PrintToPrinter(1, false, 0, 0);

If I remove the line:

Code:

report.Database.Tables[i].Location = DATABASE + ".dbo." +   report.Database.Tables[i].Location.Substring(report.Database.Tables[i].Location.LastIndexOf (".") + 1);

it print fines, but it doesn't use the database name specified at runtime. It takes whatever database I specified in the crystal report.

When that line is in the code the following error is thrown on the PrintToPrinter call:
Error in File C:\Documents and Settings\All Users.WINDOWS\Documents\CodeBarLabel\bin\Debug\ICC LabelsByItems.rpt:
Request cancelled by the user.

Anyone has an idea what I am doing wrong?

Thanks,
Joe

rrounkles · August 10th, 2005, 06:55 PM

Joe,
I used a simlar loop, but exposed each table using the iterator, like this:

CrystalDecisions.Shared.TableLogOnInfo logonInfo;
CrystalDecisions.Shared.ConnectionInfo connInfo = new CrystalDecisions.Shared.ConnectionInfo();
connInfo.ServerName = ConfigurationSettings.AppSettings["DBServer"];
connInfo.DatabaseName = ConfigurationSettings.AppSettings["DBName"];
connInfo.UserID = ""; //using integrated security (Windows)
connInfo.Password = "";

foreach(CrystalDecisions.CrystalReports.Engine.Tab le table in rpt.Database.Tables)
{
  [u]logonInfo = table.LogOnInfo;</u>Your code doesn't have this
  logonInfo.ConnectionInfo = connInfo;
  table.ApplyLogOnInfo(logonInfo);
  table.Location = table.Location.Substring(table.Location.LastIndexO f(".")+1);
}

I think what is causing your issue is the missing assignment of table.LogOnInfo. So, for your code something like this:

[i]TableLogOnInfo logOnInfos = report.Database.Tables.LogOnInfo;

Hope this helps.

Ryan

XPEHOTEH6 · November 7th, 2006, 07:42 AM

I'm having the similar error.
I'm using report (CR 9.1.5000) with pull model and it is still connecting to wrong database (where it has been created). I'm changing the datasource at runtime, but I am still getting error!

What can be wrong?

Any help would be greatly appreciated.

The code:
    Public Shared Sub UpdateLogonInfo(ByRef RepDoc As ReportDocument, ByVal DBProvider As DataProvider)
        Dim cni As New CrystalDecisions.Shared.ConnectionInfo
        Dim tli As New CrystalDecisions.Shared.TableLogOnInfo
        Dim crSecs As Sections
        Dim crSec As Section
        Dim crRepObjs As ReportObjects
        Dim crSubRepObj As SubreportObject
        Dim crSubReport As ReportDocument
        Dim i As Integer, j As Integer

        RepDoc.ReportOptions.EnableSaveDataWithReport = False

        cni.DatabaseName = DBProvider.DataBase
        cni.ServerName = DBProvider.ServerName
        cni.UserID = DBProvider.UserID
        cni.Password = DBProvider.Password

        ' Updating Database Logon Information
        RepDoc.SetDatabaseLogon(cni.UserID, cni.Password, cni.ServerName, cni.DatabaseName)

        ' Updating Tables Logon Information
        For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In RepDoc.Database.Tables
            tli = tbl.LogOnInfo
            tli.ConnectionInfo = cni
            tbl.ApplyLogOnInfo(tli)
            If (tbl.Location <> cni.DatabaseName & ".dbo." & tbl.Name) Then
                tbl.Location = cni.DatabaseName & ".dbo." & tbl.Name
            End If
        Next

        ' Updating Subreports Tables Logon Information
        crSecs = RepDoc.ReportDefinition.Sections
        For i = 0 To crSecs.Count - 1
            crSec = crSecs.Item(i)
            crRepObjs = crSec.ReportObjects
            For j = 0 To crRepObjs.Count - 1
                If crRepObjs.Item(j).Kind = ReportObjectKind.SubreportObject Then
                    crSubRepObj = RepDoc.ReportDefinition.ReportObjects.Item(crRepOb js.Item(j).Name)
                    crSubReport = RepDoc.OpenSubreport(crSubRepObj.SubreportName)
                    For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In crSubReport.Database.Tables
                        tli = tbl.LogOnInfo
                        tli.ConnectionInfo = cni
                        tbl.ApplyLogOnInfo(tli)
                        If (tbl.Location <> cni.DatabaseName & ".dbo." & tbl.Name) Then
                            tbl.Location = cni.DatabaseName & ".dbo." & tbl.Name
                        End If
                    Next
                End If
            Next
        Next
    End Sub

nater1111 · December 15th, 2006, 05:55 PM

i had a crystal report that queried multiple databases. this applied the correct connection info to the correct report.

            //logon to each table using table object connectioninfo
            foreach (CrystalDecisions.CrystalReports.Engine.Table tablePrint in rd.Database.Tables)
            {

                TableLogOnInfo tbloiLogon = tablePrint.LogOnInfo;
                tbloiLogon.ConnectionInfo.DatabaseName = tablePrint.LogOnInfo.ConnectionInfo.DatabaseName;
                tbloiLogon.ConnectionInfo.ServerName = tablePrint.LogOnInfo.ConnectionInfo.ServerName;
                tbloiLogon.ConnectionInfo.UserID = "user";
                tbloiLogon.ConnectionInfo.Password = "pass";
                tablePrint.ApplyLogOnInfo(tbloiLogon);
            }