sql query parameter syntax

cohalloran · June 2009

Hi,

Fastreport.net.1.1.1

I have added a sql query in datasource that looks as follows

select * from TABLE
where COL1 IN ('A','B','C')

[COL1 is a varchar field]

Now I want to use a paramater for the limits, I tried this:

select * from TABLE
where COL1 IN @param1

where @param1 is a Varchar with this value ('A','B','C')
but it is the incorrect syntax

Can a datasource sql query contain a WHERE IN CLAUSE ?

Thanks

AlexTZ · June 2009

Hello,

You can't supply several values using one sql parameter. You may try to build sql clause dynamically, in the report script. For example, write the following code in your report's StartReport event handler:

Â Â Â Â private void _StartReport(object sender, EventArgs e)
Â Â Â Â {
Â Â Â Â Â Â Table1.SelectCommand = "select * from MyTable where MyColumn in (" + your_dynamic_clause + ")";
Â Â Â Â }

To create this event handler, select "Report" item in the "Properties" window's combobox, click "Events" button and double-click the "StartReport" event.

cohalloran · June 2009

Thanks,

I'm getting this error

Error CS0117: 'FastReport.Table.TableObject' does not contain a definition for 'SelectCommand'

AlexTZ · June 2009

In my example, Table1 is the name of your data table (you can see it if you select the table in the "Data" window).

cohalloran · June 2009

Thank You,

Yes I had the incorrect name.

There is 'Tablename' in the Data Properties and 'Name' in the Design Properties and they were different.
I changed Name in the Design Props to Table1

pinbot · June 2009

hmm.

I can't get this to work.

I have an added Query that I added through "Add SQL Query". Named it Table1 but when I try to access it via script, I get the error "Table1 does not exist in the current context"

Bryan

AlexTZ · June 2009

To access a datasource, use the following code:

TableDataSource datasource = Report.GetDataSource("YourDataSource_Alias") as TableDataSource;

pinbot · June 2009

That's what I ended up doing and it's working fine.

Found it in an earlier post of mine.

Thanks.

Magezi Sagesse · December 2013

I have the same problem I don't know how to pass a parameter to my query.

I have a problem with passing a custom sql command, I don't know how to pass parameters to tabledatasource selectcommand as it can be done with oledbcommand.parameters.add(.....), working without that is handling error when I the where condition data has an apostrophy.

Example: table.selectcommand="select * from listetous where objsup='l'homme'" with oledbcommand it works when I use parameter :
oledbcommand.commandtext= ="select * from listetous where objsup=a"
oledbcommand.parameters.add("a",oledbvarchar,255).value= "l'homme"

Can someone help me showing me how to use parameters with tabledatasource?

thank you!!!

sql query parameter syntax

Comments

Leave a Comment