Report with SqlDataSource is blank

[Benedikt]

I have a problem with the newest telerik Reporting component. I have successfully designed a report and embedded every thing in an application. The source of the report is a SqlDataSource which has access to an SAP Sybase SQL Anywhere 12 database. Every thing works fine with the following statement:

 

select * from document

 

A problem occures if i add some parameter to the statement:

 

select * from document where id = ?

 

If i add this i don't get any error message nore the report works correctly. I only get a blank page. In the designer and in the viewer. I tried some different parameter passing methods but nothing works. What is the problem here? I also could not find anyhing in the documentation for SAP Sybase SQL Anywhere 12. Is this not supported? (It should because it is simple ADO.Net). I've tried a lot with the parameter system but still wount work. Thank you very much!

 P.S. i add the parameter like this to the report:

SqlDataSource ds = (report.DataSource as SqlDataSource);
ds.Parameters.Add(new SqlDataSourceParameter(​"Id", DbType.Guid, ​... some guid ...));

[Benedikt]

Added a complete example. You only need a sybase 12 db which has a FileName column (varchar). And you have to set the connection string in the constant: CONNECTION_STRING.

If you turn useParameter to true you will get a blank page. If not every thing works.

Could not add a working zip. But here is the code:

namespace ReportingBug
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        const string CONNECTION_STRING = "";

        public MainWindow()
        {
            InitializeComponent();

            bool useParameter = true;

            // Create new report
            Report report = new Report();

            DetailSection detail = new DetailSection();
            detail.Height = new Telerik.Reporting.Drawing.Unit(3.0, Telerik.Reporting.Drawing.UnitType.Inch);
            detail.Name = "detail";
            report.Items.Add((ReportItemBase)detail);

            Telerik.Reporting.Panel panel1 = new Telerik.Reporting.Panel();
            Telerik.Reporting.TextBox textBox1 = new Telerik.Reporting.TextBox();

            // panel1

            panel1.Location = new Telerik.Reporting.Drawing.PointU(new Telerik.Reporting.Drawing.Unit(1.0, Telerik.Reporting.Drawing.UnitType.Cm), new Telerik.Reporting.Drawing.Unit(1.0, Telerik.Reporting.Drawing.UnitType.Cm));
            panel1.Size = new Telerik.Reporting.Drawing.SizeU(new Telerik.Reporting.Drawing.Unit(8.5, Telerik.Reporting.Drawing.UnitType.Cm), new Telerik.Reporting.Drawing.Unit(3.5, Telerik.Reporting.Drawing.UnitType.Cm));
            panel1.Style.BorderStyle.Default = Telerik.Reporting.Drawing.BorderType.Solid;

            // textBox1

            textBox1.Location = new Telerik.Reporting.Drawing.PointU(new Telerik.Reporting.Drawing.Unit(0, Telerik.Reporting.Drawing.UnitType.Cm), new Telerik.Reporting.Drawing.Unit(0, Telerik.Reporting.Drawing.UnitType.Cm));
            textBox1.Name = "NameDataTextBox";
            textBox1.Size = new Telerik.Reporting.Drawing.SizeU(new Telerik.Reporting.Drawing.Unit(5.0, Telerik.Reporting.Drawing.UnitType.Cm), new Telerik.Reporting.Drawing.Unit(0.6, Telerik.Reporting.Drawing.UnitType.Cm));
            textBox1.Style.BorderStyle.Default = Telerik.Reporting.Drawing.BorderType.Solid;
            textBox1.StyleName = "Data";
            textBox1.Value = "=Fields.FileName";

            panel1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] { textBox1 });
            detail.Items.AddRange(new Telerik.Reporting.ReportItemBase[] { panel1 });

            // ======================================================================
            // Crete data source
            SqlDataSource dataSource = new SqlDataSource();
            dataSource.ConnectionString = CONNECTION_STRING;
            dataSource.ProviderName = "iAnywhere.Data.SQLAnywhere";

            if (!useParameter)
            {
                dataSource.SelectCommand = "SELECT * FROM IT_Document";
            }
            else
            {
                dataSource.SelectCommand = "SELECT * FROM IT_Document WHERE FileName = ?";
                dataSource.Parameters.Add("FileName", System.Data.DbType.String, "Test");
            }

            report.DataSource = dataSource;
            // ======================================================================

            // ======================================================================
            // Fill viewer
            InstanceReportSource source = new InstanceReportSource();
            source.ReportDocument = report;

            reportViewer.ReportSource = source;
            reportViewer.RefreshReport();
            // ======================================================================
        }
    }
}

Thank you!

[Stef]

Hi Benedikt,

The SqlDataSource component uses standard ADO.NET classes and the installed on the machine .NET providers. It is the data provider which interprets the SQL queries passed by the SqlDataSource.

Our tests also point that queries without parameters are executed successfully with the iAnywhere.Data.SQLAnywhere .NET data provider. Yet queries with parameters fail.

To avoid the issue, my recommendation is to generate the SQL query at run-time by modifying the data source components in the report. For the purpose you can use the approach illustrated in Changing the connection string dynamically according to runtime data. Once you have the DataSource property of a data item, you will be able to change the SelectCommand property as well.

Regards,
Stef
Telerik

[Benedikt]

Thank you for your answer. This workaround will "work" at the beginning, but should not be the default solution, because passing DateTime and other things are much more complex than converting them to a string...

[Stef]

Hello,

Just an update for anyone concerned:
"The issue was discussed with our development team and further investigated. It appears the provider interprets incorrectly queries and returns a wrong list with possible parameters. You can check this by executing the following command against Sybase:

static void Main(string[] args)
     {
         IterateParameters("iAnywhere.Data.SQLAnywhere",
             "UserID=DBA;Password=sql;DatabaseName=demo;ServerName=demo16",
             "SELECT * FROM Departments WHERE DepartmentName = ?");
         Console.ReadKey();

     }

     static void IterateParameters(string provider, string connectionString, string commandText)
     {
         var factory = DbProviderFactories.GetFactory(provider);

         using (var dbCon = factory.CreateConnection())
         {
             try
             {
                 dbCon.ConnectionString = connectionString;

                 dbCon.Open();

                 var command = dbCon.CreateCommand();
                 command.CommandText = commandText;
                 var parameter=command.CreateParameter();
                 parameter.DbType = System.Data.DbType.String;
                 parameter.Value = "Marketing";

                 command.Parameters.Add(parameter);
                 foreach (DbParameter p in command.Parameters)
                 {
                     Console.WriteLine("Parameter: {0}", p.Value);
                 }
                 var r = command.ExecuteScalar();
                 Console.WriteLine(r);
             }
             catch(Exception ex)
             {
                 Console.WriteLine(ex);
             }
             finally
             {
                 dbCon.Close();
             }
         }
     }

Our developers are working to improve the behavior of the SqlDataSource Wizard in cases of such data providers settings"



Regards,
Stef
Telerik