parameter cannot set the command text for dataset

Hello

I get the following error in a very quick test system i created:

An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'test'.

Error during processing of the CommandText expression of dataset 'test'.

My sql is:

= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)

There is nothing obviously wrong in the code as far as i can see

The parameter 'test1' is of type 'string'

The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'

I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine

Thanks

t.test from test?

You tried running this in sql?

|||

Yes, it works fine in sql if you strip out the bits that obviously wont work in query analyser

this works:

= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = 1" )

this does not:

= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = " & Parameters!test1.Value)

why?

there is a parameter called test1

|||

Found the answer!

You have to have another table with a different name into which the parameter links (in the section 'available values' / 'from query')

I created another table called test2, et voila! it works

Paging

I am getting an "An unexpected error occurred in Report Processing" error
when attempting to navigate to the next page in a report. The first page of
the report comes up in the browser OK but produces the error when clicking on
the "next" arrow. The main report uses a subreport that accepts two
parameters, one of which can be null. The subreport is used to display
subtotals at the 1st and 2nd grouping level. Each report runs fine
standalone and everything works as expected in the designer. The number of
rows returned by the stored procedure is small (29). I have reviewed
recommended log files but nothing stands out. Is there more I can do to
troubleshoot this problem. TIA
DIt apprears that the subreport is the problem as paging works if the
subreport is removed. The subreport has 23 columns and is placed into a
merged cell in the main report, also 23 columns. Are there limitations on
the use of the subreport?
dp
"dp" wrote:
> I am getting an "An unexpected error occurred in Report Processing" error
> when attempting to navigate to the next page in a report. The first page of
> the report comes up in the browser OK but produces the error when clicking on
> the "next" arrow. The main report uses a subreport that accepts two
> parameters, one of which can be null. The subreport is used to display
> subtotals at the 1st and 2nd grouping level. Each report runs fine
> standalone and everything works as expected in the designer. The number of
> rows returned by the stored procedure is small (29). I have reviewed
> recommended log files but nothing stands out. Is there more I can do to
> troubleshoot this problem. TIA
> D