none
Checkedlistbox, save checkstate to DB between tables

    Question

  • Hi

    I got a little problem with the usage of checklistbox. I've never used the control before, so I don't know if what I want to do is even possible. Hopefully someone out there might be able to help me out.

    I'm trying to make a Maintenance application (Windows Form) where users can check completed maintenance tasks. I got a access database with following Tables: ServiceList, MachineList, Monthly, Quarterly, HalfYear, Yearly. The ServiceList table contains all the service tasks with service interval, short description, long description and a columnID. The Monthly, Quarterly, HalfYear, Yearly tables contains MachineName, date and all the columnID's of the given interval

    The checkboxlist is populated by the servicelist table like this:

    Dim månedl As New OleDbDataAdapter("select KolonneNavn, KolonneID from Service where TypeService = '" & mån & "'", con)
            Dim dt As New DataTable
            con.Open()
            månedl.Fill(dt)
            With CheckedListBox1
                .DataSource = dt
                .DisplayMember = "KolonneNavn"
                .ValueMember = "KolonneID"
            End With
            con.Close()


    I need to store the checked state with machinename and date in the selected interval table (Monthly, Quarterly, HalfYear, Yearly). 

    Since service might change for machine to machine, and sometime perhaps a monthly service is changed to quarterly, the checklistbox might change, so I can't hardcode the parameters. 

    Is there a way to save the checkstate to the db when I don't know in what column to save the data? 

    i.e: "Insert into Montly(kolonneID) values('checkstate')"

    I hope I didn't over complicate my question :o)

    Many thanks

    Carl-Martin

    Friday, March 17, 2017 12:16 PM

