sql query parameter syntax

cohalloran

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

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

Thanks,

I'm getting this error

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

AlexTZ

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

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

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

To access a datasource, use the following code:

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

pinbot

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

Found it in an earlier post of mine.

Thanks.

Magezi Sagesse

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!!!