DGV Styles

I have a DGV that contains three columns: StoreID, DateStamp and GUID. I want to highlight duplicate or triplicate data if the StoreID exists more than once on the same DateStamp.

I have done some searching and I have found some ways to highlight columns or rows based on a certain value, but my needs require me to know if a StoreID exists more than once on the same day.

Any thoughts on how to program this would be much appreciated.

I'm assuming the data is bound correct? Here is a way to do it with LINQ, although it might be better to create a stored procedure to do this, especially with a large dataset.

vb.net Code:

Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles Button1.Click
 
        '//sample datasource
        Dim dt = New DataTable()
        With dt.Columns
            '//add columns
            .Add("StoreId", GetType(Integer))
            .Add("DateStamp", GetType(Date))
            .Add("Guid", GetType(Guid))
        End With
        With dt.Rows
            '//add rows
            .Add(1, Date.Now.Date, Guid.NewGuid())
            .Add(1, Date.Now.Date, Guid.NewGuid())
            .Add(2, Date.Now.AddDays(1.0R).Date, Guid.NewGuid())
            .Add(3, Date.Now.AddDays(2.0R).Date, Guid.NewGuid())
            .Add(2, Date.Now.AddDays(1.0R).Date, Guid.NewGuid())
            .Add(1, Date.Now.AddDays(1.0R).Date, Guid.NewGuid())
        End With
 
        '//set datasource
        Me.DataGridView1.DataSource = dt
 
        '//highlighting code
        Dim query = dt.AsEnumerable() _
                      .GroupBy(Function(key) _
                                   New With {Key .StoreId = _
                                                key.Field(Of Integer)("StoreId"), _
                                             Key .DateStamp = _
                                                key.Field(Of Date)("DateStamp")}) _
                      .Where(Function(g) g.Count() > 1) _
                      .Select(Function(g) g.Key)
 
        '//Sql Translation
        '//  SELECT dt.StoreId, dt.DateStamp
        '//  FROM(dt)
        '//  GROUP BY dt.StoreId, dt.DateStamp
        '//  HAVING (((Count(dt.DateStamp))>1));
 
        For Each row As DataGridViewRow In Me.DataGridView1.Rows
            For Each anon In query
                Dim storeId = row.Cells("StoreId").Value
                Dim dateStamp = row.Cells("DateStamp").Value
                If Object.Equals(storeId, anon.StoreId) AndAlso _
                   Object.Equals(dateStamp, anon.DateStamp) Then
                    '//row is a duplicate
                    row.DefaultCellStyle.BackColor = Color.Red
                End If
            Next
        Next
 
    End Sub
 
End Class

What is the backend? SQL Server (2008) could work like this

Select StoreID,DateStamp,GUID,
ROW_NUMBER() OVER (PARTITION BY StoreID,DateStamp ORDER BY StoreID,DateStamp) AS MyRowNum
From TableName

You can hide that last column and then base the highlighting on that column being > 1

Quote:

---

Originally Posted by GaryMazzone

What is the backend?

---

