How can you check for duplicate values during the databind of Gridview?

robnhood00
robnhood00 used Ask the Experts™
on
I have a gridview that returns no more than 40 records from a database using a stored procedure in SqlDataSource.  Within the returned values, I need to check for duplicate value (First Name) and render the value (First Name) in different color on the gridview.  Currently, the value is set in a text property of a label using 'Eval("FirstName")'.  Each record has a unique DataKeyName values.  

Thank you for your assistance.  

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

There are various ways to do that.

1. If you are/can getting the sorted result-set of "FirstName" from query then you can cater it by implementing DataRowBound Event. (You may also sort with the dataset.tables.sort("...") method.)

2. Another way is to write a method which takes the gridview as input parameter and duplicate-column as the second parameter which will color the duplicate row.

You may paste your code here for further assistance.

Kind Regards,
Aksh

Commented:
Hi,

I just wrote a code which can solve your issue.

1. Call the method after DataBind()
2. Pass the grid and columnNumber to the method.
3. If your code is in vb.net then use following link to convert.
http://www.developerfusion.com/tools/convert/csharp-to-vb/.

//////////////////////// CODE STARTS //////////////////////////////////////
private void ProcessGrid(GridView gv, int columnNumber)
    {
        if (gv == null)
            return;
        if (gv.Rows.Count < 2)
            return;

        for (int i = 0; i < gv.Rows.Count ; i++)
        {
            for (int j = i+1; j < gv.Rows.Count ; j++)
            {
                if (gv.Rows[i].Cells[columnNumber].Text  == gv.Rows[j].Cells[columnNumber].Text )
                {
                    //Aksh: If you want whole row to display in different color
                    gv.Rows[i].BackColor = System.Drawing.Color.Red;
                    gv.Rows[j].BackColor = System.Drawing.Color.Red;
                    //Aksh: If you JUST want cell to display in diff color
                    gv.Rows[i].Cells[columnNumber].BackColor = System.Drawing.Color.Yellow;
                    gv.Rows[j].Cells[columnNumber].BackColor = System.Drawing.Color.Yellow;
                }
            }
        }
    }
///////////////////////////////////////////////// CODE ENDS  /////////////////////////////////

Kind Regards,
Hardik

Author

Commented:
Hello Aksh,

Thank you for your reply.  

I already do have a DataRowBound Event that is used for a different purpose.  It hides certains rows in my gridview (see below).  Currently, all the information is displaying properly as it should.  However, I just don't know how to implement this last feature of duplicate value check in this event.  If the result set that's returned have "John" in records #10 and #25, will I be able to check previous rows at #25 or look at rows not yet created at #10?  Do I have to hold the result set in a different "container" (array, dataset, etc.) and loop through each one checking for duplicates?  If so, how can I accomplish this?  In what event?  Ultimately, I would need to have both of these values/text/itemtemplate in rows #10 and #25 rendered in RED, lets say.  Please note that people in the result set is assigned to a specific location and is ordered/sorted/returned by this location.

Thank you again for your help.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ dbConn HERE %>"
        SelectCommandType="StoredProcedure" SelectCommand="qry..Info" 
        UpdateCommandType="StoredProcedure" UpdateCommand="prc..UpdateHere">
        <UpdateParameters>
        </UpdateParameters>
        <SelectParameters>
            <asp:ControlParameter Name="location" ControlID="lblLoc" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>


		<asp:GridView ID="gvStationInfo" runat="server" DataKeyNames="visitID" DataSourceID="SqlDataSource1"  AutoGenerateColumns="false"
                    CellPadding="5" Font-Size="12px" Font-Strikeout="False" ForeColor="#333333" orderColor="Black"  ShowFooter="False" 
                    AllowSorting="true" GridLines="none" CssClass="tblData" AlternatingRowStyle-BackColor="#AADDFF" >
                    <Columns>
                        <asp:TemplateField SortExpression="bedName" HeaderText="Room"
                         HeaderStyle-HorizontalAlign="Center" HeaderStyle-CssClass="sortLink" ItemStyle-CssClass="black" HeaderStyle-Wrap="false">
                            <ItemTemplate>
                                <%#Eval("bedName")%>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField SortExpression="patientName" HeaderText="Patient"
                         HeaderStyle-HorizontalAlign="Center" HeaderStyle-CssClass="sortLink" ItemStyle-CssClass="black" HeaderStyle-Wrap="false" ItemStyle-Wrap="false">
                            <ItemTemplate>
                                <span id='spnPatientName1' class='<%# getServiceColorCSS(Eval("serviceCodeInt")) %>'><asp:Label ID="lblPatientName1" runat="server" Text='<%#Eval("patientName")%>'></asp:Label></span>
                            </ItemTemplate>
                        </asp:TemplateField>
                        ...
                        ...
                        ...
                    </Columns>
                </asp:GridView>


