SSRS Report - Parameter for dataset

Question

I have a dataset where I am declaring a parameter. It looks like this:

where @pNomActif is a report parameter: =Parameters!pNomActif.Value

It works fine in the Builder, but when running i I am getting this error:
System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@pNomActif"

SQL expression for the dataset is this:
select * from [tablename]
where Actif = @pNomActif

Any idea?

Thanks

Answer 1

Hi @mark goldin ，
What data source are you using? Not all data sources support parameters. Some OLEDB providers support unnamed parameters and not named parameters. Parameters are passed by position by using a placeholder in the query. The placeholder character is determined by the syntax that is supported by the data provider.
If using sql data source, please check if you have the parameter name mapping to the parameter label , it would be that you have a param1 prompt while have other value for the parameter name.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @mark goldin

You don't need to add parameter in dataset， you can just use it in the dataset

select * from [tablename]  
where Actif = (@pNomActif)  

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @mark goldin

Here is a link about how to create a parameter ，you can take a look ：tutorial-add-a-parameter-to-your-report-report-builder

In general，we don‘t create parameter in dataset ，but in Parameters folder . I also have an assumption that your Report Builder goes wrong，maybe you can reinstall it ，here is the download link ：https://www.microsoft.com/en-us/download/details.aspx?id=53613

Best Regards,
Isabella

Answer 4

Hi @mark goldin

Yes，it will create the dataset parameter and report parameter automatically

Here is my test screenshot ，I didn't get error ，my report can run ok .

Can you paste your dataset query here，maybe there is mistake in your query statement ，please check out this thread：https://stackoverflow.com/questions/7181976/must-declare-the-scalar-variable

Best Regards,
Isabella