INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

loop through CheckBoxList selected items and insert results into SQLdatabase

loop through CheckBoxList selected items and insert results into SQLdatabase

loop through CheckBoxList selected items and insert results into SQLdatabase

(OP)
I am using VB.

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

CODE -->

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

RE: loop through CheckBoxList selected items and insert results into SQLdatabase

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

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.

RE: loop through CheckBoxList selected items and insert results into SQLdatabase

(OP)
I must be doing something wrong...

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

CODE -->

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.

RE: loop through CheckBoxList selected items and insert results into SQLdatabase

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.

RE: loop through CheckBoxList selected items and insert results into SQLdatabase

(OP)
Awesome, that has solved it.

Thanks

RE: loop through CheckBoxList selected items and insert results into SQLdatabase

Cool, glad to help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close