none
Visual basic write values to sql server table

All replies

  • 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

    Tuesday, April 11, 2017 6:08 AM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 7:47 AM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 7:49 AM
    Moderator
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, April 11, 2017 9:54 AM
    Moderator