The data extension OLE DB does not support named parameters

[Simon Dingley]

I am creating a report that gets its data from an MS Access database using
an OLE DB connection.
My report has 1 parameter which gets passed to the query.

@id =Parameters!DepartmentID.Value

My query is as follows and would work perfectly well if connecting to a SQL
database:

SELECT dpt_id AS DeptID, Line, price, SKU, StockCode
FROM vw_str_line_subreport
WHERE (dpt_id = @id)
ORDER BY Line, SKU

If I execute the query while on the 'Data' tab I get the desired results.
However, when I change back to the Layout or Preview tabs I get teh
following error:

"The data extension OLE DB does not support named parameters"
"Use unnamed parameters instead"

If anyone can advise me how to overcome this hurdle it would be a big help.

Thanks in advance.

Simon

[Bruce Loehle-Conger]

Try a ? instead. You also will need to go and map the ? to the appropriate
report parameter.
I.e.

SELECT dpt_id AS DeptID, Line, price, SKU, StockCode FROM

vw_str_line_subreport WHERE (dpt_id = ?) ORDER BY Line, SKU

Bruce L-C

"Simon Dingley" <newsg...@nospam-creativenrg.co.uk> wrote in message
news:e8x9dfMa...@tk2msftngp13.phx.gbl...

[Simon Dingley]

Thanks for the tip Bruce but how do would one go about mapping the '?' to
appropriate report parameter ?

Thanks for your help.

Simon

"Bruce Loehle-Conger" <bruce_l...@hotmail.com> wrote in message
news:u3VqDJOa...@TK2MSFTNGP10.phx.gbl...

[Bruce Loehle-Conger]

You need to be sure you understand the difference between report parameters
and query parameters. It is possible to have multiple datasets, each with
their own parameters that map to the same or different report parameters. To
see the report parameters be in the layout and report->report parameters. In
the data tab click on the ... and then the parameters tab.

Bruce L-C

"Simon Dingley" <newsg...@nospam-creativenrg.co.uk> wrote in message

news:%2394TkjO...@tk2msftngp13.phx.gbl...

[Sherry]

Bruce,
If I have OLE DB provider and have multiple data sets and all of the
parameters in the data sets refer to the same report parameter, how do
I go about doing that. Every time i put a '?' in a new dataset it
creates a new report parameter. I want to use the same report
parameters for all of the query parameters but I have no idea how to
go about doing that. Any help will be greatly appreciated since I've
been trying to resolve this for over a week!

Thanks

sherry

"Bruce Loehle-Conger" <bruce_l...@hotmail.com> wrote in message news:<#qqaXyOa...@TK2MSFTNGP12.phx.gbl>...

[Bruce Loehle-Conger]

What is happening to you is something the development environment does to
help you (which I don't like personally). When you create a query parameter
it also creates a corresponding report parameter. I do not like the names of
the report parameters because the are totally generic (at least they are
when you use a ? as the query parameter like you must do when named
parameters are not supported).

Do the following:
1. Add a new report parameter with the name you want (or you can change one
of the ones you have there). Delete all the other report parameters. Go to
the data tab and click on the ..., go to the parameters tab. Now for each of
the query parameters map it to the single report parameter.

2. Repeat for each dataset.

Report parameters can be used in multiple places. I do the above all the
time.

Bruce L-C

"Sherry" <sherry...@mitretek.org> wrote in message
news:130ca5fa.04072...@posting.google.com...