Dynamic sql in dataset not working

I'm trying to have the sql in my dataset dynamic based on report parameters. I expected this to work, but doesn't. Dataset:

="select item from table where " + Parameters!SearchType.Value + " = " + Parameters!Filter.Value + "order by item"

Is this possible or do I need to take a different approach (and what would that approach be)

Thanks,

Derek

Try the expression in a report textbox first and look at the generated query commandtext in preview to see if it is correct.

From the expression you posted it looks like you generate invalid syntax for the query string because there seems a missing blank before "order ..."

-- Robert

|||

Hi Robert,

It is possible but make sure thet you have the filter value inside a single quote (') so that SearchType of any datatype will work. so your expression should look like this:

="select item from table where " + Parameters!SearchType.Value + " = '" + Parameters!Filter.Value + "' order by item"

(also note the blank space before order by clause)

Shyam

|||

I've been able to get this working for a dataset accessing sql server, but i'm having trouble getting it to function with an oracle datasource. Should this work for Oracle or do I need to head down a different road - maybe plsql for this?

Thanks!