How to create validation in row creation, if this row is not in rows, create it. DatagridView, DataTable

Question

hi

   Dim MyDataTable As New DataTable
   Dim OnlyColumns As String() = {"Type", "Location", "Size"}
    Private Sub DataGridView1_RowValidating(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DataGridView1.RowValidating
'e.Cancel=MyDataTable by OnlyColumns   Need condition by 'linq' 
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        MyDataTable.Columns.Add("Group")
        MyDataTable.Columns.Add("Type")
        MyDataTable.Columns.Add("Size")
        MyDataTable.Columns.Add("Location")
        DataGridView1.DataSource = MyDataTable
        MyDataTable.Rows.Add({"CABLE", "MV", "1x240", "Air Building"})
        MyDataTable.Rows.Add({"CABLE", "MV", "1x240", "ACC Building"})
        MyDataTable.Rows.Add({"CABLE", "LV", "1x240", "Swithgear"})
        MyDataTable.Rows.Add({"Install", "Cable Tray", "20", "Swithgear"})
    End Sub

Answer 1

Hi @Mansour_Dalir ,

Here is an example with dataview and linq.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim newRowData() As String = {"CABLE", "MV", "1x240", "Air Building1"}

        Dim dataView As New DataView(MyDataTable)
        dataView.RowFilter = GetRowFilterExpression(newRowData)

        Dim isDuplicate As Boolean = dataView.Count > 0

        If Not isDuplicate Then
            MyDataTable.Rows.Add(newRowData)
            Console.WriteLine("New row added.")
        Else
            Console.WriteLine("New row duplicate existing rows.")
        End If
    End Sub

    Function GetRowFilterExpression(newRowData() As String) As String
        Dim expressions As New List(Of String)
        For Each columnName As String In MyDataTable.Columns.Cast(Of DataColumn)().Select(Function(col) col.ColumnName)
            expressions.Add(String.Format("{0} = '{1}'", columnName, newRowData(Array.IndexOf(MyDataTable.Columns.Cast(Of DataColumn)().Select(Function(col) col.ColumnName).ToArray(), columnName))))
        Next
        Return String.Join(" AND ", expressions)
    End Function

If the amount of data is too large, the operation of in-memory data will always take more time, in which case you may consider using a database system.

Answer 2

Hi @Mansour_Dalir ,

I think it might be better to detect if a new row duplicates an existing row in the DataGridView1.RowValidating event, where e.cancle is able to prevent the user from leaving the duplicate row.

Please check the following code.

    Private Sub DataGridView1_RowValidating(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DataGridView1.RowValidating
        Dim newRowIndex As Integer = e.RowIndex

        If newRowIndex < DataGridView1.Rows.Count - 1 Then
            Dim newRowValues As New List(Of String)
            For Each cell As DataGridViewCell In DataGridView1.Rows(newRowIndex).Cells
                newRowValues.Add(cell.Value.ToString())
            Next

            Dim duplicateRow = (From row As DataGridViewRow In DataGridView1.Rows.Cast(Of DataGridViewRow)()
                                Where row.Index <> newRowIndex AndAlso
                                      row.Cells.Cast(Of DataGridViewCell)().All(Function(cell) cell.Value IsNot Nothing) AndAlso
                                      Enumerable.Range(0, DataGridView1.Columns.Count).All(Function(columnIndex) row.Cells(columnIndex).Value.ToString() = newRowValues(columnIndex))
                                Select row).FirstOrDefault()

            If duplicateRow IsNot Nothing Then
                MessageBox.Show("New lines duplicate existing lines! Please modify it!", "Duplicate row detection", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                e.Cancel = True
            End If
        End If
    End Sub

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.