Visual basic write values to sql server table

Question

I have a checkedlist box that I want to write those values to a sql server table. Can anyone help?

---

Alan

Answer 1

depends how the data is used in data base. By that I mean, how each item in checkedlist box is related to a field in database.

See if this helps

---

Mark Answered, if it solves your question and Vote if you found it helpful.
Rohit Arora

Answer 2

Hi Alan,

Welcome to the MSDN forum.

Refer to your description, your issue is more related to the development of VB. Since our forum is to discuss Visual Studio WPF/SL Designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System, and Visual Studio Editor, I will help you move this thread to this appropriate forum: Visual Studio Languages  >  Visual Basic , you will get a more professional support and thank you for your understanding.

Best regards,

Sara

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Hi anaylor01,

I do a simple that you can refer to. I put one CheckListBox and Button Control in the Form.

Imports mshtml
Imports System.Data.SqlClient
Public Class Form18
    Private Sub Form18_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CheckedListBox1.SuspendLayout()
        Dim dt As New DataTable
        Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Example(VB)\DataBase1.mdf;Integrated Security=True;Connect Timeout=30"
        Using conn As New SqlConnection(str)
            Dim sql As String = "select * from CheckBox"
            Using cmd As New SqlCommand(sql, conn)
                conn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
        Dim lastindex As Integer = 0
        Try
            For Each row As DataRow In dt.Rows
                CheckedListBox1.Items.Add(row.Field(Of String)("Item"))
                lastindex = CheckedListBox1.Items.Count - 1
                If row.Field(Of Boolean)("CheckState") Then
                    CheckedListBox1.SetItemChecked(lastindex, True)
                End If
            Next
            CheckedListBox1.SelectedIndex = 0

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            CheckedListBox1.ResumeLayout()
        End Try

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim affected As Integer = 0
        Dim errorcount As Integer = 0
        Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Example(VB)\DataBase1.mdf;Integrated Security=True;Connect Timeout=30"
        Using conn As New SqlConnection(str)
            Dim sql As String = "Update CheckBox set Itemindex=@Itemindex, checkstate=@checkstate where Itemindex=@ItemIndex"
            Using cmd As New SqlCommand(sql, conn)

                cmd.Parameters.Add("@Itemindex", SqlDbType.Int)
                cmd.Parameters.Add("@CheckState", SqlDbType.Bit)
                cmd.Parameters.Add("@ItemIndex", SqlDbType.Int)
                conn.Open()
                For index As Integer = 0 To (CheckedListBox1.Items.Count - 1)
                    cmd.Parameters(0).Value = index
                    If CheckedListBox1.GetItemChecked(index) Then
                        cmd.Parameters(1).Value = True
                    Else
                        cmd.Parameters(1).Value = False
                    End If
                    cmd.Parameters(2).Value = index
                    Try
                        '
                        ' Good form to check if the query actually worked
                        '
                        affected = cmd.ExecuteNonQuery

                        If affected <> 1 Then
                            errorcount += 1
                        End If

                    Catch ex As Exception
                        errorcount += 1
                    End Try
                Next

            End Using
        End Using
    End Sub
End Class

Hope It is helpful to you.

Best Regards,

Cherry Bu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Hello,

See my MSDN code sample. All the basic operations are shown in the ProductsCodeSample for loading a CheckedListBox and updating data.

What I don't show is a single method for updating all items at once but here is code that will do the trick for the ProductsCodeSample.

Add a button to the form and place the following code into it.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Items As List(Of CheckListBoxItem) =
        (
            From item In checkedListBox1.Items.Cast(Of CheckListBoxItem)
            Select item
        ).ToList
End Sub

Then in Operations class add the following method

Public Sub UpdateAll(Items As List(Of CheckListBoxItem))
    Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
        Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}

            cmd.CommandText = "UPDATE [dbo].[Products] " &
                "SET [Quantity] = @Quantity, CheckedStatus = CheckedStatus WHERE id = @Id"

            cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@id", .SqlDbType = SqlDbType.Int})
            cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@Quantity", .SqlDbType = SqlDbType.Int})
            cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@CheckedStatus", .SqlDbType = SqlDbType.Bit})

            cn.Open()

            For Each item As CheckListBoxItem In Items
                cmd.Parameters("id").Value = item.PrimaryKey
                cmd.Parameters("@Quantity").Value = item.Quantity
                cmd.Parameters("@CheckedStatus").Value = item.Checked
                cmd.ExecuteNonQuery()
            Next
        End Using
    End Using
End Sub

Note that I used a class for all of the database operations as this is a best practice to separate code from user interface which allows you to use said code in more than one form or project and keeps form code clean.

Since the checked state is remembered in the CheckedListBox each time the form is opened the checked state is the same as last time the form was opened. If this is not desirable you can elect not to use that part simply be removing the following line in form load.

checkedListBox1.SetItemChecked(LastIndex, row.Field(Of Boolean)("CheckedStatus"))

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator