DGV Styles

[mbutler755]

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.

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

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

[GaryMazzone]

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

[mbutler755]

What is the backend?

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

[GaryMazzone]

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

[mbutler755]

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.

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:

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

[mbutler755]

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.

        '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

[mbutler755]

Most up-to-date:

        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