Find duplicate DataGrid rows before saving its data

Say you display data in a DataGrid, allow the user to edit it then save it. If the user can insert new rows or change values in the columns that uniquely identify a row you need to guard against duplicate rows.

Typically you validate the datagrid's contents for the correctness of values and uniqueness before saving its changed data. You can issue SELECT statements against the database for each changed datagrid row to determine if the row exists but this can be costly in terms of performance. (The assumption is you will generate and execute your own SQL commands to store the data and not allow .NET to do it for you.)

One way to check for duplicate rows is to loop through the DataGrid and build SQL statements used to Filter the underlying datatable. If the filtered datatable has more than one row you have a duplicate. A VB.NET example of this technique is shown below.

Retrieve data from the database:

    Imports System.Data.OleDb
    '
    ' Retrieve the data.
    '
    Dim strSQL As String = "Select * From Customers"
    Dim Connection As New OleDbConnection(strConnection)
    Dim DA As New OleDbDataAdapter(strSQL, Connection)
    Dim DS As New DataSet

    DA.Fill(DS, "Customers")
    '
    ' Display the data in the datagrid.
    '
    If DS.Tables("Customers").Rows.Count > 0 Then
        With DataGrid
           .DataSource = DS.Tables("Customers")
           .Expand(-1)
           .NavigateTo(0, "Customers")
        End With
    End If

Execute this code when the data is validated:

    Dim blnErrorsExist As Boolean = False
    Dim strMsg As String = ""
    Dim strValue As String = ""
    Dim strSQL As System.Text.StringBuilder
    Dim cm As CurrencyManager = _
        CType(DataGrid.BindingContext(DataGrid.DataSource), CurrencyManager)
    Dim intRowCount As Integer = cm.Count - 1
    Dim intRowIndex As Integer = 0
    Dim intRows As Integer = 0
    Dim intColIndex As Integer
    Dim intColCount As Integer = 1
    '
    ' Loop through the datagrid rows.
    '
    For intRowIndex = 0 To intRowCount
        '
        ' Initialize the filter string. Trim values so "JOE" matches "  JOE  ".
        '
        strSQL = "Trim(Cust_Name)='?0' AND (Trim(Cust_Office)='?1' ?2)"
        '
        ' Loop through the row's columns. The key to the Customer table is
        ' Cust_Name. If the key consisted of more than one column you could
        ' easily expand this technique.
        '
        For intColIndex = 0 To intColCount
            '
            ' Get the datagrid cell's value and trim it.
            '
            strValue = DataGrid(intRowIndex, intColIndex).ToString().Trim()

            Select Case intColIndex
                Case 0
                    '
                    ' Customer name is required.
                    '
                    If strValue = "" Then
                        blnErrorsExist = True
                        strMsg = "Missing Customer Name."
                        Exit For
                    End If
                    '
                    ' Build the filter SQL.
                    '
                    strSQL = strSQL.Replace("?0", strValue)

                Case 1
                    '
                    ' The Customer Office can be blank or Null. The filter string
                    ' must catch this and treat the following 2 rows as duplicates:
                    '   Cust Name   Cust Office
                    '   JOE           blank
                    '   JOE           null
                    '
                    strSQL = strSQL.Replace("?1", strValue)
                    If aValue = "" Then
                        strSQL = strSQL.Replace("?2", " OR Cust_Office IS Null")
                    Else
                        strSQL = strSQL.Replace("?2", "")
                    End If
            End Select
        Next

        If blnErrorsExist Then Exit For
            '
            ' Look for duplicate rows by applying a filter 
            ' and checking the number of rows.
            '
            DS.Tables("Customers").DefaultView.RowFilter = strSQL
            intRows = DS.Tables("Customers").DefaultView.Count
            DS.Tables("Customers").DefaultView.RowFilter = ""

            If intRows > 1 Then
                blnErrorsExist = True
                strMsg = "Duplicate row found. The Customer Name" & vbCrLf & _
                             "and Customer Office must be unique."
                Exit For
            End If
        Next
        '
        ' If an error was found go to the bad row and issue a message.
        '
        If blnErrorsExist Then
            DataGrid.Select(intRowIndex)
            '
            ' See text below.            
            '
            DataGrid.ScrollToRow(intRowIndex)
            DataGrid.CurrentRowIndex = intRowIndex 

            MessageBox.Show(strMsg,"Error Saving Data", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If

When an invalid row is found the datagrid is scrolled to that row using the ScrollToRow method. This method is based on a derived datagrid which calls the datagrid's GridVScrolled method. To see how to implement the ScrollToRow method click here.


About TheScarms
About TheScarms


Sample code
version info

If you use this code, please mention "www.TheScarms.com"

Email this page


© Copyright 2016 TheScarms
Goto top of page