loop through CheckBoxList selected items and insert results into SQLdatabase 1

[MrMode]

I am using VB.

I have code that works Inserting into a database individual records checked in CheckListBox control on a webform.

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
    End Sub
    Private Function GetCheckBoxListSelections() As String
        Dim cblItems As String()
        Dim cblSelections As New ArrayList()
        For Each item As ListItem In CheckBoxList1.Items
            If item.Selected Then
                cblSelections.Add(item.Value)
            End If
        Next

        cblItems = DirectCast(cblSelections.ToArray(GetType(String)), String())
        Return String.Join(",", cblItems)
    End Function

   Protected Sub Button3_Click(sender As Object, e As EventArgs)
        Dim str As [String] = ""
        For i As Integer = 0 To CheckBoxList1.Items.Count - 1

            If CheckBoxList1.Items(i).Selected Then

                If str = "" Then
                    str = CheckBoxList1.Items(i).Value
                Else

                    str += "," + CheckBoxList1.Items(i).Value

                End If
            End If
        Next

        Dim cmd As New SqlCommand("Insert into tblTagOnFly(CompanyID, TagOnFlyName) values('" & str & "','" & TextBox1.Text & "')", con)
        cmd.ExecuteNonQuery()
        Clear()
        con.Close()
    End Sub
    Public Sub Clear()
        CheckBoxList1.SelectedIndex = -1
    End Sub

I need it to work on all the records selected, not just the first record, but I do not know how to write the code to loop through the selected records...

Any help would be most appreciated

 

[jbenson001]

As you loop through the checkbox list, call your insert code

 For Each item As ListItem In CheckBoxList1.Items
  If item.Selected Then
      'Call Insert command..
  End If
End For

OR

You can write a stored procedure that accepts a string of IDs as a parameter. You can then parse the string and do and Insert.

 

[MrMode]

I must be doing something wrong...

I have created a new button control and put the following code behind its OnClick event

 Protected Sub Button8_Click(sender As Object, e As EventArgs)

        For Each item As ListItem In CheckBoxList1.Items
            If item.Selected Then
                Dim cmd As New SqlCommand("Insert into tblTagOnFly(CompanyID, TagOnFlyName) values('" & CheckBoxList1.SelectedItem.Value & "','" & TextBox1.Text & "')", con)
                cmd.ExecuteNonQuery()
            End If
        Next
        con.Close()
    End Sub

It adds the correct number of records, but gives them all the first record's id... Apologies for being slow.

 

[jbenson001]

For the values, you need to change CheckboxList1.SelectedItem.Value to item.Value. Not sure about the textbox value, is that supposed to be the same.

On another note, I think you need to close the connection in the loop after the cmd.ExecuteNonQuery() call.

for each insert, you need to open and close the connection.

 

[MrMode]

Awesome, that has solved it.

Thanks

 

[jbenson001]

Cool, glad to help.