Error while passing multiple values to single parameter in Mysql

Question

Hi,

     I am using Mysql and while writing the below query I get an error

="select * from Table1 whereParameter in "+"('"+Join(Parameters!Parameter1.Value, "','") + "')"+" "

and the error is

An error has occurred during report processing.
Cannot set the command text for data set 'dataset1'.
Error during processing of the CommandText expression of dataset
'dataset1'.

I have used the same query in other reports it is working fine there.What could be the reason?

Answer 1

Hi,

Do as per below:

1. The dataset query will be :

="select * from Table1 where @Parameter2 in (@parametr3)

2. Open the Dataset property go to the Parametrs tab and set an expression for @parametr3 as:

=Join(Parameters!Parameter1.Value, ",").

Hope this will work.

Thanks,

Rana

Answer 2

So you could answer your own question here's how you troubleshoot:

1. Remove (cut) your query expression from your dataset.

2. Create a new textbox in your report.

3. Enter (paste) the expression from step 1 into this textbox.

4. Run your report and you will see what the full sql string that is being formed.

5. Adjust/Fix what you see is wrong.

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 3

Hi Madhavib,

Please try the expression below:
="select * from Table1 where Parameter in"+"('"+Join(Parameters!Parameter1.Value, "','") + "')"+" "
OR
="select * from Table1 where Parameter in('"+Join(Parameters!Parameter1.Value, "','") + "')"

If you have any questions, please feel free to ask.

Regards,

---

Charlie Liao
TechNet Community Support

Answer 4

Hi Charlie,

         I have tried both of your option but still I am getting this error:

"An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for dataset.

Error during processing of the CommandText Expression of dataset."

The same query when I have written in other report it is working fine.

I have tried Krootz method also but the textbox is only displaying ERROR.

Please help me out with this.

Thanks

Answer 5

Hi,

can you copy/paste here ctly the expression you are using? I'm assuming you dont have a table called Table1 and a field name in Table1 named Parameter.

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 6

Hi Krootz,

                 Table 1 does exist as other reports are running on the same and I have created these three parameter in my report.

="select * from Table1 where WW in "+"('"+Join(Parameters!Parameter1.Value, "','") + "')"+"  and Team in "+"('"+Join(Parameters!Parameter2.Value, "','") + "')"+" and Model in "+"('"+Join(Parameters!Parameter3.Value, "','") + "') "+""

Thanks

Answer 7

Hi,

First, answer these quetsions:

1. Are the fields WW, Team and Model string/text fields or numeric?

2. Are all your parameters Parameter1, Parameter2, Parameter3 multi-value parameter?

3. If all fields are text and all are multi value, then change your query to this, you have too many unnecessary + in your original:

="select * from Table1 where WW in ('"+Join(Parameters!Parameter1.Value, "','") + "')  and Team in ('"+Join(Parameters!Parameter2.Value, "','") + "') and Model in ('"+Join(Parameters!Parameter3.Value, "','") + "')"

4. If any of your field is numeric, you have to change the Join syntax to this: For example, if Model is numeric, then

....Model in (" + Join(Parameters!Parameter3.Value,",") + ")"  -- you have to take out the single quotes ' from the expression.

5. If your parameter is not multi value, then don't use JOIN, 

...Team='" + Parameters!Parameter2.Value + "'"

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 8

Hi Krootz All the three parameter are varchar type. WW and Team are single valued while model is multiple valued parameter. Now which one to follow from your answer? Thanks

Answer 9

Hi Krootz,

             Your method worked and now my report is running thank a lot..........................