When Gridview column is hidden, and update button is clicked, it tries to insert null value for that hidden field

Question

Problem is similar to the indicated here: http://forums.asp.net/t/945656.aspx/1

I have a gridview with update button which updates the row.

When a column is hidden, it tries to insert null values to database and update the current data with null. I don't want that, I want to leave the current data as it is.

Below is my sqldatasource

<asp:SqlDataSource ID="sds_OrderDetail" runat="server" ConnectionString="<%$ ConnectionStrings:MyDbConn %>"

    DeleteCommand="DELETE FROM [OrderDetail] WHERE [RowNo] = @RowNo" InsertCommand="INSERT INTO [OrderDetail] ([Id], [PONumber], [MaterialCode], [MaterialDescription], [MaterialCategory], [UnitOfMeasure], [Quantity], [ContainerId], [LoadingDate], [CutOffDate], [TransporterName], [BookingNo]) VALUES (@Id, @PONumber, @MaterialCode, @MaterialDescription, @MaterialCategory, @UnitOfMeasure, @Quantity, @ContainerId, @LoadingDate, @CutOffDate, @TransporterName, @BookingNo)"

    SelectCommand="SELECT * FROM [OrderDetail] WHERE ([Id] = @Id)" UpdateCommand="UPDATE [OrderDetail] SET [Id] = @Id, [PONumber] = @PONumber, [MaterialCode] = @MaterialCode, [MaterialDescription] = @MaterialDescription, [MaterialCategory] = @MaterialCategory, [UnitOfMeasure] = @UnitOfMeasure, [Quantity] = @Quantity, [ContainerId] = @ContainerId, [LoadingDate] = @LoadingDate, [CutOffDate] = @CutOffDate, [TransporterName] = @TransporterName, [BookingNo] = @BookingNo WHERE [RowNo] = @RowNo"

    OnSelected="sds_OrderDetail_Selected">

    <DeleteParameters>

        <asp:Parameter Name="RowNo" Type="Int32" />

    </DeleteParameters>

    <InsertParameters>

        <asp:SessionParameter DefaultValue="-1" Name="Id" SessionField="Id" Type="Int32" />

        <asp:Parameter Name="PONumber" Type="String" />

        <asp:Parameter Name="MaterialCode" Type="String" />

        <asp:Parameter Name="MaterialDescription" Type="String" />

        <asp:Parameter Name="MaterialCategory" Type="String" />

        <asp:Parameter Name="UnitOfMeasure" Type="String" />

        <asp:Parameter Name="Quantity" Type="String" />

        <asp:Parameter Name="ContainerId" Type="String" />

        <asp:Parameter Name="LoadingDate" Type="String" />

        <asp:Parameter Name="CutOffDate" Type="String" />

        <asp:Parameter Name="TransporterName" Type="String" />

        <asp:Parameter Name="BookingNo" Type="String" />

    </InsertParameters>

    <SelectParameters>

        <asp:SessionParameter DefaultValue="-1" Name="Id" SessionField="Id" Type="Int32" />

    </SelectParameters>

    <UpdateParameters>

        <asp:SessionParameter DefaultValue="-1" Name="Id" SessionField="Id" Type="Int32" />

        <asp:Parameter Name="PONumber" Type="String" />

        <asp:Parameter Name="MaterialCode" Type="String" />

        <asp:Parameter Name="MaterialDescription" Type="String" />

        <asp:Parameter Name="MaterialCategory" Type="String" />

        <asp:Parameter Name="UnitOfMeasure" Type="String" />

        <asp:Parameter Name="Quantity" Type="String" />

        <asp:Parameter Name="ContainerId" Type="String" />

        <asp:Parameter Name="LoadingDate" Type="String" />

        <asp:Parameter Name="CutOffDate" Type="String"  />

        <asp:Parameter Name="TransporterName" Type="String" />

        <asp:Parameter Name="BookingNo" Type="String" />

        <asp:Parameter Name="RowNo" Type="Int32" />

    </UpdateParameters>

</asp:SqlDataSource>

below is my GridView

