locked
select sql RRS feed

  • Question

  • hi

    I have 6 checkbox column in table database access 2007


    I want to make sure that one of the first three checkbox columnhas been checked

    column in table name

    EMPLOYEDATE_DATECOMCH =true , or  EMPLOYEDATE_DATECOMCHC =true ,or  EMPLOYEDATE_DATECOMCHDI =true


    And make sure that the three checkbox columnof the second have not been checked

    EMPLOYEDATE_DATELEAVCH =false ,  EMPLOYEDATE_DATELEAVCHC =false , EMPLOYEDATE_DATELEAVCHDI =false

    error in

    EMPLOYEDATE_DATECOMCH =true OR EMPLOYEDATE_DATECOMCHC =true OR EMPLOYEDATE_DATECOMCHDI =true AND EMPLOYEDATE_DATELEAVCH =false AND EMPLOYEDATE_DATELEAVCHC =false AND EMPLOYEDATE_DATELEAVCHDI =false AND

    "SELECT TOP 1 * FROM  EMPLOYEDATE_TB WHERE EMPLOYEDATE_DATECOMCH =true OR EMPLOYEDATE_DATECOMCHC =true OR EMPLOYEDATE_DATECOMCHDI =true AND EMPLOYEDATE_DATELEAVCH =false AND EMPLOYEDATE_DATELEAVCHC =false AND EMPLOYEDATE_DATELEAVCHDI =false AND  EMPLOYEDATE_CODE= '" & DATAG_GAPS.Item(2, DATAG_GAPS.CurrentRow.Index).Value.ToString.Trim & "'  ORDER BY EMPLOYEDATE_DATE ASC")

    Wednesday, March 27, 2019 12:57 PM

Answers

  • Have you considered (this would only be true if all data entry is done in your app) using code assertion to enforce these rules?

    Public Class Form1
        WithEvents bsData As New BindingSource
        Public Defaults As New Dictionary(Of Integer, String)
        Private Function GetMockedData() As DataTable
            Defaults.Add(1, "Check2")
            Defaults.Add(2, "Check1")
            Defaults.Add(3, "Check2")
            Defaults.Add(4, "Check3")
            Defaults.Add(5, "Check3")
    
            Dim dt As New DataTable
    
            dt.Columns.AddRange(New DataColumn() _
               {
                   New DataColumn("Identifier", GetType(Integer)),
                   New DataColumn("Check1", GetType(Boolean)),
                   New DataColumn("Check2", GetType(Boolean)),
                   New DataColumn("Check3", GetType(Boolean)),
                   New DataColumn("Check4", GetType(Boolean)),
                   New DataColumn("Check5", GetType(Boolean)),
                   New DataColumn("Check6", GetType(Boolean))
               }
            )
    
            dt.Rows.Add(1, False, False, False)
            dt.Rows.Add(2, True, False, False)
            dt.Rows.Add(3, False, True, False)
            dt.Rows.Add(4, False, False, True)
            dt.Rows.Add(5, False, False, True)
    
            dt.AcceptChanges()
    
            Return dt
    
        End Function
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            bsData.DataSource = GetMockedData()
            DataGridView1.DataSource = bsData
        End Sub
        Private Sub DataGridView1SelectAll_CurrentCellDirtyStateChanged(
            sender As Object, e As EventArgs) _
            Handles DataGridView1.CurrentCellDirtyStateChanged
    
            If TypeOf DataGridView1.CurrentCell Is DataGridViewCheckBoxCell Then
                DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
            End If
    
        End Sub
        Private Sub DataGridView1_CellValueChanged(
            sender As Object,
            e As DataGridViewCellEventArgs) _
            Handles DataGridView1.CellValueChanged
    
            If DataGridView1.CurrentRow IsNot Nothing Then
    
                Dim selectedColumnName = DataGridView1.
                        Columns(DataGridView1.CurrentCellValue.ColumnIndex).Name
    
                If e.ColumnIndex = DataGridView1.Columns("Check1").Index OrElse
                   e.ColumnIndex = DataGridView1.Columns("Check2").Index OrElse
                   e.ColumnIndex = DataGridView1.Columns("Check3").Index Then
    
                    Dim row = (CType(bsData.Current, DataRowView)).Row
    
                    row.SetField("Check4", False)
                    row.SetField("Check5", False)
                    row.SetField("Check6", False)
    
    
                    If selectedColumnName = "Check1" Then
                        If DataGridView1.CurrentRowCellValue("Check1") = "True" Then
                            row.Item("Check2") = False
                            row.Item("Check3") = False
                        End If
                    ElseIf selectedColumnName = "Check2" Then
                        If DataGridView1.CurrentRowCellValue("Check2") = "True" Then
                            row.Item("Check1") = False
                            row.Item("Check3") = False
                        End If
                    ElseIf selectedColumnName = "Check3" Then
                        If DataGridView1.CurrentRowCellValue("Check3") = "True" Then
                            row.Item("Check1") = False
                            row.Item("Check2") = False
                        End If
                    End If
    
                    CType(bsData.DataSource, DataTable).AcceptChanges()
    
                    If CType(row("Check1"), Boolean) = False AndAlso
                       CType(row("Check2"), Boolean) = False AndAlso
                       CType(row("Check3"), Boolean) = False Then
    
                        row(Defaults(CInt(row("Identifier")))) = True
                        CType(bsData.DataSource, DataTable).AcceptChanges()
    
                    End If
    
                    DataGridView1.CurrentCell = DataGridView1(e.ColumnIndex, e.RowIndex)
                ElseIf e.ColumnIndex = DataGridView1.Columns("Check4").Index OrElse
                       e.ColumnIndex = DataGridView1.Columns("Check5").Index OrElse
                       e.ColumnIndex = DataGridView1.Columns("Check6").Index Then
    
                    Dim row = (CType(bsData.Current, DataRowView)).Row
    
                    If CType(row("Check1"), Boolean) = True OrElse
                       CType(row("Check2"), Boolean) = True OrElse
                       CType(row("Check3"), Boolean) = True Then
    
                        row.SetField("Check4", False)
                        row.SetField("Check5", False)
                        row.SetField("Check6", False)
                        If row.RowState <> DataRowState.Detached Then
                            row.AcceptChanges()
                        End If
                    End If
                End If
            End If
        End Sub
    
    End Class

    Helpers

    Friend Module DataGridviewExtensions
        <Runtime.CompilerServices.Extension()>
        Function CurrentRowCellValue(sender As DataGridView, columnName As String) As String
            Dim result = ""
    
            If Not sender.Rows(sender.CurrentRow.Index).Cells(columnName).Value Is Nothing Then
                result = sender.Rows(sender.CurrentRow.Index).Cells(columnName).Value.ToString
            End If
    
            Return result
    
        End Function
        <Runtime.CompilerServices.Extension()>
        Function CurrentCellValue(sender As DataGridView) As DataGridViewCell
            Return sender(sender.Columns(sender.CurrentCell.ColumnIndex).Index, sender.CurrentRow.Index)
        End Function
    End Module


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by ahmeddc Thursday, March 28, 2019 7:14 PM
    Wednesday, March 27, 2019 2:41 PM