listbox mutliselect

Posted on 2009-04-29
Last Modified: 2013-12-17
Has anybody ever created a Listbox with a multiselect. Then managed to pass the selected item values accross to a stored procedure as OR statements.

For example the selection is 1,2,3 therefore the SQL statement will be 1 or 2 or 3
Question by:Mr_Shaw
    LVL 7

    Accepted Solution

    you can pass the list as varchar to sp,
    then use a function to convert it as a table variable storing all the value
    and use exists in your select statement in stored procedure.

    the code snippet include a sql function and a sample select statement.

    CREATE FUNCTION [dbo].[fnSplitCommaList]
    	@RowData nvarchar(3000),
    	@SplitOn nvarchar(5)
    RETURNS @RtnValue table 
    	strValue varchar(100)
    	While (Charindex ( @SplitOn,@RowData)>0)
    		Insert Into @RtnValue (strValue)
    			strValue = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))
    		Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
    	Insert Into @RtnValue (strValue)
    	Select strValue = ltrim(rtrim(@RowData))
    select * from table1 
    where AND exists(SELECT 1 FROM dbo.fnSplitCommaList(@strDiagramRef,',') where strValue = table1.field1)

    Open in new window

    LVL 7

    Expert Comment

    typo... please refer to the following for the select statement
    select * from table1 where 
    exists(SELECT 1 FROM dbo.fnSplitCommaList(@selectedList,',') where strValue = table1.field1)

    Open in new window

    LVL 53

    Expert Comment

    The easiest will be to loop the selecteditems and store it in a string to pass it as a parameter
    string selitems = "";
    for(int cnt =0;cnt <= listBox1.SelectedItems.Count -1;cnt++)
          selitems  = selitems  + "," + listBox1.SelectedItems[cnt];                          

    Author Closing Comment


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Flash ( has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
    Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now