<cc1:BulkEditGridView ID="begv_OrderDetail" runat="server" AutoGenerateColumns="False"
    DataKeyNames="RowNo" DataSourceID="sds_OrderDetail" SaveButtonID="btn_SaveAll"
    BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"
    CellPadding="3" OnRowCommand="begv_OrderDetail_RowCommand" OnRowUpdated="begv_OrderDetail_RowUpdated"
    OnRowDeleted="begv_OrderDetail_RowDeleted" OnRowCreated="begv_OrderDetail_RowCreated">
    <Columns>
        <asp:TemplateField ShowHeader="False">
            <ItemTemplate>
                <asp:LinkButton ID="lbt_Update" runat="server" CausesValidation="false" CommandName="Update"
                    Text="Kaydet"></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField ShowHeader="False">
            <ItemTemplate>
                <asp:LinkButton ID="lbt_Copy" runat="server" CausesValidation="false" CommandName="Copy"
                    CommandArgument='<%# Container.DataItemIndex %>' Text="Kopyala" Visible='<%# (Roles.IsUserInRole("Planlamacı") && Session["Status"].ToString()=="1")||(Roles.IsUserInRole("Depocu") && Session["Status"].ToString()=="2") %>'></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField ShowHeader="False">
            <ItemTemplate>
                <asp:LinkButton ID="lbt_Delete" runat="server" CausesValidation="false" CommandName="Delete"
                    Text="Sil" Visible='<%# (Roles.IsUserInRole("Planlamacı") && Session["Status"].ToString()=="1")||(Roles.IsUserInRole("Depocu") && Session["Status"].ToString()=="2") %>'></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" ReadOnly="true" />
        <asp:TemplateField HeaderText="PONumber" SortExpression="PONumber">
            <EditItemTemplate>
                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("PONumber") %>' Enabled="false"
                    Width="94%"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="MaterialCode" SortExpression="MaterialCode">
            <EditItemTemplate>
                <asp:DropDownList ID="ddl_MaterialCode" runat="server" DataSourceID="sds_MaterialCodes"
                    DataTextField="MaterialCode" DataValueField="MaterialCode" SelectedValue='<%# Bind("MaterialCode") %>'
                    OnSelectedIndexChanged="ddl_MaterialCode_SelectedIndexChanged" Width="100%" AppendDataBoundItems="true"
                    Enabled='<%# (Roles.IsUserInRole("Planlamacı") && Session["Status"].ToString()=="1")||(Roles.IsUserInRole("Depocu") && Session["Status"].ToString()=="2") %>'>
                    <asp:ListItem Text="" Value=""></asp:ListItem>
                </asp:DropDownList>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="MaterialDescription" SortExpression="MaterialDescription">
            <EditItemTemplate>
                <asp:TextBox ID="txt_MaterialDescription" runat="server" Text='<%# Bind("MaterialDescription") %>'
                    Enabled="false"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="MaterialCategory" SortExpression="MaterialCategory">
            <EditItemTemplate>
                <asp:TextBox ID="txt_MaterialCategory" runat="server" Text='<%# Bind("MaterialCategory") %>'
                    Enabled="false" Width="94%"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="UnitOfMeasure" SortExpression="UnitOfMeasure">
            <EditItemTemplate>
                <asp:DropDownList ID="ddl_UnitOfMeasure" runat="server" DataSourceID="sds_UnitOfMeasure"
                    DataTextField="UnitOfMeasure" DataValueField="UnitOfMeasure" SelectedValue='<%# Bind("UnitOfMeasure") %>'
                    Width="94%" Enabled='<%# (Roles.IsUserInRole("Planlamacı") && Session["Status"].ToString()=="1")||(Roles.IsUserInRole("Depocu") && Session["Status"].ToString()=="2") %>'>
                </asp:DropDownList>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Quantity" SortExpression="Quantity">
            <EditItemTemplate>
                <asp:TextBox ID="txt_Quantity" runat="server" Text='<%# Bind("Quantity") %>' Width="94%"
                    Enabled='<%# (Roles.IsUserInRole("Planlamacı") && Session["Status"].ToString()=="1")||(Roles.IsUserInRole("Depocu") && Session["Status"].ToString()=="2") %>'></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ContainerId" SortExpression="ContainerId">
            <EditItemTemplate>
                <asp:DropDownList ID="ddl_ContainerId" runat="server" DataSourceID="sds_Containers"
                    DataTextField="ContainerId" DataValueField="ContainerId" SelectedValue='<%# Bind("ContainerId") %>'
                    Enabled='<%# Roles.IsUserInRole("Depocu") && Session["Status"].ToString()=="2" %>'
                    Width="100%" AppendDataBoundItems="true">
                    <asp:ListItem Text="" Value=""></asp:ListItem>
                </asp:DropDownList>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="FileNo" SortExpression="FileNo">
            <EditItemTemplate>
                <asp:TextBox ID="txt_FileNo" runat="server" Text='<%# Bind("FileNo") %>' Enabled="false"
                    Width="90%"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="LoadingDate" SortExpression="LoadingDate">
            <EditItemTemplate>
                <asp:TextBox ID="txt_LoadingDate" runat="server" Text='<%# Bind("LoadingDate", "{0:yyyy-MM-dd}") %>'
                    Enabled='<%# Roles.IsUserInRole("İhracat Uzmanı") && Session["Status"].ToString()=="3" %>'
                    Width="94%"></asp:TextBox>
                <asp:CalendarExtender ID="ce_LoadingDate" runat="server" TargetControlID="txt_LoadingDate"
                    Format="yyyy-MM-dd">
                </asp:CalendarExtender>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="CutOffDate" SortExpression="CutOffDate">
            <EditItemTemplate>
                <asp:TextBox ID="txt_CutOffDate" runat="server" Text='<%# Bind("CutOffDate", "{0:yyyy-MM-dd}") %>'
                    Enabled='<%# Roles.IsUserInRole("İhracat Uzmanı") && Session["Status"].ToString()=="3" %>'
                    Width="94%"></asp:TextBox>
                <asp:CalendarExtender ID="ce_CutOffDate" runat="server" TargetControlID="txt_CutOffDate"
                    Format="yyyy-MM-dd">
                </asp:CalendarExtender>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="TransporterName" SortExpression="TransporterName">
            <EditItemTemplate>
                <asp:DropDownList ID="ddl_Transporters" runat="server" DataSourceID="sds_Transporters"
                    DataTextField="TransporterName" DataValueField="TransporterName" SelectedValue='<%# Bind("TransporterName") %>'
                    Width="94%" AppendDataBoundItems="true" Enabled='<%# (Roles.IsUserInRole("Planlamacı") && Session["Status"].ToString()=="1")||(Roles.IsUserInRole("Depocu") && Session["Status"].ToString()=="2") %>'>
                    <asp:ListItem Text="" Value=""></asp:ListItem>
                </asp:DropDownList>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="BookingNo" SortExpression="BookingNo">
            <EditItemTemplate>
                <asp:TextBox ID="txt_BookingNo" runat="server" Text='<%# Bind("BookingNo") %>' Enabled="false"
                    Width="94%"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ContainerNo" SortExpression="ContainerNo">
            <EditItemTemplate>
                <asp:TextBox ID="txt_ContainerNo" runat="server" Text='<%# Bind("ContainerNo") %>'
                    Enabled="false" Width="94%"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ReasonCode" SortExpression="ReasonCode">
            <EditItemTemplate>
                <asp:TextBox ID="txt_ReasonCode" runat="server" Text='<%# Bind("ReasonCode") %>'
                    Enabled="false" Width="94%"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Comment" SortExpression="Comment">
            <EditItemTemplate>
                <asp:TextBox ID="txt_Comment" runat="server" Text='<%# Bind("Comment") %>' Enabled="false"
                    Width="94%"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
    </Columns>
    <FooterStyle BackColor="White" ForeColor="#000066" />
    <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
    <RowStyle ForeColor="#000066" />
    <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#007DBB" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#00547E" />
