asp.net VB - Compare two gridviews and change cell colour.

[darkgen]

asp.net VB - Compare two gridviews and change cell colour based on unique / duplicate values.

I'm trying to perform a basic compare between two identically formatted gridviews where column1, which contains a value, is either unique or a duplicate of column1 in gridview2.

Suffice to say; I'm scratching my head. the gridviews are populated based on a sqldatasource control from a dropdownlist. That aspect works fine and for the moment I'll park trying to do that dynamically with whatever value is in the dropdownlist via autopostback.

So, to help myself I'll use a button1_click as the trigger to compare whatever is currently presented in each gridview.

   Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim arrayList1 As ArrayList = New ArrayList()
        Dim arrayList2 As ArrayList = New ArrayList()
        Dim arrayList3 As ArrayList = New ArrayList()
        Dim a As Integer
        Dim b As Integer
        For a = 0 To GridView1.Rows.Count - 1 Step 1
            For b = 0 To GridView1.Columns.Count - 1 Step 1
                arrayList1.Add(GridView1.Rows(a).Cells(b).Text)
            Next
        Next

        Dim c As Integer
        Dim d As Integer
        For c = 0 To GridView2.Rows.Count - 1 Step 1
            For d = 0 To GridView2.Columns.Count - 1 Step 1
                arrayList2.Add(GridView2.Rows(c).Cells(d).Text)
            Next
        Next

        If Not arrayList2.Contains(arrayList1) Then
            MsgBox("True")

        Else
            MsgBox("False")
        End If

    End Sub

I know the above is nonsense but it's the best start-point I can find with my google fu. I'm just trying to fathom how I a) go about performing the compare and b) perform the cell colour change based on whether it matches or not.

   Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
     
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim view As DataRowView = DirectCast(e.Row.DataItem, DataRowView)
            Select Case Convert.ToString(view("CompareColumn"))
                Case "Low"
                    e.Row.Cells(8).BackColor = System.Drawing.Color.PaleGreen
                    Exit Select
                Case "Medium"
                    e.Row.Cells(8).BackColor = System.Drawing.Color.LightYellow
                    Exit Select
                Case "High"
                    e.Row.Cells(8).BackColor = System.Drawing.Color.Pink
                    Exit Select
            End Select
        End If
End Sub

I've only previously managed formatting within a sub and so I am unclear how you reference changes in cell behaviour outside of one.

I know it's a shambles; any pointers would be most appreciated.

TIA, Paul!

 

[touch]

I've taken your code and made some changes:

   Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim arrayList1 As ArrayList = New ArrayList()
 
        //Go through all values in GridView2 column1 and add them to an array
        Dim c As Integer
        For c = 0 To GridView2.Rows.Count - 1 Step 1
            arrayList1.Add(GridView2.Rows(c).Cells(1).Text)
        Next

        //Go through all values in GridView1 column1 and compare them to the array of gridview2 values
        Dim a As Integer
        For a = 0 To GridView1.Rows.Count - 1 Step 1
            If arrayList1.Contains(GridView1.Rows(a).Cells(1).Text) Then
               //If the text in column1 of this row is in the array, set the cell background to red
               GridView1.Rows(a).Cells(1).BackColor = System.Drawing.Color.Red
        Next

    End Sub

This is all untested and probably needs some cleaning up to work.

I would say that it's probably best to do the comparing at the point when you get the data from the database and include a hidden column in the gridviews to mark if they are duplicates or not but I've tried to keep the code similar to what you already had so that it's easier to understand.

 

[darkgen]

You absolute delight!

I need to play some more but this is great. Next up get it up refresh on AutoPostBack from the dropdownlist.

thank you.

e: yes, understood. in actuality the gridview is populated from the same sqlquery but with slightly different where conditions for the compare. I know it's a very silly way to this.

Next up; remove the need for the button and have the compare code run on the dropdownlist autopostback!

 

[touch]

If you update your query for GridView1 to:
SELECT
<<current selected columns>>
,column1 IN (SELECT column1 FROM <<rest of query 2>>) as 'duplicate'

Add this 'duplicate' column to your GridView but don't display it on screen and set the background of the cell based on the value in this column. eg if row(1).cell('duplicate') == true Then row(1).background = red
That'll work in the current setup and also once you get dynamic loading too

 

[darkgen]

Hmm I got this far but the execution complains of returning more than 1 value.

SELECT        REPLACE(LastConnectionUser, 'DOMAIN\', '') AS PrimUser, DNSName, DLU, DGN,
                             (SELECT        REPLACE(LastConnectionUser, 'DOMAIN\', '') AS Duplicate
                               FROM            view_Broker
                               WHERE        (CMDBuild = 1803) AND (DGN = @DGN)) AS Duplicate
FROM            view_Broker AS view_Broker_1
WHERE        (CMDBuild = 7) AND (DLU < 8) AND (DGN = @DGN)

Back to the other, less efficient method!

dropdownlist

  <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="DGN" DataValueField="DGN" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"></asp:DropDownList>

code behind

Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged
        Dim arrayList1 As ArrayList = New ArrayList()
        Dim arrayList2 As ArrayList = New ArrayList()

        Dim c As Integer
        For c = 0 To GridView2.Rows.Count - 1 Step 1
            arrayList1.Add(GridView2.Rows(c).Cells(0).Text)
        Next


        Dim a As Integer
        For a = 0 To GridView1.Rows.Count - 1 Step 1
            If arrayList1.Contains(GridView1.Rows(a).Cells(0).Text) Then
                GridView1.Rows(a).BackColor = System.Drawing.Color.Pink
            Else
                GridView1.Rows(a).BackColor = System.Drawing.Color.LightGreen
            End If
        Next

        Dim d As Integer
        For d = 0 To GridView1.Rows.Count - 1 Step 1
            arrayList2.Add(GridView1.Rows(d).Cells(0).Text)
        Next


        Dim b As Integer
        For b = 0 To GridView2.Rows.Count - 1 Step 1
            If arrayList2.Contains(GridView2.Rows(b).Cells(0).Text) Then
                GridView2.Rows(b).BackColor = System.Drawing.Color.Pink
            Else
                GridView2.Rows(b).BackColor = System.Drawing.Color.LightGreen
            End If
        Next
    End Sub

but the code doesn't fire.

 

[touch]

Is it LastConnectionUser that you want to check for duplicates?
Try this:

SELECT        REPLACE(LastConnectionUser, 'DOMAIN\', '') AS PrimUser, DNSName, DLU, DGN,
                             LastConnectionUser IN (SELECT LastConnectionUser
                               FROM            view_Broker
                               WHERE        (CMDBuild = 1803) AND (DGN = @DGN)) AS Duplicate
FROM            view_Broker AS view_Broker_1
WHERE        (CMDBuild = 7) AND (DLU < 8) AND (DGN = @DGN)

 

[darkgen]

Hmm complains about incorrect syntax near the keyword "IN" and the scaler variable not being declared (@DGN).