1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'HTML, Graphics & Programming' started by darkgen, Nov 15, 2019.

  1. darkgen

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,434

    Location: London, UK

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

    Code:
       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!
     
  2. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 11,175

    Location: Sufferlandria

    I've taken your code and made some changes:

    Code:
       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.
     
    Last edited: Nov 15, 2019
  3. darkgen

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,434

    Location: London, UK

    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!
     
    Last edited: Nov 15, 2019
  4. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 11,175

    Location: Sufferlandria

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

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,434

    Location: London, UK

    Hmm I got this far but the execution complains of returning more than 1 value.
    Code:
    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
    Code:
      <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="DGN" DataValueField="DGN" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"></asp:DropDownList>
    code behind
    Code:
    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.
     
  6. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 11,175

    Location: Sufferlandria

    Is it LastConnectionUser that you want to check for duplicates?
    Try this:
    Code:
    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)
     
  7. darkgen

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,434

    Location: London, UK

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