469,626 Members | 825 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Multiple Select listbox and sql stored procedure -Help needed

Hi,
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select any number of items in listbox, I have to take
value from items and
pass it to stored procedure to extract a dataset back.

1.What should i do while passing the multiple selected values?
2.Can i use 'in' clause in SQL procedure like

eg:Select a.xxx from a where a.yyy in @y
@y is the multiple selected values from listbox.
3.How to concatenate and send to SQL Procedure.

Please help me

Sep 4 '06 #1
2 7709

You can't send arrays into a stored procedure in this way (at least MSDE
2000 you can't). I'm not sure about 2005, but I don't think this facility
has been added, at least not with Vanilla SQL. What I do is construct an
NTEXT field and then parse it into a temporary table in the stored
procedure, then do a join with that temporary table. Code below. Remember
that your NTEXT items are space separated. You can change to comma or
Umlaught (!) separated or whatever you want. If your have a small maximum
number of items likely to be selected, you could I suppose have a different
parameter for each one. But for the general case this is the only way I
know to do it:


' Collect the items into NTEXT format ("12034 23245 5435 5353", etc. )

Dim theBuilder As New StringBuilder

For i As Integer = 0 To Items.Count - 1

theBuilder.Append(Items(i).ToString)
theBuilder.Append(" ")

Next

' Remove trailing space.

theBuilder.Remove(theBuilder.Length - 1, 1)

.....

' Send the NTEXT field into the stored procedure......

Command.Parameters.Add("@In_Items", SqlDbType.NText).Value =
theBuilder.ToString()
Command.Parameters("@In_Items").Direction = ParameterDirection.Input

.......


' Here is how you join in the stored procedure:

SELECT * FROM MyTable INNER JOIN dbo.Split ( @In_Items ) i ON MyTable.Value
= i.value


..............

' and here is a stored procedure - modify to suit. Mine splits an NTEXT of
space separated integers into ID's, so I can join on that field. Yours
might be text values, I don't know :).....
CREATE FUNCTION dbo.Split (

@list NTEXT
)

RETURNS @Table TABLE ( listpos INT IDENTITY(1, 1) NOT NULL,
number INT NOT NULL) AS
BEGIN

DECLARE @pos INT,
@textpos INT,
@chunklen SMALLINT,
@str NVARCHAR(4000),
@tmpstr NVARCHAR(4000),
@leftover NVARCHAR(4000)

SET @textpos = 1
SET @leftover = ''


WHILE @textpos <= datalength(@list) / 2
BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
@chunklen))

SET @textpos = @textpos + @chunklen
SET @pos = charindex(' ', @tmpstr)

WHILE @pos 0
BEGIN

SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @Table (number)
VALUES
(convert(int, @str))

SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1,
len(@tmpstr)))
SET @pos = charindex(' ', @tmpstr)
END

SET @leftover = @tmpstr

END

IF ltrim(rtrim(@leftover)) <''

INSERT @Table (number)
VALUES
(convert(int, @leftover))

RETURN


END

Sep 4 '06 #2

Robinson wrote:
You can't send arrays into a stored procedure in this way (at least MSDE
2000 you can't). I'm not sure about 2005, but I don't think this facility
has been added, at least not with Vanilla SQL. What I do is construct an
NTEXT field and then parse it into a temporary table in the stored
procedure, then do a join with that temporary table. Code below. Remember
that your NTEXT items are space separated. You can change to comma or
Umlaught (!) separated or whatever you want. If your have a small maximum
number of items likely to be selected, you could I suppose have a different
parameter for each one. But for the general case this is the only way I
know to do it:


' Collect the items into NTEXT format ("12034 23245 5435 5353", etc. )

Dim theBuilder As New StringBuilder

For i As Integer = 0 To Items.Count - 1

theBuilder.Append(Items(i).ToString)
theBuilder.Append(" ")

Next

' Remove trailing space.

theBuilder.Remove(theBuilder.Length - 1, 1)

....

' Send the NTEXT field into the stored procedure......

Command.Parameters.Add("@In_Items", SqlDbType.NText).Value =
theBuilder.ToString()
Command.Parameters("@In_Items").Direction = ParameterDirection.Input

......


' Here is how you join in the stored procedure:

SELECT * FROM MyTable INNER JOIN dbo.Split ( @In_Items ) i ON MyTable.Value
= i.value


.............

' and here is a stored procedure - modify to suit. Mine splits an NTEXT of
space separated integers into ID's, so I can join on that field. Yours
might be text values, I don't know :).....
CREATE FUNCTION dbo.Split (

@list NTEXT
)

RETURNS @Table TABLE ( listpos INT IDENTITY(1, 1) NOT NULL,
number INT NOT NULL) AS
BEGIN

DECLARE @pos INT,
@textpos INT,
@chunklen SMALLINT,
@str NVARCHAR(4000),
@tmpstr NVARCHAR(4000),
@leftover NVARCHAR(4000)

SET @textpos = 1
SET @leftover = ''


WHILE @textpos <= datalength(@list) / 2
BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
@chunklen))

SET @textpos = @textpos + @chunklen
SET @pos = charindex(' ', @tmpstr)

WHILE @pos 0
BEGIN

SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @Table (number)
VALUES
(convert(int, @str))

SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1,
len(@tmpstr)))
SET @pos = charindex(' ', @tmpstr)
END

SET @leftover = @tmpstr

END

IF ltrim(rtrim(@leftover)) <''

INSERT @Table (number)
VALUES
(convert(int, @leftover))

RETURN


END

hi Robinson,
thank you.Its working

Sep 6 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Lauren Quantrell | last post: by
2 posts views Thread by Patrick Olurotimi Ige | last post: by
8 posts views Thread by Steve Schroeder | last post: by
16 posts views Thread by Randy Harris | last post: by
2 posts views Thread by =?Utf-8?B?VGVycnk=?= | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.