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

Mansour_Dalir 1,371 Reputation points



   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
        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
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,496 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiachen Li-MSFT 24,201 Reputation points Microsoft Vendor

    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
                Console.WriteLine("New row added.")
                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))))
            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.

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Jiachen Li-MSFT 24,201 Reputation points Microsoft Vendor

    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
                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.