none
How to insert checked items from checkedlistbox to SQL database?

    Question

  • I am trying to save checked items from a checkedlistbox to my SQL database and i am filling my checkedlistbox from the same SQL database,So far i am able to get the text of checked item from the checkedlistbox and i saved it in a string then i used a label to display if i am getting the text of checked item or not and its working but when i try to insert the checked data in database i get a error "Connection property has not been initialized." on ExecuteNonQuery() method.\

    Code:-

    Imports System.Data
    Imports System.Data.SqlClient
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim da As New SqlDataAdapter
            Dim dt As New DataTable
            Dim connectionString As String = "Server=DESKTOP-V12PTAV ;Database=test ;User Id=sa ;Password=wills8877"
            Using conn As New SqlConnection(connectionString)
                conn.ConnectionString = connectionString
                conn.Open()
                Dim str As String
                str = "Select sem1 From sem"
                da = New SqlDataAdapter(str, conn)
                dt = New DataTable
                da.Fill(dt)
                CheckedListBox1.DataSource = dt
                CheckedListBox1.DisplayMember = "sem1"
                conn.Close()
            End Using
        End Sub
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
    
            Dim str As String
            Dim cmd As New SqlCommand
            Dim sql As String
            Dim connectionString As String = "Server=DESKTOP-V12PTAV ;Database=test ;User Id=sa ;Password=wills8877"
            Using conn As New SqlConnection(connectionString)
                conn.Open()
                Dim itemChecked As Object
                For Each itemChecked In CheckedListBox1.CheckedItems
                    str = itemChecked.item("sem1").ToString
                    Label1.Text = str
                    sql = "insert into pretab(pre) values('" + str + "')"
                    cmd.ExecuteNonQuery()
                Next
                conn.Close()
    
            End Using
    
        End Sub
    End Class

    Saturday, April 22, 2017 4:43 AM

All replies

  • If the items are in the database "Bool" then it is simply updating the database with the datatable. 

    You can use for instance a commandbuilder. 

    See the last rows in this sample on our website. 

    http://www.vb-tips.com/SQLServerUpdate.aspx


    Success
    Cor

    Saturday, April 22, 2017 8:47 AM
  • First off I would do data operations in a class e.g. (note I focus on inserts)

    Imports System.Data.SqlClient
    Public Class Operations
        Private Server As String = "KARENS-PC"
        Private Catalog As String = "CheckedListBoxDatabase"
        Private ConnectionString As String = ""
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        Public Function Read() As DataTable
            ' read rows for checked listbox here
        End Function
        Public Sub Insert(ByVal sender As List(Of String))
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn, .CommandText = "insert into pretab(pre) values (@pre)"}
                    cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@pre", .SqlDbType = SqlDbType.NVarChar})
                    cn.Open()
                    For Each item In sender
                        cmd.Parameters("@pre").Value = item
                        cmd.ExecuteNonQuery()
                    Next
                End Using
            End Using
        End Sub
    End Class
    

    Form code

    Public Class Form1
        Private ops As New Operations
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim Result = CheckedListBox1.Items.OfType(Of String).Where(Function(item, index) CheckedListBox1.GetItemChecked(index)).ToList
            ops.Insert(Result)
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            CheckedListBox1.DataSource = ops.Read
            CheckedListBox1.DisplayMember = "sem1"
        End Sub
    End Class
    For more on alternate methods for working with a CheckedListBox see my MSDN code sample.


    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

    Saturday, April 22, 2017 11:18 AM
    Moderator
  • Hi Shivang2211,

    I put one CheckListBox and Button control in the Form, then I create one empty Table(Test2) in SQL. There are three CheckItems(Id, Fruit, IsBuy) in the CheckListBox, please refer to the code below.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim conn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Database1.mdf;Integrated Security=True")
            Dim value As Integer = 1
            conn.Open()
            For i = 0 To CheckedListBox1.Items.Count - 1
                Dim sql As String = "insert into Test2 (id,Fruit,Isbuy) values (@Id,@Fruit, @IsBuy)"
                Dim cmd As New SqlCommand(sql, conn)
                cmd.Parameters.AddWithValue("@Id", value)
                cmd.Parameters.AddWithValue("@Fruit", CheckedListBox1.Items(i))
                cmd.Parameters.AddWithValue("@IsBuy", CheckedListBox1.GetItemCheckState(i))
                cmd.ExecuteNonQuery()
                value = value + 1
            Next
            conn.Close()
        End Sub

    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.

    Monday, April 24, 2017 8:49 AM
    Moderator