</cc1:BulkEditGridView>

I used the below solution to hide a column

https://stackoverflow.com/a/4954976/1235655

Answer 1

I have in the past, solved this by using IsNull() for each of the hidden columns in the UpdateCommand

e.g.

If PONumber is a hidden Column, use ISNULL(@PONumber,PONumber) inplace of @PONumber

UpdateCommand="UPDATE [OrderDetail] SET [Id] = @Id, [PONumber] = @PONumber, [MaterialCode] = @MaterialCode, [MaterialDescription] = @MaterialDescription, [MaterialCategory] = @MaterialCategory, [UnitOfMeasure] = @UnitOfMeasure, [Quantity] = @Quantity, [ContainerId] = @ContainerId, [LoadingDate] = @LoadingDate, [CutOffDate] = @CutOffDate, [TransporterName] = @TransporterName, [BookingNo] = @BookingNo WHERE [RowNo] = @RowNo"

PS, the reason the a hidden columns value is NULL on an update is because hiding a column means it isn't in the DOM or rendered.

An alternative approach would be to use a CSS class or style (Display: None) so that the column(s) are still generated however, beware you may be sending sensitive information in the HTML.

Answer 2

Adding the hidden columns value as datakeyname solves the issue.

Currently it is

DataKeyNames="RowNo"

Changing it to

DataKeyNames="RowNo,CutOffDate" 

solved it.