Datagridview checkboxes in Sqlce database RRS feed

  • Question

  • Hello,

    With this i fill a datagridview and put a checkbox in the datagridview.

     cmd = New SqlCeCommand("Select * FROM Bemanning", con)
            If con.State = ConnectionState.Closed Then con.Open()
            myDA = New SqlCeDataAdapter(cmd)
            myDataSet = New DataSet()
            myDA.Fill(myDataSet, "MyTable")
            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
            If con.State = ConnectionState.Closed Then con.Open()
    Dim checkBoxColumn As New DataGridViewCheckBoxColumn()
            checkBoxColumn.HeaderText = "Selecteer"
            checkBoxColumn.Width = 30
            checkBoxColumn.Name = "checkBoxColumn"
            DataGridView1.Columns.Insert(0, checkBoxColumn)

    But can i save the checkboxes to the sqlce database so it is showing the next time the one's i selected.

    • Edited by KeesBlunder Saturday, July 14, 2018 6:22 AM
    Saturday, July 14, 2018 6:21 AM

All replies

  • Here is a mock up with plenty of comments. Bottom line is the focus is on the DataTable when dealing with the DataGridView check column. This will work for any database since we focus on the DataTable.

    A text file stores the primary key for any checked rows in the DataGridView.

    I use a BindingSource component as this is a great way to work with data but you can eliminate it if you elect to do so.

    There are several assertions done which cause the code to be complex to handle invalid rows as stated in the comments.

    Imports System.IO
    ''' <summary>
    ''' Load mocked data
    ''' Check if Checked.txt exists, if it does
    ''' then load primary keys and ignore empty lines
    ''' which can happen when a user had selected the
    ''' new row in the DataGridView and was not properly
    ''' saved to the backend database table.
    ''' On app close get checked rows, save to text file
    ''' while if no checked rows delete text file
    ''' </summary>
    Public Class Form1
        Private ReadOnly _bsPeople As New BindingSource
        Private _fileName As String = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Checked.txt")
        Private Sub Form1_Load(sender As System.Object, e As EventArgs) Handles MyBase.Load
            ' simulate reading data from a database table
            Dim dt = GetMockedData()
            _bsPeople.DataSource = dt
            DataGridView1.DataSource = _bsPeople
            BindingNavigator1.BindingSource = _bsPeople
            If File.Exists(_fileName) Then
                Dim identifiers = File.ReadAllLines(_fileName).
                        Where(Function(line) Not String.IsNullOrWhiteSpace(line)).
                        Select(Function(line) CInt(line))
                    Where(Function(row) identifiers.Contains(row.Field(Of Integer)("Identifier"))).
                    ForEach(Sub(row) row.SetField(Of Boolean)("Process", True))
            End If
        End Sub
        Private Function GetMockedData() As DataTable
            Dim dt As New DataTable()
            dt.Columns.Add("Identifier", GetType(Integer))
            dt.Columns.Add("FirstName", GetType(String))
            dt.Columns.Add("LastName", GetType(String))
            ' hide columns in the user interface
            dt.Columns("FirstName").ColumnMapping = MappingType.Hidden
            dt.Columns("LastName").ColumnMapping = MappingType.Hidden
            dt.Rows.Add(10, "John", "Smith")
            dt.Rows.Add(20, "Mary", "Willson")
            dt.Rows.Add(30, "John", "Mills")
            dt.Rows.Add(40, "Amy", "Jones")
            dt.Rows.Add(50, "Karen", "Starr")
            ' after similating load from database table, add a boolean
            ' column with the default value as false so not to get a null exception
            dt.Columns.Add(New DataColumn With {.DataType = GetType(Boolean),
                              .ColumnName = "Process", .DefaultValue = False})
            ' show first/last name in one column
            dt.Columns.Add(New DataColumn With {.DataType = GetType(String),
                              .ColumnName = "FullName",
                              .Expression = " FirstName + ' ' + LastName"})
            dt.Columns("LastName").ColumnMapping = MappingType.Hidden
            ' don't use this when loading from a database
            Return dt
        End Function
        Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
                ' This state is rather complex only for protection against invalid rows
                ' when a DataGridView allows users to add new rows in the last empty row
                Dim results = CType(_bsPeople.DataSource, DataTable).
                    Where(Function(row) Not row.RowState = DataRowState.Detached AndAlso row.Field(Of Boolean)("Process")).
                    Select(Function(row) row.Field(Of Integer?)("Identifier").ToString()).ToArray()
                If results.Count() > 0 Then
                    File.WriteAllLines(_fileName, results)
                    If File.Exists(_fileName) Then
                    End If
                End If
            Catch ex As Exception
                ' Handle any exceptions
            End Try
        End Sub
    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

    Saturday, July 14, 2018 10:41 AM
  • Hi 

    I think we can save the selected data as a DataTable,then  save the DataTable to the database, and use it the next time it is called.

    Imports System.Data.SqlClient Public Class Form1 Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf" Dim conn As SqlConnection Dim sda As SqlDataAdapter Dim dt As DataTable Dim dtable As DataTable Dim row1 As DataRow Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim checkBoxColumn As New DataGridViewCheckBoxColumn() checkBoxColumn.HeaderText = "Selecteer" checkBoxColumn.Width = 55 checkBoxColumn.Name = "checkBoxColumn" DataGridView1.Columns.Insert(0, checkBoxColumn) Using conn = New SqlConnection(constr) conn.Open() sda = New SqlDataAdapter("Select * From Student", conn) dt = New DataTable() sda.Fill(dt) DataGridView1.DataSource = dt End Using End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click dtable = dt.Clone() For Each Row As DataGridViewRow In DataGridView1.Rows If CBool(Row.Cells("checkBoxColumn").Value <> False) And Row.Cells("checkBoxColumn").Value <> Nothing Then Dim drv As DataRowView = TryCast(Row.DataBoundItem, DataRowView) row1 = DirectCast(drv.Row, DataRow) dtable.Rows.Add(row1.ItemArray) End If Next DataGridView2.DataSource = dtable End Sub

    End Class

    Best Regards,


    MSDN Community Support Please remember to click &amp;amp;amp;amp;amp;amp;quot;Mark as Answer&amp;amp;amp;amp;amp;amp;quot; the responses that resolved your issue, and to click &amp;amp;amp;amp;amp;amp;quot;Unmark as Answer&amp;amp;amp;amp;amp;amp;quot; 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, July 16, 2018 5:50 AM