I would like to ask for the method to pass all the values in a listbox as Multi Value parameters to objectdatasource. When I search from the web, the parameters sertting from listbox is a bit tricky as it relates to multi value selection. Some suggested
one can loop through all the listbox value as a string after setting the SelectionMode to 'Multiple'. But actully I am quite confused about the setting in objectdatasource. What is the appropriate parameter source in objectdatasource if we pass it as a string?
Or it's there any way to control a particalur listbox item to selected mode? E.g. Something like 'listbox.selected'. Thanks very much!
We can also create a stored procedure using dynamic SQL.
For example,
CREATE PROCEDURE [dbo].[GetSuppliers]
@Countries nvarchar(max)
AS
BEGIN
DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT [CompanyName], [SupplierID], [Country] FROM [Suppliers]
WHERE [Country] IN ('+@Countries+')'
EXEC (@SQL)
END
In this case, the format of the comma delimited string should be "'abc', 'bcd'":
now use/ create the split values function into your db...
-- create this split value function into your database
CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
-- after creating function
-- use below code to fetch record
Select * from YourTableName where YourTableId in (Select items from dbo.Split(@Parameter,','))
-- @Parameter pass as parameter
-- , comma as separated
kennyli323
Member
3 Points
17 Posts
Pass Multi Value parameters in listbox to objectdatasource
Sep 28, 2009 02:40 AM|LINK
I would like to ask for the method to pass all the values in a listbox as Multi Value parameters to objectdatasource. When I search from the web, the parameters sertting from listbox is a bit tricky as it relates to multi value selection. Some suggested one can loop through all the listbox value as a string after setting the SelectionMode to 'Multiple'. But actully I am quite confused about the setting in objectdatasource. What is the appropriate parameter source in objectdatasource if we pass it as a string? Or it's there any way to control a particalur listbox item to selected mode? E.g. Something like 'listbox.selected'. Thanks very much!
Jian Kang - ...
All-Star
33132 Points
2465 Posts
Re: Pass Multi Value parameters in listbox to objectdatasource
Sep 29, 2009 09:04 AM|LINK
Hi,
We can generate a comma delimited string of values and pass it as the parameter value.
string strCountries = ""; foreach (ListItem li in ListBox1.Items) { if(li.Selected) { strCountries += "," + li.Value; } } strCountries = strCountries.Substring(1);Then we create a split function in SQL Server and join with that function. Please refer to the following thread for more information:
http://forums.asp.net/t/1386633.aspx
We can also create a stored procedure using dynamic SQL.
For example,
CREATE PROCEDURE [dbo].[GetSuppliers] @Countries nvarchar(max) AS BEGIN DECLARE @SQL nvarchar(max) SET @SQL = 'SELECT [CompanyName], [SupplierID], [Country] FROM [Suppliers] WHERE [Country] IN ('+@Countries+')' EXEC (@SQL) ENDIn this case, the format of the comma delimited string should be "'abc', 'bcd'":
string strCountries = ""; foreach (ListItem li in ListBox1.Items) { if(li.Selected) { strCountries += ",'" + li.Value+"'"; } } strCountries = strCountries.Substring(1);Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
yrb.yogi
Star
14460 Points
2402 Posts
Re: Pass Multi Value parameters in listbox to objectdatasource
Sep 29, 2009 09:12 AM|LINK
first of all make a string delimited by comma or anyother delimiter.. as below
string strCountries = ""; foreach (ListItem li in ListBox1.Items) { if(li.Selected) { strCountries += "," + li.Value; } } strCountries = strCountries.Substring(1);now use/ create the split values function into your db...
-- create this split value function into your database CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end -- after creating function -- use below code to fetch record Select * from YourTableName where YourTableId in (Select items from dbo.Split(@Parameter,',')) -- @Parameter pass as parameter -- , comma as separated.Net All About