.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to use LIKE and IN in SQL with a multi-select parameter

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :Windows Application

I have a report parameter, @antibiotics, which presently allows the user to select one value from a dropdown list.  I then use this parameter to search a string column using LIKE.  So far, this all works great.   So my WHERE statement looks like this presently:

Where Notes LIKE '%' + @antibiotics + '%'

Now I want to allow users to select multiple values for @antibiotics.  So I made the parameter multi-select. 

But now I am lost on how to construct my WHERE statement.  I tried using combinations of LIKE and IN but nothing works. 

It works if I only select one value from my dropdown list.  But if I select more than one value, I get a syntax error near "," or something.  Does anyone know how I can accomplish this?




View Complete Post

More Related Resource Links

NULL label doesn't appear on drop down or multi-select parameter.

Hi, I am using SSRS 2005 SP3. I have the following query that I use for my parameter: SELECT 0 AS c1, 'NOT Specified' AS c2 UNION SELECT 1 AS c1, NULL AS c2 UNION SELECT 2 AS c1, 'Test 1' AS c2 UNION SELECT 3 AS c1, 'Test 2' AS c2 Let's call my parameter prm1. The type of prm1 is string and it has "Allow null value" and "Allow blank value" options ticked. C1 is the value field and C2 is the label field for prm1. When I preview the report, and I didn't see NULL in my drop down list,  but that record has been shown as 1 (which is the value). I am wondering if this is a normal behavior of SSRS? It's not a big deal. I can replace NULL with something else, but I am just curious. Cheers, Uzzie

ReportViewer 2008 multi-select parameter alignment

We are using SSRS 2008 SP1 & ReportViewer 2008 SP1. The multi-select parameter options are left aligned in the drop-down listbox on the Report Server but when run thru ASP.Net & reportViewer, they are aligned in the center of the drop-down list. This is very problematic because the width of the drop-down is also narrow compared to the text size of the fields. I tried wrapping the ReportViewer in a Div tag (<div style="vertical-align: left; text-align: left">), but still no joy.  Would appreciate any help thanks.

ObjectDataSource CancelSelectOnNullParameter="false" // Select all when select parameter is null or


Hi ! I am using an objectdatasource in my project and I want to select all rows when the selectparameter value is empty ! I did the following but I am failling;
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DataObjectTypeName="TugberkUgurluCom_TransferModule.TransferPriceDetailsAll" SelectMethod="GetAllDataFromPricesForControl" TypeName="TugberkUgurluCom_TransferModule.TransferPriceDBComponent" UpdateMethod="UpdateTransferPrices">
    <asp:ControlParameter ConvertEmptyStringToNull="true" ControlID="DropDownList1" Name="TpDestID" PropertyName="SelectedValue" Type="

Use BDC data in list as multi-select column

I have a document library that uses values from another system for its metadata.  Right now, we copy the values from the primary system into SharePoint so users can choose them.  I want to find a way to use the BDC connection that I have set up to automatically pull those values.  The problem is that the current metadata is set to multi-select.  So, I have a file that is in the library and it can be used for multiple clients - clients is a column in my library and users can select many entries.  When I set up a column to pull BDC data, it only allows single entry.  Does anyone have any ideas?

DVWP connections with multi-select lookup columns

I have a question concerning the Data View Web Part using SharePoint Designer 2007.   I have two list (A and B). List A has a lookup column (called B-items) with multiple selections to items in List B(using the Title column from B).   I create a web part page and insert a data view of using List A. I create another data view with data from List B. Next, I make a Web Part Connection between the two data views with A passing B-items column as parameter to List B data view. I then create a filter on the List B data view with a comparison of List B’s Title column equal to the parameter of List A’s B-items value when a user selects an item from List A data view. The problem is nothing appears in List B’s data view.   When I try the reverse of the above scenario it works fine.     I understand that this is properly functionality, but is there a way to achieve my first scenario? If so, how can it be accomplished?   I have looked through the web for help and found the answer here http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/749b7477-f37f-4724-94b3-b6ace770e73a seems to be what I’m looking for. However, I am unsure how to implement his code into the web part page.   I am a worse than a novice with xsl so if anyone gives an example I would greatly appreciate step by step on what code is needed a

multi-valued parameter

I need to do something like this: DECLARE @SalespersonCode SET  @SalespersonCode = '''smith'' + ',' + ''jones'''@SalespersonCode = '''SMITH'',''JONES'',''ADAMS''' SELECT * FROM Salesperson WHERE SalespersonCode in (@SalespersonCode) but I cannot get any results to return.  Is there a way to put multiple parameters in a single variable to use with in IN statement? John Schroeder* FROM Salesperson@Salesperson Varchar(100)

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

