INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Removing Outer Single Quotes for WHERE IN Clause from Join(Parameters)

Removing Outer Single Quotes for WHERE IN Clause from Join(Parameters)

(OP)
Hello,

I am passing a multi-valued parameter into a dataset from a TypeBusiness dropdown list:
=join(Parameters!prmTypeBusiness.Value,",")

Here is the dataset query behind the TypeBusiness dropdown parameter list:
SELECT type_bus_code, bus_desc
FROM business
WHERE (type_bus_code IS NOT NULL)

Here is the WHERE clause from the main dataset query (dsMainData) which gets the parameter values:
WHERE WEKAVG.BUSINESS_CODE IN(@prmTypeBusiness)

Here is the error I get when selecting values from the TypeBusiness dropdown list then previewing the report:
"Cannot read the next data row for the dataset dsMainData.
Conversion failed when converting the nvarchar value '1,2' to data type smallint."

The value is a small int coming from the dataset query behind the TypeBusiness dropdown list, and the data type in the column expecting this value (in the main dataset query) is also a small int.

I have tried many things, including a replace in the =join(Parameters!prmTypeBusiness.Value,","), a replace in the WHERE clause, a Split function, CAST to varchar, have researched this online and cannot solve this error.

If someone could please help me save what little gray hairs I have left I would greatly appreciate any help, suggestion or point in the right direction.

Thanks,
Larry

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close