Passing ListBox multiselect to Gridview using Store Proc

[CJwater]

I'm trying to populate a gridview depending on 3 listboxes using "multi selection" to call a stored proc that expects 3 parameters that can be nulls if there's no data. (This works in ACCESS and I'm convering this application to the ASP.net and having trouble.)

Stored Proc Info:
CREATE PROCEDURE [dbo].[Web_GetPOItems]
/*** EXPECTING 3 strings in this format '738,740,742' ***/
(
@firstVal varchar(500) = NULL,
@SeconVal varchar(1000) = NULL ,
@ThirdVal varchar(1500) = NULL

)
AS
/*** Function place quotes to sepearate element for item in clause as ('','', ) ***/
set @a =@firstVal
set @a=replace (@a, ',' , ''',''')

----------------- ASP code ---------------------
NOTE: Call to Stored Proc in ASP when configuring Data Source with 3 default values work.
But not if I input DBnull as the default values
Code below "does not show" the grid at run time
-------------------------------------------------

This is what I have so Far:

Dim ConnectionString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("DATAConnectionString").ConnectionString
Dim con As New Data.SqlClient.SqlConnection(ConnectionString)
Dim cmd As New Data.SqlClient.SqlCommand("Web_GetPOItems", con)

LabelMsg.Visible = False
LabelMsg.Text = ""


Dim SQLString1 As String
Dim Item As ListItem
SQLString1 = ""
If lstSelected.SelectedIndex > -1 Then

For Each Item In lstSelected.Items
If Item.Selected Then
SQLString1 = SQLString1 & Item.Text + ","
End If
Next
SQLString1 = Left(SQLString1.Trim, SQLString1.Trim.Length - 1)

End If

cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Clear()
' first ID
cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@firstVal ", Data.SqlDbType.VarChar, 500))
cmd.Parameters("@firstVal").Value = Trim(SQLString1)
' cmd.Parameters("@firstVal ").Value = Utility.GetListControlSelectedValues(lstSelected)
' item
'If Len(TextBoxItem.Text) > 0 And TextBoxItem.Text <> "ITEM" Then

cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@SeconVal", Data.SqlDbType.VarChar, 1000))
cmd.Parameters("@SeconVal").Value = DBNull.Value
cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@ThirdVal", Data.SqlDbType.VarChar, 1500))
cmd.Parameters("@ThirdVal").Value = DBNull.Value


Try
con.Open()

cmd.ExecuteNonQuery()
GridView1.DataBind()


Catch ex As Exception
LabelMsg.Text = ex.Message
LabelMsg.Visible = True
Finally
con.Close()
End Try

Can anyone tell me what I'm doing wrong?
Thank You
CJ

 

[jmeckley]

are you sure you want the diplayed text, and not the value of the selected items?

 SQLString1 = SQLString1 & Item.[COLOR=red]Text[/color] + ","

 SQLString1 = SQLString1 & Item.[COLOR=blue]Value[/color] + ","

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190