MS SQL Server 2005 (I can't get them to upgrade).

That might work in 2005 also (at least I believe that ROW_NUMBER() was included in that release)

Quote:

---

Originally Posted by ForumAccount

I'm assuming the data is bound correct? Here is a way to do it with LINQ, although it might be better to create a stored procedure to do this, especially with a large dataset.

vb.net Code:

Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles Button1.Click
 
        '//sample datasource
        Dim dt = New DataTable()
        With dt.Columns
            '//add columns
            .Add("StoreId", GetType(Integer))
            .Add("DateStamp", GetType(Date))
            .Add("Guid", GetType(Guid))
        End With
        With dt.Rows
            '//add rows
            .Add(1, Date.Now.Date, Guid.NewGuid())
            .Add(1, Date.Now.Date, Guid.NewGuid())
            .Add(2, Date.Now.AddDays(1.0R).Date, Guid.NewGuid())
            .Add(3, Date.Now.AddDays(2.0R).Date, Guid.NewGuid())
            .Add(2, Date.Now.AddDays(1.0R).Date, Guid.NewGuid())
            .Add(1, Date.Now.AddDays(1.0R).Date, Guid.NewGuid())
        End With
 
        '//set datasource
        Me.DataGridView1.DataSource = dt
 
        '//highlighting code
        Dim query = dt.AsEnumerable() _
                      .GroupBy(Function(key) _
                                   New With {Key .StoreId = _
                                                key.Field(Of Integer)("StoreId"), _
                                             Key .DateStamp = _
                                                key.Field(Of Date)("DateStamp")}) _
                      .Where(Function(g) g.Count() > 1) _
                      .Select(Function(g) g.Key)
 
        '//Sql Translation
        '//  SELECT dt.StoreId, dt.DateStamp
        '//  FROM(dt)
        '//  GROUP BY dt.StoreId, dt.DateStamp
        '//  HAVING (((Count(dt.DateStamp))>1));
 
        For Each row As DataGridViewRow In Me.DataGridView1.Rows
            For Each anon In query
                Dim storeId = row.Cells("StoreId").Value
                Dim dateStamp = row.Cells("DateStamp").Value
                If Object.Equals(storeId, anon.StoreId) AndAlso _
                   Object.Equals(dateStamp, anon.DateStamp) Then
                    '//row is a duplicate
                    row.DefaultCellStyle.BackColor = Color.Red
                End If
            Next
        Next
 
    End Sub
 
End Class

---

Thanks for the code. I think there is something wrong with the way that I am declaring the dataTable. This is what I have thus far:

Code:

---

SEE BELOW FOR UP-TO-DATE CODE

---

Right now, my dgv is completely blank. If I comment out the code, I get my entire table (as normal).

OK, everything is blank because I am creating a new dataTable and assigning it to the dgv. I think it needs to look something closer to this, but AsEnumberable is not available on the BindingSource.

Code:

---

        'TODO: This line of code loads data into the 'AlohadbDataSet.BriadBackup' table. You can move, or remove it, as needed.
        Me.BriadBackupTableAdapter.FillTodaysBackups(Me.AlohadbDataSet.BriadBackup)
        Label4.Text = Me.BriadBackupTableAdapter.getNumberOfBackedUpStores
        Label5.Text = Me.BriadBackupTableAdapter.getNumberOfUniqueStores

        '  HIGHLIGHTING CODE
        '
        Dim dt = BriadBackupBindingSource
        Me.DataGridView1.DataSource = dt
        Dim query = dt.AsEnumerable() _
                  .GroupBy(Function(key) _
                              New With {Key .StoreID = _
                                  key.Field(Of Integer)("StoreID"), _
                                        Key .DateStamp = _
                                        key.Field(Of Date)("DateStamp")}) _
                                .Where(Function(g) g.Count() > 1) _
                                .Select(Function(g) g.Key)

        For Each row As DataGridViewRow In Me.DataGridView1.Rows
            For Each anon In query
                Dim StoreID = row.Cells("StoreID").Value
                Dim DateStamp = row.Cells("DateStamp").Value
                If Object.Equals(StoreID, anon.StoreID) AndAlso _
                    Object.Equals(DateStamp, anon.DateStamp) Then
                    row.DefaultCellStyle.BackColor = Color.Red
                    row.DefaultCellStyle.ForeColor = Color.White

                End If
            Next
        Next

---

Most up-to-date:

Code:

---

        Dim dt = New DataTable()
        ' Me.DataGridView1.DataSource = dt
        Dim query = dt.AsEnumerable() _
                  .GroupBy(Function(key) _
                              New With {Key .StoreID = _
                                  key.Field(Of Integer)("StoreID"), _
                                        Key .DateStamp = _
                                        key.Field(Of Date)("DateStamp")}) _
                                .Where(Function(g) g.Count() > 1) _
                                .Select(Function(g) g.Key)

        For Each row As DataGridViewRow In Me.DataGridView1.Rows
            For Each anon In query
                Dim StoreID = row.Cells("StoreID").Value
                Dim DateStamp = row.Cells("DateStamp").Value
                If Object.Equals(StoreID, anon.StoreID) AndAlso _
                    Object.Equals(DateStamp, anon.DateStamp) Then
                    row.DefaultCellStyle.BackColor = Color.Red
                    row.DefaultCellStyle.ForeColor = Color.White

                End If
            Next
        Next

---