Answers

  • Hello

    The following is base logic, read data from a ms-access database table, populate the CheckedListBox with this data. Then a button to get the checked items from the CheckedListBox.

    In the class there are two methods, one to read data and the other partly done to work the data which came from the CheckedListBox (note I didn't complete the Command CommandText and add in Command parameters.

    Note, there is no reason to push a DataTable to the CheckedListBox, in short all we need is a structured class as shown below and the same goes for pushing data back to the database table.

    Coding was done in VB.NET, Visual Studio 2015.

    Form code

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New Operations
            Dim LastIndex As Integer = 0
    
            Dim items = ops.LoadData
            For Each item As ServiceItem In items
                CheckedListBox1.Items.Add(item)
                LastIndex = CheckedListBox1.Items.Count - 1
                CheckedListBox1.SetItemChecked(LastIndex, item.Checked)
            Next
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If CheckedListBox1.CheckedItems.Count > 0 Then
                Dim ops As New Operations
                ops.UpdateItems(
                    (
                        From item In CheckedListBox1.CheckedItems
                        Select CType(item, ServiceItem)).ToList
                    )
            Else
                MessageBox.Show("Nothing checked")
            End If
        End Sub
    End Class
    
    

    Back end code

    Imports System.Data.OleDb
    
    Public Class Operations
        Private Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(
                    AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
            }
        Public Function LoadData() As List(Of ServiceItem)
            Dim itemList As New List(Of ServiceItem)
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                id, 
                                Item, 
                                TheDate ,
                                CheckedState
                            FROM Table1;
                        </SQL>.Value
    
                    Dim dt As New DataTable With {.TableName = "ServiceTable"}
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                    If reader.HasRows Then
                        While reader.Read
                            itemList.Add(New ServiceItem With
                                         {
                                            .Id = reader.GetInt32(0),
                                            .Item = reader.GetString(1),
                                            .TheDate = reader.GetDateTime(2),
                                            .Checked = reader.GetBoolean(3)
                                         })
                        End While
                    End If
    
                    Return itemList
    
                End Using
            End Using
        End Function
        Public Sub UpdateItems(items As List(Of ServiceItem))
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "UPDATE SQL goes here with parameters"
                    ' create parameters here
                    cn.Open()
                    For Each item As ServiceItem In items
                        ' set parameter values to items below then
                        ' do cmd.ExecuteNonReader
                        Console.WriteLine($"{item.Id}, {item.Item}, {item.TheDate}")
                    Next
                End Using
    
            End Using
        End Sub
    End Class
    Public Class ServiceItem
        Public Property Id As Integer
        Public Property Item As String
        Public Property TheDate As DateTime
        Public Property Checked As Boolean
        Public Overrides Function ToString() As String
            Return Item
        End Function
    End Class
    


    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

    • Marked as answer by Calle78 Tuesday, March 21, 2017 1:36 PM
    Friday, March 17, 2017 11:46 PM
    Moderator

All replies

  • Hello

    The following is base logic, read data from a ms-access database table, populate the CheckedListBox with this data. Then a button to get the checked items from the CheckedListBox.

    In the class there are two methods, one to read data and the other partly done to work the data which came from the CheckedListBox (note I didn't complete the Command CommandText and add in Command parameters.

    Note, there is no reason to push a DataTable to the CheckedListBox, in short all we need is a structured class as shown below and the same goes for pushing data back to the database table.

    Coding was done in VB.NET, Visual Studio 2015.

    Form code

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New Operations
            Dim LastIndex As Integer = 0
    
            Dim items = ops.LoadData
            For Each item As ServiceItem In items
                CheckedListBox1.Items.Add(item)
                LastIndex = CheckedListBox1.Items.Count - 1
                CheckedListBox1.SetItemChecked(LastIndex, item.Checked)
            Next
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If CheckedListBox1.CheckedItems.Count > 0 Then
                Dim ops As New Operations
                ops.UpdateItems(
                    (
                        From item In CheckedListBox1.CheckedItems
                        Select CType(item, ServiceItem)).ToList
                    )
            Else
                MessageBox.Show("Nothing checked")
            End If
        End Sub
    End Class
    
    

    Back end code

    Imports System.Data.OleDb
    
    Public Class Operations
        Private Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(
                    AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
            }
        Public Function LoadData() As List(Of ServiceItem)
            Dim itemList As New List(Of ServiceItem)
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                id, 
                                Item, 
                                TheDate ,
                                CheckedState
                            FROM Table1;
                        </SQL>.Value
    
                    Dim dt As New DataTable With {.TableName = "ServiceTable"}
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                    If reader.HasRows Then
                        While reader.Read
                            itemList.Add(New ServiceItem With
                                         {
                                            .Id = reader.GetInt32(0),
                                            .Item = reader.GetString(1),
                                            .TheDate = reader.GetDateTime(2),
                                            .Checked = reader.GetBoolean(3)
                                         })
                        End While
                    End If
    
                    Return itemList
    
                End Using
            End Using
        End Function
        Public Sub UpdateItems(items As List(Of ServiceItem))
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "UPDATE SQL goes here with parameters"
                    ' create parameters here
                    cn.Open()
                    For Each item As ServiceItem In items
                        ' set parameter values to items below then
                        ' do cmd.ExecuteNonReader
                        Console.WriteLine($"{item.Id}, {item.Item}, {item.TheDate}")
                    Next
                End Using
    
            End Using
        End Sub
    End Class
    Public Class ServiceItem
        Public Property Id As Integer
        Public Property Item As String
        Public Property TheDate As DateTime
        Public Property Checked As Boolean
        Public Overrides Function ToString() As String
            Return Item
        End Function
    End Class
    


    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

    • Marked as answer by Calle78 Tuesday, March 21, 2017 1:36 PM
    Friday, March 17, 2017 11:46 PM
    Moderator
  • Sorry for not replaying sooner, but have been really trying to figure this out. But I'm still stuck.

    Most of my problems are in saving the information to the DB. The idea is to be able to have different serviceitems for different machines and I want to be able to add or remove service items without having to code them. So the tables would look like this:

    Serviceitems:


    The completed maintenance table:

     So the question is. Is it possible to insert into the completed maintenance table when I don't in advance know what fields/values/parameters are to be saved?

    I.e: If a user in a combobox select he wants to do maintenance on Machine2, and in another combobox selects that he wants to do monthly service I would do the selection for the checkboxlist like this:

    "select ColName from Service where Machine2 = X and TypeOfService = Monthly"

    The checkboxlist would change depending on selected machine and type of service. If I knew that every time I would add something to each column in the completed maintenance table and the number of columns would never change, this would be easy. But I want to be able to add/remove columns depending on if new maintenance tasks are created. 

    Hope I'm making sense in my question..

    Carl-Martin

     
    Tuesday, March 21, 2017 9:58 AM
  • First off your initial question was how 1. how to use the CheckedListBox with data 2. how to save the checkstate to the db. This has been answered while it may not directly address your tables the code sample I provided allows you to move forward.

    In your last reply this is more of a database schema issue in tangent with the CheckedListBox and deserves a new question.

    What you should do from here is close this thread out, if my code sample got you on the right track then either mark it as answered or as a helpful post.

    When starting a new thread I can tell you that your statement

    I don't in advance know what fields/values/parameters are to be saved?

    If you don't know the fields then there is something wrong with either how you are approaching the problem or the database schema is incorrect for what you want to do.

    Also in regards to the new thread, know that the following needs to have parameters

    select ColName from Service where Machine2 = X and TypeOfService = Monthly

    example

    Public Function GetDemo(ByVal Machine As String, ByVal TypeOfService As String) As DataTable
        Dim dt As New DataTable
        Using cn As New OleDbConnection(Builder.ConnectionString)
            Using cmd As New OleDbCommand("", cn)
                cmd.CommandText = "SELECT ColName FROM Service WHERE Machine2 = @Machine AND " &
                "TypeOfService = @TypeOfService"
                cmd.Parameters.AddWithValue("@Machine", Machine)
                cmd.Parameters.AddWithValue("@TypeOfService", TypeOfService)
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
        Return dt
    End Function
    In closing, close out this post and start a new one for the last reply you wrote.


    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, March 21, 2017 10:59 AM
    Moderator
  • Carl-Martin

    A checkbox (whatever) is simply a representing of a Bool column in a database and therefore normally updated. 

    Be aware that a radiobutton has some issues to keep track off, but a checkbox not. 


    Success
    Cor

    Tuesday, March 21, 2017 11:06 AM
  • I'm sorry for not formulating my initial question the way it should have been, perhaps my English is not as good as I thought :)

    Your code works fine for filling the checkboxlist and reading to/from the DB. Your code was a bit more advanced than what I usually write, so it was very educational reading and understanding it, thank you for that.

    Only comment I have, is when trying to write to DB, I got the error 'ExecuteNonReader' is not a member of oledb.

    As for a new question, I'm not sure how to write it in an understandable way...

    But again, thank you for your help

    Carl-Martin

     
    Tuesday, March 21, 2017 1:36 PM