Listbox multiple insert

[Big E]

I'm using ASP.Net and SQL Server 2000. I'm trying to insert or update
records from a multiselect listbox. How can I get the contents of the
listbox if there is more than one selected and update that into the
database. But most important how do I update or insert those records.
Any articles or ideas would be appreciated.

Thanks.

Big e

[Steve Caliendo]

Dim cn As New
SqlClient.SqlConnection("Server=(local);Database=E pionDataCenter;User
Id=scaliendo;Password=scaliendo;Trusted_Connection =False;")

Dim Command As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand("", cn)

cn.open

dim i as integer

for i = 1 to listbox1.items.count

if listbox1.items(i-1).selected = true then

command.commandtext = "update yourtable set yourfield = '" &
listbox1.items(i-1) & "' where yourfieldindex = currentfieldindex"

command.executenonquery

end if

next

or build an array out of the seleceted items in the listbox first if you'd
like to run only one query.

Steve


"Big E" <no****@nospam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

I'm using ASP.Net and SQL Server 2000. I'm trying to insert or update
records from a multiselect listbox. How can I get the contents of the
listbox if there is more than one selected and update that into the
database. But most important how do I update or insert those records.
Any articles or ideas would be appreciated.

Thanks.

Big e

[Big E]

Here is what I currently have. It inserts the first record and then gives me
the error that there are too many arguments or parameters specified.

Dim cnPortal As SqlConnection = New SqlConnection(Portal)
Dim dsCommQuestion As New DataSet
Dim dtCommQuestion As New DataTable
Dim MyCommand As SqlDataAdapter = New SqlDataAdapter("spCommQuest_Insert",
cnPortal)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
Dim li As ListItem

For Each li In lstCommunities.Items

If li.Selected = True Then

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@QuestionId",
SqlDbType.Int))

MyCommand.SelectCommand.Parameters("@QuestionId"). Value = txtQuestionId.Text

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@CommunityID",
SqlDbType.SmallInt))

MyCommand.SelectCommand.Parameters("@CommunityID") .Value = li.Value

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@IsDisplayed",
SqlDbType.Bit))

MyCommand.SelectCommand.Parameters("@IsDisplayed") .Value =
Convert.ToInt16(chkDisplayed.Checked)

MyCommand.Fill(dsCommQuestion, "tblCommQues")

dtCommQuestion = dsCommQuestion.Tables("tblCommQues")

End If

Next

cnPortal.Close()

cnPortal = Nothing

"Steve Caliendo" <sc*******@epion.com> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

Dim cn As New
SqlClient.SqlConnection("Server=(local);Database=E pionDataCenter;User
Id=scaliendo;Password=scaliendo;Trusted_Connection =False;")

Dim Command As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand("", cn)

cn.open

dim i as integer

for i = 1 to listbox1.items.count

if listbox1.items(i-1).selected = true then

command.commandtext = "update yourtable set yourfield = '" &
listbox1.items(i-1) & "' where yourfieldindex = currentfieldindex"

command.executenonquery

end if

next

or build an array out of the seleceted items in the listbox first if you'd
like to run only one query.

Steve


"Big E" <no****@nospam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

I'm using ASP.Net and SQL Server 2000. I'm trying to insert or update
records from a multiselect listbox. How can I get the contents of the
listbox if there is more than one selected and update that into the
database. But most important how do I update or insert those records.
Any articles or ideas would be appreciated.

Thanks.

Big e

[Big E]

I figured out what the problem was.
I needed to put the loop outside everything.
After it ran the stored procedure I needed to destroy it and do the next
record.
Thanks

"Big E" <no****@nospam.com> wrote in message
news:O3**************@tk2msftngp13.phx.gbl...

Here is what I currently have. It inserts the first record and then gives me the error that there are too many arguments or parameters specified.

Dim cnPortal As SqlConnection = New SqlConnection(Portal)
Dim dsCommQuestion As New DataSet
Dim dtCommQuestion As New DataTable
Dim MyCommand As SqlDataAdapter = New SqlDataAdapter("spCommQuest_Insert",
cnPortal)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
Dim li As ListItem

For Each li In lstCommunities.Items

If li.Selected = True Then

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@QuestionId",
SqlDbType.Int))

MyCommand.SelectCommand.Parameters("@QuestionId"). Value = txtQuestionId.Text
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@CommunityID",
SqlDbType.SmallInt))

MyCommand.SelectCommand.Parameters("@CommunityID") .Value = li.Value

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@IsDisplayed",
SqlDbType.Bit))

MyCommand.SelectCommand.Parameters("@IsDisplayed") .Value =
Convert.ToInt16(chkDisplayed.Checked)

MyCommand.Fill(dsCommQuestion, "tblCommQues")

dtCommQuestion = dsCommQuestion.Tables("tblCommQues")

End If

Next

cnPortal.Close()

cnPortal = Nothing

"Steve Caliendo" <sc*******@epion.com> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

Dim cn As New
SqlClient.SqlConnection("Server=(local);Database=E pionDataCenter;User
Id=scaliendo;Password=scaliendo;Trusted_Connection =False;")

Dim Command As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand("", cn)

cn.open

dim i as integer

for i = 1 to listbox1.items.count

if listbox1.items(i-1).selected = true then

command.commandtext = "update yourtable set yourfield = '" &
listbox1.items(i-1) & "' where yourfieldindex = currentfieldindex"

command.executenonquery

end if

next

or build an array out of the seleceted items in the listbox first if you'd like to run only one query.

Steve


"Big E" <no****@nospam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

I'm using ASP.Net and SQL Server 2000. I'm trying to insert or update
records from a multiselect listbox. How can I get the contents of the
listbox if there is more than one selected and update that into the
database. But most important how do I update or insert those records.
Any articles or ideas would be appreciated.

Thanks.

Big e