Skip to Content
avatar image
Former Member

SetSQLCommandTable with parameters

hello all,
How can I do change SQL-script to CommnadTable with parameters? My testing example when ends with an error:

CrystalDecisions.CrystalReports.Engine.ReportDocument doc;
doc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
doc.Load("D:\\Temp_02\\Report1_Test.rpt");

CrystalDecisions.Shared.ConnectionInfo connectionInfo;
connectionInfo = (CrystalDecisions.Shared.ConnectionInfo)doc.Database.Tables[0].LogOnInfo.ConnectionInfo.Clone();
connectionInfo.Password = "password";

CrystalDecisions.Shared.ParameterValues ThisPV = new CrystalDecisions.Shared.ParameterValues();
ThisPV.AddValue(3);
doc.DataDefinition.ParameterFields[0].ApplyCurrentValues(ThisPV);

try
{               
    doc.SetSQLCommandTable(connectionInfo, doc.Database.Tables[0].Name, "SELECT * FROM sys.all_objects WHERE schema_id = {?CR ParSchemaId}");   
}
catch (Exception ex)
{
    doc.Close();
}
doc.SaveAs("D:\\Temp_02\\Report2_Test.rpt");

If I set SQL-script without parameters then I have no error. The problem is only parameters. I do not know how I can to set these parameters.

I want to change SQL-string for CommandTable:

SELECT TOP(1)* FROM sys.all_objects WHERE schema_id = {?CR ParSchemaId}

on

SELECT * FROM sys.all_objects WHERE schema_id = {?CR ParSchemaId}

How can I do it?

Thanks, Artem

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    May 31, 2017 at 03:34 PM

    I found the answer on my ask. Here is the solution:

    using CrystalDecisions.CrystalReports.Engine;
    using CrystalDecisions.Shared;
    using CrystalDecisions.ReportAppServer.DataDefModel;
    using CrystalDecisions.ReportAppServer.Controllers;
    using CrystalDecisions.ReportAppServer.ClientDoc;
    
    private void button_Test_Click(object sender, EventArgs e)
    {
        ReportDocument doc = new ReportDocument();
    
        doc.Load("D:\\Temp_01\\Report1_Test.rpt");
    
        ISCDReportClientDocument rcd = doc.ReportClientDocument as ISCDReportClientDocument;
    
        CommandTable rcdTable_Old;
    
        CommandTable rcdTable_New;
    
        try
        {
            rcdTable_Old = rcd.Database.Tables[0] as CommandTable;
    
            rcdTable_New = rcd.Database.Tables[0].Clone(true) as CommandTable;
    
            rcdTable_New.CommandText = "SELECT TOP(1000) * FROM sys.all_objects WHERE name = {?CR ParSchemaId}";
    
            rcd.DatabaseController.LogonEx("ServerName", "DataBaseName", "UserId", "Password");
    
            rcd.VerifyDatabase();
    
            rcd.SaveAs(rcd.DisplayName, "D:\\Temp_02\\", 1);
    
            rcd.Close();
        }
        catch (Exception ex)
        {
            rcd.Close();
    
            MessageBox.Show(ex.Message);
        }
    }

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • May 30, 2017 at 04:45 PM
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 30, 2017 at 10:37 PM

    Hello Don,

    I saw this blog, but I could not read how to do change SQL with parameters for CommandTable there.
    Don, please, give an example here how to do it. How to do change SQL with parameters in CommandTable?

    Thanks

    Add comment
    10|10000 characters needed characters exceeded