?
Solved

listbox mutliselect

Posted on 2009-04-29
4
Medium Priority
?
185 Views
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
0
Comment
Question by:Mr_Shaw
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Alpha Au earned 2000 total points
ID: 24258699
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)
) 
AS  
BEGIN 
	While (Charindex ( @SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (strValue)
		Select 
			strValue = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))
 
		Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
	End
 
	Insert Into @RtnValue (strValue)
	Select strValue = ltrim(rtrim(@RowData))
	Return
END
 
select * from table1 
where AND exists(SELECT 1 FROM dbo.fnSplitCommaList(@strDiagramRef,',') where strValue = table1.field1)

Open in new window

0
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24258708
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

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24258709
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];                          
    }
0
 

Author Closing Comment

by:Mr_Shaw
ID: 31575853
thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

839 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