Parameterized SQL Query

Parameterized SQL Query

Posted by: Charles.Jimenez on 11 August 2018, 1:08 pm EST

• 

  Charles.Jimenez

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 11 August 2018, 1:08 pm EST

  I am evaluating the C1FlexReport/C1FlexViewer. I need to build an SQL query as a datasource that is driven by a parameter I can pass at rendering time. For example, if my SQL Query is:

  Select Column1, Column2,Column3 from Datatable
  
  Where [Column4] = ‘SomeParameter’

  I need to be able to substitute ‘SomeParameter’ with an actual parameter I can pass to the datasource of C1FlexViewer at run/query-time, so that the type of object I am retrieving data about can be selected by the user.

  In the designer I see that you can associate parameters with the datasource, but I don’t see how to “glue” them in to the SQL Query.

  Is it possible to do what I need?

  Thanks in advance.

  Charlie

• 

  esha.dhir

  

  • Post Options:
  • Link
    Copy

  Posted 13 August 2018, 3:14 am EST

  Hello Charlie,

  Thanks for considering ComponentOne FlexReport.
  
  To be able to choose a parameter for the SQL query at run-time, you can make use of a combobox that can help you select the value that can further be passed to the query, to fetch the records and render the report as expected. The corresponding sample is attached for your reference. Note that the report is bound to C1Nwind.mdb shipped with ComponentOne controls.

  Best Regards,
  
  Esha
  
  prj_C1FlexReport_Parameters.zip

• 

  Charles.Jimenez

  • Post Options:
  • Link
    Copy

  Posted 13 August 2018, 10:17 am EST

  Thank you, Esha. That was very helpful.

  Charlie

• 

  Charles.Jimenez

  • Post Options:
  • Link
    Copy

  Posted 26 August 2018, 10:09 am EST

  Hi Esha,

  Using the example code you provided, I am having no success using a parameter to set the Top Count in the Select portion of the SQL Query. Here is my modified code:

  
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
      'Default parameter set at simple button click
      Dim para As ReportParameter
  
      C1FlexReport1.DataSource.RecordSource = "select TOP [TopCount] * from [Cars] where [Cyl] = [Cylinder];"
      para = New ReportParameter
      para.DataType = C1.Win.C1Document.ParameterType.[Integer]
      para.Name = "Cylinder"
      para.Prompt = "Cylinder"
      para.Value = 6
      para.AllowedValuesDefinition.Binding.DataSourceName = C1FlexReport1.DataSource.Name
      C1FlexReport1.Parameters.Add(para)
  
      para = New ReportParameter
      para.DataType = C1.Win.C1Document.ParameterType.[Integer]
      para.Name = "TopCount"
      para.Prompt = "Top Count"
      para.Value = 2
      'para.AllowedValuesDefinition.Binding.DataSourceName = C1FlexReport1.DataSource.Name
      C1FlexReport1.Parameters.Add(para)
  
      'C1FlexReport1.Render()
      C1FlexViewer1.DocumentSource = C1FlexReport1
    End Sub
  
  

  When I run this, it reports that the SELECT statement contains an error. It looks like the parameter is not being substituted into the SQL Query.

  Also, I do not understand how the "AllowedValuesDefinition’ works. It does not seem to make any difference how I set it, but is it supposed to derive the allowed values from the datasource itself? I don’t understand how it does this. In the example code, are the allowable values for Cylinders derived from the Cars table? How does it do this?

  Thank you for your help.

  Charlie

• 

  esha.dhir

  

  • Post Options:
  • Link
    Copy

  Posted 27 August 2018, 4:48 am EST

  Hello Charlie,

  I could observe the issue while using a parameter with TOP clause. I am discussing the same with the development team and will get back to you once it is done.

  AllowedValuesDefinition represents a collection of allowed values for a ReportParameter. It may be None, through a data source or through a set of defined values. Its members are mentioned here:
  
  http://help.grapecity.com/componentone/NetHelp/FlexReport/webframe.html#C1.Win.FlexReport.4~C1.Win.FlexReport.AllowedValuesDefinition_members.html
  
  Hope it helps.

  Best Regards,
  
  Esha

• 

  esha.dhir

  

  • Post Options:
  • Link
    Copy

  Posted 29 August 2018, 2:56 am EST

  Charlie,

  On setting the DataSource.ParameterPassingMode property to ParameterPassingMode.Literal, the issue is resolved. The modified code snippet is:

   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
         'Default parameter set at simple button click
         Dim para As ReportParameter
  
         C1FlexReport1.DataSource.ParameterPassingMode = ParameterPassingMode.Literal
         C1FlexReport1.DataSource.RecordSource = "select TOP [TopCount] * from [Cars] where [Cyl] = [Cylinder];"
         para = New ReportParameter
         para.DataType = C1.Win.C1Document.ParameterType.[Integer]
         para.Name = "Cylinder"
         para.Prompt = "Cylinder"
         para.Value = 6
         para.AllowedValuesDefinition.Binding.DataSourceName = C1FlexReport1.DataSource.Name
         C1FlexReport1.Parameters.Add(para)
  
         para = New ReportParameter
         para.DataType = C1.Win.C1Document.ParameterType.[Integer]
         para.Name = "TopCount"
         para.Prompt = "TopCount"
         para.Value = 2
         C1FlexReport1.Parameters.Add(para)
  
         C1FlexReport1.Render()
         C1FlexViewer1.DocumentSource = C1FlexReport1
     End Sub
  

  Hope it helps.

  Best Regards,
  
  Esha

• 

  Charles.Jimenez

  • Post Options:
  • Link
    Copy

  Posted 29 August 2018, 4:37 pm EST

  Hi Esha,

  I will try this and let you know if I run into any other difficulties.

  Thanks for your help.

  Charlie