Skip to Content
0

SetSQLCommandTable with parameters

May 30, 2017 at 03:42 PM

103

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Artem Solodovnik May 31, 2017 at 03:34 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
May 30, 2017 at 04:45 PM
0
Share
10 |10000 characters needed characters left characters exceeded
Artem Solodovnik May 30, 2017 at 10:37 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded