ListBox Selected Items as parameter for Stored Procedure

[JoeZim]

I am trying to figure out how to pass multiple ListBox items into a stored procedure. What would be the best way to attack this? I'm currently trying to build a dataset based on the selected items, then somehow pass that dataset into the stored procedure. Here is what I have thus far, just a loop through the list box items to see what has been selected:

Dim counter As Integer
Dim ds_selected_items As DataSet
Dim product_code As DataColumn

For counter = 0 To (ListBox1.Items.Count - 1) Step 1
If ListBox1.Items(counter).Selected = True Then

HOW CAN I POPULATE A DATASET AT THIS POINT???

End If
Next

Then, how can I get the list of selected items to be used as parameters in a Stored Procedure?

 

[ca8msm]

You can't pass a DataSet to a stored procedure. If you have multiple parameters that you want to add, simply loop through the selected items and create a New parameter on each loop.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[JoeZim]

I finally got this working! This may not be the best way, but it seems to be working well:

asp code on a button click:

Dim counter As Integer 
Dim array As String 
For counter = 0 To (ListBox1.Items.Count - 1) Step 1 
If ListBox1.Items(counter).Selected = True Then 
If counter = 0 Then 
array = ListBox1.Items(counter).Value() & ","
 
Else 
array = array & ListBox1.Items(counter).Value() & ","
 
End If 
Else 
End If 
Next 
array = KillEnding(array) ' This is a function to remove the comma at the end'
 
Dim cn As New SqlClient.SqlConnection(Application("connection")) 
cn.Open()
 
Dim cmd As New SqlClient.SqlCommand("sp_ParseArray", cn) 
cmd.CommandType = CommandType.StoredProcedure
 
Dim parm As New SqlClient.SqlParameter("@Array", SqlDbType.VarChar, 1000) 
parm.Value = array
 
cmd.Parameters.Add(parm)
 
Dim parm1 As New SqlClient.SqlParameter("@Separator", SqlDbType.VarChar, 1) 
parm1.Value = ","
 
cmd.Parameters.Add(parm1)
 
cmd.ExecuteNonQuery()
 
cn.Close()
 
 
 
Function KillEnding(ByVal sItem As String) 
Return sItem.Substring(0, sItem.Length - 1) 
End Function

sql stored procedure: got this from

http://www.sqlteam.com/item.asp?ItemID=637

CREATE procedure sp_ParseArray
( @Array varchar(1000),
@separator char(1) ) 
AS
-- Created by graz@sqlteam.com 
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
 
-- Delete the current contents of table where array values will be placed
delete test_prod_code
 
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
 
-- For my loop to work I need an extra separator at the end.  I always look to the
-- left of the separator character for each array value
set @array = @array + @separator
 
-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0 
begin
 
  -- patindex matches the a pattern against a string
  select @separator_position =  patindex('%' + @separator + '%' , @array)
  select @array_value = left(@array, @separator_position - 1)
 
  -- This is where you process the values passed.
  -- Replace this select statement with your processing
  -- @array_value holds the value of this element of the array
--INSERT INTO test_prod_key(product_code)
  --select Array_Value = @array_value
INSERT INTO test_prod_code (product_code)
       VALUES (@array_value)
 

  -- This replaces what we just processed with and empty string
  select @array = stuff(@array, 1, @separator_position, '')
end
 
set nocount off
GO

That stored procedure creates a table with the array items. I then use this table in another stored procedure to get the results I am looking for.