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