By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,874 Members | 1,058 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,874 IT Pros & Developers. It's quick & easy.

Multiple Select listbox and sql stored procedure -Help needed

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a

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

P: n/a

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.