Protected Sub gvStationInfo_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvStationInfo.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim bedName As String '= CType(e.Row.DataItem, String)
            Dim locationCode As Integer

            bedName = DataBinder.Eval(e.Row.DataItem, "bedName").ToString()  
            locationCode = DataBinder.Eval(e.Row.DataItem, "locationCode")
           
            If locationCode = 5 Then
                If bedName >= ??? Then
                    e.Row.Visible = False
                End If
            ElseIf locationCode = 6 Then
                If bedName > ??? Then
                    e.Row.Visible = False
                End If
            End If
        End If
    End Sub

Open in new window

Author

Commented:
Hello Hardik,

Thank you for your suggestion.  The "logic" of your code is understood.  However, I am not getting any values with...   gv.Rows[i].Cells[columnNumber].Text  == gv.Rows[j].Cells[columnNumber].Text

Both of these cell TEXT is returning zero-length (blank) text even though the gridview is displaying the correct data.  I have tried it using most of the columnNumber in my gridview.  Can you provide any insights as to WHY it is not returing any string values?  Thanks.

Regards,

Author

Commented:
By the way, I am executing your ProcessGrid() method on the gridview's DATABOUND event just in case you are wondering if this method is executed before the databind.  Thank you again for your assistance in advance.  You have definitely pointed me in the right direction.  

Commented:
Hi,

Sorry, I couldnt answer you earlier as I was bit busy.

The ProcessGrid(...) method should be executed after following statement in your code.
gvStationInfo.DataBind()
The purpose the method is to change the grid after its "already" filled with the data. I should have given the method name as "PostProcessGrid()". You should not call the method in the "ROWDATABOUND" event handler.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
As I have mentioned in the first post that you can do the functionality in the RowDataBound() event handler also but it would require different approach. If you want to go with that approach then:
1. You should sort the data-source for faster result.
2. Declare shared variable on the page to remember the last row's "firstName" value from which you can determine wheather the row already bounded with the same value.
3. And then implement the code in RowDataBound...

I guess, if the ProcessGrid(...) works fine for you then you may stick with it... but take your call if you really love DataRowBound(..).... :)))

Have fun..
Aksh



Author

Commented:
Hello Aksh,

Thank you for your reply.  I don't have the gvStationInfo.DataBind() code written in the codebehind.  The gridview is populated from the SqlDataSource code on the ASPX page.  I don't completely understand the order of execution...  thus, I added the ProcessGrid(...)  on the DATABOUND event on the codebehind.  It is still not bringing back any data on the...  IF gv.Rows(i).Cells(columnNumber).Text = gv.Rows(j).Cells(columnNumber).Text Then (I converted to VB.net) ...  even though the gridview is populated properly with appropriate data on the databind.  Both comparison values are blank, empty strings.  Any further suggestion will be highly appreciated.  Thank you very much for all the help you provided so far.  

Regards,

Author

Commented:
Hello Aksh,

Thank you for all your help.  I finally got your code to work.  It would bring back any cell text value just "as-is" so I had to use a label (itemtemplate) and use findcontrol to retrieve the text value and compare.  I appreciate your patience and help.  You've been great...  Thank you.

Regards,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial