Home > Net >  How to check for duplicate entries in a gridview and prevent them
How to check for duplicate entries in a gridview and prevent them

Time:11-11

I am relatively new to programming and I am having trouble figuring out how to detect and prevent a user from entering in duplicate values into a gridview I have containing multiple controls. Currently I am using a foreach loop to loop through the controls that contain the values that cannot be duplicates and it works for the most part, however when I edit the first record in a table and try to "update" the record, even if I haven't changed any values, I receive a null reference exception and I don't know why. I have attached any pertinent code below and would appreciate any and all help to come to a solution.

Please excuse the long post, I am trying to be as thorough as possible without adding irrelevant information.

To start I have a grid view with a series of controls in it allowing a user to enter information into our database. The gridview and the relevant controls can be seen below.

<asp:GridView ID="GridView2" runat="server"
        EmptyDataText="No Miscellaneous" AutoGenerateColumns="False"
        ShowHeaderWhenEmpty="True" DataKeyNames="RecID"
        ShowFooter="True" DataSourceID="SqlDataSource7" Width="95%"
        OnRowDataBound="OnRowDataBound" >

    <EmptyDataTemplate>
        <asp:DropDownList ID="ddlMiscEquipmentNew" runat="server" DataSourceID="SqlDataSourcePartEquipment" DataValueField="RecID"
            DataTextField="EquipmentPart" AppendDataBoundItems="True" AutoPostBack="True" Width="220px" Height="20px" style="margin-left: 70px;" >
            <asp:ListItem Value="0">---SELECT---</asp:ListItem>
        </asp:DropDownList>
        <asp:DropDownList ID="ddlMiscNew" runat="server" DataSourceID="SqlDataSourceMisc" DataValueField="RecID" DataTextField="Description"
            AppendDataBoundItems="true" Width="220px" Height="20px" AutoPostBack="True" OnSelectedIndexChanged="ddlMiscNew_SelectedIndexChanged"
            OnDataBound="ddlMiscNew_DataBinding">
            <asp:ListItem Value="0">---SELECT---</asp:ListItem>
        </asp:DropDownList>
        <asp:TextBox ID="txtMiscQuantityNew" runat="server" Width="95px" Style="margin-left: 68px;" AutoPostBack="True" OnTextChanged="txtMiscQuantityNew_TextChanged" />
        <asp:TextBox ID="txtMiscCostNew" runat="server" Width="95px" Text="0" ReadOnly="True" />
        <asp:TextBox ID="txtMiscRequestedNew" runat="server" Width="95px" Text="0" ReadOnly="True" />
        <asp:TextBox ID="txtMiscTaxNew" runat="server" Width="95px" Text="0" />
        <asp:Button ID="InsertDetail" runat="server" CommandName="InsertDetail" Height="25px" Text="Add Detail" Width="85px" UseSubmitBehavior="True" />
    </EmptyDataTemplate>

    <AlternatingRowStyle BackColor="#CCCCCC" />
        <Columns>
            <asp:CommandField ShowEditButton="True" FooterText="Add -->" ShowDeleteButton="true" HeaderStyle-Width="70px" />
            <asp:BoundField DataField="RecID" HeaderText="RecID" SortExpression="RecID" ReadOnly="True" Visible="False" />

            <asp:TemplateField HeaderText="Miscellaneous" ItemStyle-HorizontalAlign="center">
                <ItemTemplate>
                    <asp:Label ID="lblMiscEquipmentAdd" Text='<%# Bind("EquipmentType") %>' runat="server" Width="220px" />
                    <asp:Label ID="lblMiscDescriptionAdd" Text='<%# Bind("MiscDescription") %>' runat="server" Width="220px" />
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:DropDownList ID="ddlMiscEquipmentEdit" runat="server" DataSourceID="SqlDataSourcePartEquipment" DataValueField="RecID" DataTextField="EquipmentPart" SelectedValue='<%# Eval("EquipmentID") %>'
                        AppendDataBoundItems="True" AutoPostBack="True" Width="220px" Height="20px">
                        <asp:ListItem Value="0">---SELECT---</asp:ListItem>
                    </asp:DropDownList>
                    <asp:DropDownList ID="ddlMiscEdit" runat="server" DataSourceID="SqlDataSourceMisc" DataValueField="RecID" DataTextField="Description" SelectedValue='<%# Eval("MiscType") %>' AppendDataBoundItems="True" Width="220px" Height="20px" AutoPostBack="True" OnSelectedIndexChanged="ddlMiscEdit_SelectedIndexChanged">
                        <asp:ListItem Value="0">---SELECT---</asp:ListItem>
                    </asp:DropDownList>
                </EditItemTemplate>
                <FooterTemplate>
                    <asp:DropDownList ID="ddlMiscEquipmentInsert" runat="server" DataSourceID="SqlDataSourcePartEquipment" DataValueField="RecID" DataTextField="EquipmentPart"
                        AppendDataBoundItems="True" Width="220px" Height="20px" style="margin-left: 29px;">
                        <asp:ListItem Value="0">---SELECT---</asp:ListItem>
                    </asp:DropDownList>
                    <asp:DropDownList ID="ddlMiscInsert" runat="server" DataSourceID="SqlDataSourceMisc" DataValueField="RecID" DataTextField="Description" AppendDataBoundItems="True" Width="220px" Height="20px" AutoPostBack="True" OnSelectedIndexChanged="ddlMiscInsert_SelectedIndexChanged">
                        <asp:ListItem Value="0">---SELECT---</asp:ListItem>
                    </asp:DropDownList>
                </FooterTemplate>
            </asp:TemplateField>

There are more controls in the gridview but these are where the problem lies. As you can see there are a number of drop down lists and textboxes, the controls in question are the drop down list "ddlMiscEdit" in the edit item template and the label "lblMiscDescriptionAdd". When the user makes a selection in the aforementioned ddl and proceeds to hit the insert detail button at the end of each row it adds the values to the gridview, and in turn they are added to my database through a row command event. This works as intended, the problem I run into is when a user trys to edit the first row of the gridview and changes the value for ddlMiscEdit, it triggers a null reference exception for the label lblMiscDescriptionAdd.

In order to detect duplicates I have a For Each loop in the selectedIndexChanged event for the ddlMiscEdit drop down as shown below.

 For Each row In GridView2.Rows
        Dim miscdescription As Label = (CType(row.FindControl("lblMiscDescriptionAdd"), Label))
        If miscdescription.Text = ddlMiscEdit.SelectedItem.Text Then
            lblErrorMisc.Text = "Miscellaneous items can only be claimed once"
            Return
        Else
            lblErrorMisc.Text = ""
            Return
        End If
    Next

I am thinking this exception has to do with the fact that when the user clicks edit it activates the drop down list and hides the label, thus when it comes to the loop "lblMiscDescriptionAdd" doesn't exist in that specific context.

I have also tried putting the foreach loop in the row command event for the gridview in the "Update" commandName section, which leads to the same issue but in a different place. When this loop is in the Update section mentioned above, instead of receiving this exception on the first record of the gridview I receive it in the second/third/fourth record, basically everything except the first record.

I use that same foreach loop in the InsertDetail command and it works as intended preventing duplicate values from being entered into the gridview, the exception only appears when I am trying to edit a given row depending on where I put the loop.

I apologize for the long post but I don't really know how to simplify my issue beyond what I have done here. I am pretty new to programming so I don't know if I could go about doing this in a better/more efficient manner, but any help/critiques would be greatly appreciated as I am at a loss as to how to fix this problem. Thank you in advance for any effort on your part for helping me solve this.

CodePudding user response:

To make a long story short?

Don't try and process the GV.What happens if other users are adding data. You may well not have re-loaded the grid with updated data.

In your save code for the one record, simple hit/pull/query the main database and check for the record. That way you not messing around all day trying to process and pull values out of the GV.

Always try to think in terms of data management, not trying to use the actual GV as some kind of database - it only for display, and selecting the data to edit, but to check for duplicates, then simple query the database for that information and check if such records already exist.

For example, I have a simple GV with some hotels. (that working example you can try here: enter image description here

  • Related