Multi select control does not work with Infopath. :(

I create new lookup column that allows multiple values.  The important part is the column is the Title(linked to item) so the item is shown as Hyperlink item to the actual item.  This works fine, but then I would like to fine tune my form with infopath.  When I open this form it complains about these columns, basically will not let me open the form without first deleting these lookup columns.  So I then try the other way I create a lookup column from infopath and this work fine, the only problem being is these items no longer so up as hyperlinks to the original items.  This is a major bumma, any one have any suggestions to get this working?

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Enter or Select value for parameter

Hi all, I have a parameter 'ProjectNumber' and I want the user to be able to select a Project Number from a drop-down list OR type in the Project Number (in case of a new Project that needs to be added to the database). Is this possible?  Everything I've tried gives me the impression that it has to be one or the other - type it in or select from list. Thanks for your assistance.

Passing multi values to another multi value parameter

Hi, my requirement is as follows I have a parameter1 in which i populate values from database. the user can select multiple values from parameter1. after the parameter1 i have another parameter2 which is also mutliselection value. i,e depending on the selection of parameter i need to populate remaining values in Parameter2 (which is also a multi selection value). i have splited the values from parameter1 and passed the value of parameter1 (parameter1.value) to parameter2. but i am getting the following error An expression of non-boolean type specified in a context where condition is expected, near ',' incorrect syntax near the keyword 'ELSE' can anyone help me in this Thanks, Rajesh Peethala

Multi Select Sorting help

I need help with a bit complex JavaScript. This is what I am trying to do: I have a database table with id and title. I am populating the multi select list with the values "id + title" (ex: 1234 Title1) What I need to do is sort the list based on the items selected. There is a sort button which when a user clicks after selecting his titles should push the selected items to the top of the list and sorted and the list that's not selected must also be sorted based on the title. For ex if I have these in the select list:my options will look like this <option value="122" id="001">122 Amazing Facts</option><option value="890" id="002">890 Famous Seven</option><option value="345" id="003">345 Niagara Falls</option>......<option value="100" id="007">100 Zimbabwe</option>122 Amazing Facts 890 Famous Seven 345 Niagara Falls 879 Spain Champions 865 Trinidad & Tobago 213 Victoria Falls100 Zimbabwe They are all sorted on the title (or the ID since I am populating them based on the titles). This will be the first pass when I populate them. Now the user selects 890 Famous Seven, 213 Victoria Falls, 100 Zimbabwe and hits the sort button. The result should look like this 890 Famous Seven 213 Victoria Falls 100 Zimbabwe 122 Amazing Facts 345 Niagara Fa

Form Display is very slow to display, (using large multi-select lists)

Hi, I am looking for some suggestions on how to speed up a few very slow forms. We have created some Leaver and Joiner type forms for our IT guys. They contain some lookup data , and we need to pick multiple values from a hidden list. Unfortunately this (Systems/Roles) list is large (3000 rows and growing). On a couple of forms there are 2 of these multi-select boxes, and things are getting pretty slow to display. Any suggestions? ---------- Background Info / things I have tried: The forms are based on Site content types and Site Columns. A set of 5 Combo boxes is slower than one Multi-select list. SPDesigner is available but I do not have Central Admin rights I am not alowed to write/install custom code on the server. (Visual Studio code / Codeplex code / extra Features etc) :-( I have had to resize the Multi-select list, so am using the SPServices JQuery call to make the multi-select box wide enough for the text. But this is extra overhead so I might have to back that out / replace with a fixed width for the multiselect box (if I can find it's id, so I can do that in Javascript). Filtering the Multi-select box is possible, but only after the page has initially loaded (it seems) (again I tried the SPServices Jquery call) The Company intend to add more Content types, so I am trying to avoid a custom Newform.aspx for each type (Leaver / New Joiner / Transfer/ C

API SSRS 2008 for php multi-select

Hi, I use API SSRS 2008 for php .  We have create with a report builder  a report to use multi-select. In the manager web SSRS we can use  multi-select filter option. But with API i don't see the possibility to use multi-select. Is-it possible ? Regards Marc       

Parameter For Select Statement

I Am Working On A Project,I am using MVC, VS 2010, SQL 2005, Model Is Created Using LINQ TO SQL (.dbml)Table Joining Code in My Controller Looks Like Thispublic ActionResult Index() { var DataContext = new SMSAPPDataContext(); var result = from t in DataContext.tbl_teachers from e in DataContext.tbl_teacherenrollments from b in DataContext.tbl_batches from c in DataContext.tbl_classes from s in DataContext.tbl_subjects where t.Teacher_ID == e.Teacher_ID && e.Batch_ID == b.Batch_ID && b.Class_ID == c.Class_ID && e.Sub_ID == s.Sub_ID && c.Class_Name == "TEN" && c.Class_Section == "C" && t.Teacher_Name == " ABC " select c; return View(result.ToList()); } This Table Join QUery Gives Me Data From On Table with Select Statement.And My Inherit Page In View Looks Like This<%@ Page Title="" Language=&quo

How can I select Table by pussing parameter table name


How can I Create Select store proc for select Table but I will send the Table Name in Parmeter which table I want Like below.



Proc SelectAll







multi parameter sub report



i have a sub report that i want to pass the same multi-select parameter through as in the main report itself.

i have set the parameter in the sub report exactly the same as in the main report but i get an error.

also, in the main report i have clicked on the properties of the sub report and selected the parameter, the only difference is that the paramter automatically end with "(0)" and i dont know what this means.

Any help would be much appreciated,





ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend