Skip to Content
avatar image
Former Member

How do dynamic and correctly to change the query in CommandTable for Crystal Reports file?

Hello all,

I have many rpt files. I want to change the query for each report using C#. There are several ways to do this changes.

First way:

private void button_Test_Click(object sender, EventArgs e)
{

    ReportDocument rptDoc = new ReportDocument();

    rptDoc.Load("D:\\Temp_01\\Report1_Test.rpt");

    rptDoc.SetDatabaseLogon("User", "Password", "ServName", "DBName");

    CrystalDecisions.Shared.ConnectionInfo ConnInf;

    ConnInf = rptDoc.Database.Tables[0].LogOnInfo.ConnectionInfo;

    String strSQLQuery = "SELECT TOP(123) * FROM sys.all_objects";

    String strTableName = rptDoc.Database.Tables[0].Name;

    try
    {
        rptDoc.SetSQLCommandTable(ConnInf, strTableName, strSQLQuery);

        rptDoc.VerifyDatabase();
    }
    catch (Exception ex) { rptDoc.Close(); }

    rptDoc.SaveAs("D:\\Temp_02\\Report2_Test.rpt");

    rptDoc.Close();
}

It is not the best way. The method SetSQLCommand does not work when the query has any parameters. Even if you set value for each parameter, SetSQLCommand does not work. The example with a parameter which does not work:

private void button_Test_Click(object sender, EventArgs e)
{

    ReportDocument rptDoc = new ReportDocument();

    rptDoc.Load("D:\\Temp_01\\Report1_Test.rpt");

    rptDoc.SetDatabaseLogon("User", "Password", "ServName", "DBName");

    CrystalDecisions.Shared.ConnectionInfo ConnInf;

    ConnInf = rptDoc.Database.Tables[0].LogOnInfo.ConnectionInfo;

    String strSQLQuery = "SELECT TOP(1) * FROM sys.all_objects WHERE name = {?strName}";

    String strTableName = rptDoc.Database.Tables[0].Name;

    try
    {
        rptDoc.SetParameterValue("strName", "Text");

        rptDoc.SetSQLCommandTable(ConnInf, strTableName, strSQLQuery);

        rptDoc.VerifyDatabase();
    }
    catch (Exception ex) { rptDoc.Close(); }

    rptDoc.SaveAs("D:\\Temp_02\\Report2_Test.rpt");

    rptDoc.Close();
}

It returns an error. This method does not work with parameters!

Second way:

private void button_Test_Click(object sender, EventArgs e)
{
    ReportDocument rptDoc = new ReportDocument();

    rptDoc.Load("D:\\Temp_01\\Report1_Test.rpt");

    rptDoc.SetDatabaseLogon("User", "Password", "ServName", "DBName");

    ISCDReportClientDocument rcd = null;

    rcd = rptDoc.ReportClientDocument as ISCDReportClientDocument;

    CommandTable rTblOld;

    CommandTable rTblNew;

    rTblOld = rcd.Database.Tables[0] as CommandTable;

    rTblNew = rcd.Database.Tables[0].Clone(true) as CommandTable;

    rTblNew.CommandText = "SELECT TOP(1) * FROM sys.all_objects";

    try
    {
        rcd.DatabaseController.SetTableLocationEx(rTblOld, rTblNew);

        rcd.VerifyDatabase();
    }
    catch (Exception ex) { rcd.Close(); }

    rcd.SaveAs(rcd.DisplayName, "D:\\Temp_02\\", 1);

    rcd.Close();
}

This is also not the best way. The method SetLocalTableEx does a struct of the report is bad. After run SetLocalTableEx, attribute ConnectionInf.UserId have value NULL also the Name of connection

After SetTableLocationEx:

rcd.DatabaseController.SetTableLocationEx(rTblOld, rTblNew);

String UserID;

UserID = rptDoc.Database.Tables[0].LogOnInfo.ConnectionInfo.UserID;

if (UserID == null) MessageBox.Show("UserID has NULL");

UserId has value NULL

Also, before run SetTableLocationEx, Connection Name is MSODBCSQL11

After run SetTableLocationEx, Connection Name is Command

So,

how do dynamic and correctly to change the query in CommandTable for Crystal Reports file?

Thanks,

Artem

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jun 02, 2017 at 06:04 PM

    Set the Parameter values first. See this for an example, does require editing in VS and debugging.

    https://blogs.sap.com/2016/02/17/how-to-parameters-in-crystal-reports-for-visual-studio-net/

    Don

    Add comment
    10|10000 characters needed characters exceeded