Member Avatar for Texpert
Texpert 0 Newbie Poster

Hi,

I am having problem with my Editable GridView. I am selecting columns from SQL-Server 2005 DB using a store procedure [with multiple tables in it] and then I wrote UPDATE and DELTE statements in the GridView query builder.

When I run the page, Select and delete works fine but while updating the row in database , it inserts all the NULLS for those columns whose values are not changed in the Grid.

Please see the attached code for review
============================================

<asp:GridView ID="GridView2" runat="server" AllowSorting="True" AutoGenerateColumns="False"
        DataKeyNames="RegID" DataSourceID="SqlDataSource2" Style="z-index: 102; left: 453px;
        position: absolute; top: 305px">
        <Columns>
            <asp:CommandField ButtonType="Image" CancelImageUrl="~/Images/icon-pencil-x.gif"
                DeleteImageUrl="~/Images/icon-delete.gif" EditImageUrl="~/Images/icon-pencil.gif"
                ShowDeleteButton="True" ShowEditButton="True" UpdateImageUrl="~/Images/icon-floppy.gif" />
            <asp:BoundField DataField="RegID" HeaderText="RegID" InsertVisible="True" ReadOnly="True"
                SortExpression="RegID" Visible="False" />
            <asp:BoundField DataField="STU_ID" HeaderText="STU_ID" SortExpression="STU_ID" Visible="False" />
            <asp:BoundField DataField="Name" HeaderText="Name" ReadOnly="True" SortExpression="Name" />
            <asp:BoundField DataField="Branch_ID" HeaderText="Branch_ID" SortExpression="Branch_ID"
                Visible="False" />
            <asp:BoundField DataField="Branch_Name" HeaderText="Branch_Name" SortExpression="Branch_Name" />
            <asp:BoundField DataField="AcademicYear_ID" HeaderText="AcademicYear_ID" SortExpression="AcademicYear_ID"
                Visible="False" />
            <asp:BoundField DataField="Academic_Year" HeaderText="Academic_Year" SortExpression="Academic_Year" />
            <asp:BoundField DataField="Registration_Type" HeaderText="Registration_Type" SortExpression="Registration_Type" />
            <asp:BoundField DataField="regdate" HeaderText="regdate" ReadOnly="True" SortExpression="regdate" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:KPLCOneSourceConnectionString %>"
        OldValuesParameterFormatString="original_{0}" DeleteCommand="DELETE FROM Registration WHERE (RegID = @Original_RegID)"
        SelectCommand="GetRegDetails" SelectCommandType="StoredProcedure" UpdateCommand="UPDATE Registration SET Branch_ID = @Branch_ID, AcademicYear_ID = @AcademicYear_ID, Registration_Type = @Registration_Type, Registartion_Date = @Registration_Date WHERE (RegID = @original_RegID)">
        <DeleteParameters>
            <asp:Parameter Name="Original_RegID" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="Branch_ID" />
            <asp:Parameter Name="AcademicYear_ID" />
            <asp:Parameter Name="Registration_Type" />
            <asp:Parameter Name="Registration_Date" />
            <asp:Parameter Name="original_RegID" />
        </UpdateParameters>
        <SelectParameters>
            <asp:ControlParameter ControlID="GridView1" Name="vStudent_ID" PropertyName="SelectedValue"
                Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

==================================
select statement in Store Proc is

SELECT RegID,
Registration.Student_ID as STU_ID,
Last_Name + ' ' + First_Name as Name,
Registration.Branch_ID,
Branch_Name,Registration.AcademicYear_ID,
Academic_Year,
Registration_Type,
ISNULL(CONVERT(nvarchar(11),Registartion_Date , 109),' / / ') as regdate,
Registration.UpdatedBy,
ISNULL(CONVERT(nvarchar(11),Registration.UpdatedDate , 109),' / / ') as EnterDate
from Registration
INNER JOIN Student on Registration.Student_ID = student.Student_ID
INNER JOIN Branch on Registration.Branch_ID = Branch.Branch_ID
INNER JOIN Academic_Year on Registration.AcademicYear_ID = Academic_Year.Academic_YearID
Where Registration.Student_ID = @vStudent_ID
ORDER BY regdate
  • 1 Contributor
  • 0 Replies
  • 137 Views

Dani AI

Generated

— The behavior you describe (unchanged columns ending up NULL after an update) means the SqlDataSource is not receiving values for those update parameters. That usually happens when the GridView does not render editable inputs for the fields the UpdateCommand expects (for example because those columns were hidden or because the SELECT returns formatted/aliased values instead of the raw columns). The fix is to make sure every value the UpdateCommand needs is actually supplied on postback — either as an editable control, a bound hidden control, or via DataKeys.

Practical fixes to try (in this order):

  • Keep the raw database columns in the SELECT results (don’t only return a formatted string for a date). Use DataFormatString or a TemplateField for display while preserving the raw value for binding.
  • If a column must be hidden but still required for updates (e.g., Branch_ID, AcademicYear_ID), include it in GridView.DataKeyNames or add a HiddenField bound with Bind("FieldName") in an EditItemTemplate. BoundField with Visible="false" will not emit an input and won’t post a value.
  • For TemplateFields, use Bind(...) (two-way) instead of Eval(...) (one-way) so the new value is posted back.
  • Add a small diagnostic in RowUpdating or the SqlDataSource. Inspect e.NewValues / e.OldValues (GridView) or e.Command.Parameters (SqlDataSource) to see which parameters are null and copy values from DataKeys if needed.

Example: copy missing values from DataKeys during RowUpdating:

protected void GridView2_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    var keys = GridView2.DataKeys[e.RowIndex].Values;
    if (e.NewValues["Branch_ID"] == null)
        e.NewValues["Branch_ID"] = keys["Branch_ID"];
    if (e.NewValues["AcademicYear_ID"] == null)
        e.NewValues["AcademicYear_ID"] = keys["AcademicYear_ID"];
}

Quick checklist: add needed fields to DataKeyNames, preserve raw date values (not only formatted text), use Bind for edit templates, and inspect NewValues at runtime to confirm what is being sent to the UpdateCommand.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.