How to use SQL Query Parameter?

xwolkyxxwolkyx
edited 7:49PM in FastReport .NET
Hi all
I need some help about using SQL Query Parameter in reports.
When i m trying to use this code "select * from some_table where id=@param1"; gives a error like in the image.
«1

Comments

  • AlexTZAlexTZ
    edited 7:49PM
    Hello,

    Please read the user's manual:
    http://fast-report.com/documentation/UserM...yparameters.htm
  • xwolkyxxwolkyx
    edited 7:49PM
    I have alredy read but i dont understand my english not good can you explain with example.
  • AlexTZAlexTZ
    edited 7:49PM
    Sorry, I have no example. This documentation article shows how to create a parameter and define its properties.
  • xwolkyxxwolkyx
    edited 7:49PM
    Thanks for advice i wonder can i use "@Param1,@Param2"; in SQL syntax just like SSRS.
  • AlexTZAlexTZ
    edited 7:49PM
    Yes, you may use something like
    select * from table where field in (@param1, @param2)
  • xwolkyxxwolkyx
    edited 7:49PM
    Man when i am try to use code like "select * from table where field in (@param1, @param2)"; gives an error as my first post top of the page.
  • AlexTZAlexTZ
    edited 7:49PM
    It's because you don't declare parameters! If your SQL text has parameters, you have to declare them as described in the user's manual.
  • xwolkyxxwolkyx
    edited 7:49PM
    Well how can i do that?
  • AlexTZAlexTZ
    edited 7:49PM
    Did you read the manual?
    - add a new parameter;
    - set up its properties (Name, DataType, DefaultValue, Expression).
  • xwolkyxxwolkyx
    edited 7:49PM
    Yes i have read and set new param and other things my question is;

    How can i buid a report befor run let the user choose select from a list or textbox predefined parameter.
  • AlexTZAlexTZ
    edited 7:49PM
    To pass a value to the query parameter, use the technique described in the following article:
    http://fast-report.com/documentation/UserM...passvalueto.htm

    If you want to form a SQL text dynamically, use the following code:
    TableDataSource table = Report.GetDataSource("MyTable") as TableDataSource;
    table.SelectCommand = your_sql_text;
  • xwolkyxxwolkyx
    edited 7:49PM
    Yes its work thank you.
  • xwolkyxxwolkyx
    edited 7:49PM
    One more question is there any way to use autocomplete in textbox?
  • AlexTZAlexTZ
    edited March 2010
    There is no public property of the TextBoxControl, but you may use the script and access the TextBox winform's control:
    Text1.TextBox.AutoCompleteMode = ...;
    Text1.TextBox.AutoCompleteSource = ...;
    (see more details here: http://fast-report.com/documentation/UserM...ingcontrol.htm)
  • xwolkyxxwolkyx
    edited 7:49PM
    Link broken
  • AlexTZAlexTZ
    edited 7:49PM
    Sorry, I've edited my post, now it's ok.
  • xwolkyxxwolkyx
    edited 7:49PM
    Ok now woking.

    I ask so many questions but how can i pass DataSelectorControl value to paramater?
  • AlexTZAlexTZ
    edited March 2010
    You can't pass multiple values to a query parameter. See the following topic:
    http://www.fast-report.com/en/forum/?p=/discussion/5690
  • xwolkyxxwolkyx
    edited 7:49PM
    This code is it right?

    TableDataSource Table = Report.GetDataSource("Connection") as TableDataSource;
    Table.SelectCommand = "select * from fason where firma in (" + @P1 + ")";
  • AlexTZAlexTZ
    edited 7:49PM
    Table.SelectCommand = "select * from fason where firma in (" + P1 + ")";
    (where P1 is the string-type variable)
  • xwolkyxxwolkyx
    edited 7:49PM
    I cant get this work can you please check my report file and fix for me.
  • AlexTZAlexTZ
    edited 7:49PM
    You have attached DataSelectorControl to "Table1" datasource, but your report prints "Table" datasource. To fix it, attach DataSelectorControl to "Table.firma" column. Also you don't need SQL in the "Table" datasource; just use the table name or simple sql text "select * from fason".
  • xwolkyxxwolkyx
    edited 7:49PM
    I have no problem with datasource s i added Table.Firma for select the unique values to DataSelectorControl "select distinct firma from fason" report is working correctly if i dont set Param1 expression to DataSelectorControl when i use TextBox for entering the Param1 values working but using DataSelectorControl gives an error;

    FastReport.Net v1.3.8
    Failed to convert parameter value from a DataSelectorControl to a String.
    Inner exception:
    Object must implement IConvertible.
    at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
    at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
    at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
    at System.Data.SqlClient.SqlParameter.GetCoercedValue()
    at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
    at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
    at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
    at FastReport.Data.DataConnectionBase.FillTableData(DataTable table, String selectCommand, CommandParameterCollection parameters)
    at FastReport.Data.DataConnectionBase.FillTable(TableDataSource source)
    at FastReport.Data.TableDataSource.LoadData(ArrayList rows)
    at FastReport.Data.DataSourceBase.Init(Relation relation, String filter, SortCollection sort)
    at FastReport.Data.DataSourceBase.Init(DataSourceBase parentData, String filter, SortCollection sort)
    at FastReport.Data.DataSourceBase.Init(DataSourceBase parentData)
    at FastReport.Dialog.DataFilterBaseControl.FillData(DataSourceBase parentData)
    at FastReport.Dialog.DataFilterBaseControl.InitializeControl()
    at FastReport.Dialog.DialogPage.l1rVOO3Rq()
    at FastReport.Engine.ReportEngine.mdGv397RC(DialogPage )
    at FastReport.Engine.ReportEngine.j2uIoT5PW()
    at FastReport.Engine.ReportEngine.sIBSXiMcT(Boolean , Boolean , ReportPage )
    at FastReport.Report.Prepare(Boolean append)
    at FastReport.Report.Prepare()
    at AxMUPd3GmP2LsD21tJe.HDAvfI3ulEnI6JrqKyR.5AG75Lqn0()
  • AlexTZAlexTZ
    edited 7:49PM
    Of course, because "DataSelector1" expression returns the DataSelectorControl object. You can't pass the control to a parameter (it's like passing "Text1" instead of "Text1.Text").
    Here is the corrected report. I'm not sure if it will work because I have no database and can't check it.
  • xwolkyxxwolkyx
    edited 7:49PM
    Yes its worked and thanks for fixing but you have filtering data in report side i dont want to do that i want to filtering data in sql query i have build many reports by using your technic.
  • AlexTZAlexTZ
    edited 7:49PM
    In your example, using server-side filtering has no advantage because you use one table only. In any case, you have to do full select to show available variants in the selector control.
  • xwolkyxxwolkyx
    edited 7:49PM
    Dear AlexTZ

    The Test file only for "Test" i want to make realy realy complex reports using views,relational tables can you modify my report passing multi values to sql parameter.
  • AlexTZAlexTZ
    edited 7:49PM
    Here it is.
  • xwolkyxxwolkyx
    edited 7:49PM
    Same error.


    FastReport.Net v1.3.8
    Failed to convert parameter value from a DataSelectorControl to a String.
    Inner exception:
    Object must implement IConvertible.
    at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
    at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
    at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
    at System.Data.SqlClient.SqlParameter.GetCoercedValue()
    at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
    at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
    at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
    at FastReport.Data.DataConnectionBase.FillTableData(DataTable table, String selectCommand, CommandParameterCollection parameters)
    at FastReport.Data.DataConnectionBase.FillTable(TableDataSource source)
    at FastReport.Data.TableDataSource.LoadData(ArrayList rows)
    at FastReport.Data.DataSourceBase.Init(Relation relation, String filter, SortCollection sort)
    at FastReport.Data.DataSourceBase.Init(DataSourceBase parentData, String filter, SortCollection sort)
    at FastReport.DataBand.InitDataSource()
    at FastReport.Engine.ReportEngine.Hoske0MJH(DataBand )
    at FastReport.Engine.ReportEngine.iOYhKNX5Qq(BandCollection )
    at FastReport.Engine.ReportEngine.pTShUSvp6t(ReportPage )
    at FastReport.Engine.ReportEngine.vyxhMoXTvD()
    at FastReport.Engine.ReportEngine.PLiKTkb6F(ReportPage )
    at FastReport.Engine.ReportEngine.sIBSXiMcT(Boolean , Boolean , ReportPage )
    at FastReport.Report.Prepare(Boolean append)
    at FastReport.Report.Prepare()
    at AxMUPd3GmP2LsD21tJe.HDAvfI3ulEnI6JrqKyR.5AG75Lqn0()
  • xwolkyxxwolkyx
    edited 7:49PM
    OK i solve the problem;
    "Failed to convert parameter value from a DataSelectorControl to a String.
    Inner exception:
    Object must implement IConvertible."

    but now;

    FastReport.Net v1.3.8
    Invalid column name 'ADONTE'. 'ADONTE' is my selected value not a column.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
    at FastReport.Data.DataConnectionBase.FillTableData(DataTable table, String selectCommand, CommandParameterCollection parameters)
    at FastReport.Data.DataConnectionBase.FillTable(TableDataSource source)
    at FastReport.Data.TableDataSource.LoadData(ArrayList rows)
    at FastReport.Data.DataSourceBase.Init(Relation relation, String filter, SortCollection sort)
    at FastReport.Data.DataSourceBase.Init(DataSourceBase parentData, String filter, SortCollection sort)
    at FastReport.DataBand.InitDataSource()
    at FastReport.Engine.ReportEngine.Hoske0MJH(DataBand )
    at FastReport.Engine.ReportEngine.iOYhKNX5Qq(BandCollection )
    at FastReport.Engine.ReportEngine.pTShUSvp6t(ReportPage )
    at FastReport.Engine.ReportEngine.vyxhMoXTvD()
    at FastReport.Engine.ReportEngine.PLiKTkb6F(ReportPage )
    at FastReport.Engine.ReportEngine.sIBSXiMcT(Boolean , Boolean , ReportPage )
    at FastReport.Report.Prepare(Boolean append)
    at FastReport.Report.Prepare()
    at AxMUPd3GmP2LsD21tJe.HDAvfI3ulEnI6JrqKyR.5AG75Lqn0()

Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.

Home • FastReport .NETComment As ...

© Vanilla Keystone Theme 2024