Remove From My Forums

Unable to Delete from a GridView

Question

User-1013333689 posted

I wanted to know how the parameters values are passed to SqlDataSource object through the Data web server control GridView. From the Microsoft website I got to know that the values are passed using DataKeyNames therefore the parameters name must match with that of DataKeyNames. But when I tried to print their value while updating data through the GridView control (the values was printed from the updating function method of SqlDataSource's Object). But It prints nothing for p.DefaultValue or p.ToString(); I am just getting blank in front of the parameter on the output screen.

How to retrieve those values? Or may be the values doesn't get actually passed. Please help me where I am missing.

How can I check those values just before updating the data in database?

Here is the Code.

 protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
        {
            Status.Text = "";
            ParameterCollection coll = SqlDataSource1.UpdateParameters;
            foreach (Parameter p in coll)
            {
                Status.Text += p.Name + ": " + p.DefaultValue + "<br/>";
            }
        }

Second Question I am unable to delete from DataGridView Control. Everytime I click on delete button a postback occurs but nothing happens.

Here is the code.

<body>
    <form id="form1" runat="server">
        <div>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" 
                ConnectionString="<%$ ConnectionStrings:DCSADatabaseConnectionString %>" 
               
                
                OldValuesParameterFormatString="original_{0}" 
                SelectCommand="SELECT * FROM [Emp]" 
                UpdateCommand="UPDATE [Emp] SET [ename] = @ename, [job] = @job, [mgr] = @mgr, [hiredate] = @hiredate, [sal] = @sal, [comm] = @comm, [deptno] = @deptno WHERE [empno] = @original_empno AND [ename] = @original_ename AND [job] = @original_job AND (([mgr] = @original_mgr) OR ([mgr] IS NULL AND @original_mgr IS NULL)) AND (([hiredate] = @original_hiredate) OR ([hiredate] IS NULL AND @original_hiredate IS NULL)) AND (([sal] = @original_sal) OR ([sal] IS NULL AND @original_sal IS NULL)) AND (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))" 
                DeleteCommand="DELETE FROM Emp WHERE (empno = @empno)"  
                OnUpdated="SqlDataSource1_Updated" 
                OnUpdating="SqlDataSource1_Updating">
              
                <DeleteParameters>
                    <asp:Parameter Name="empno" Type="Int32"/>
                </DeleteParameters>
              
                
                <UpdateParameters>
                    <asp:Parameter Name="ename" Type="String" />
                    <asp:Parameter Name="job" Type="String" />
                    <asp:Parameter Name="mgr" Type="Int32" />
                    <asp:Parameter DbType="Date" Name="hiredate" />
                    <asp:Parameter Name="sal" Type="Int32" />
                    <asp:Parameter Name="comm" Type="Int32" />
                    <asp:Parameter Name="deptno" Type="Int32" />
                    <asp:Parameter Name="original_empno" Type="Int32" />
                    <asp:Parameter Name="original_ename" Type="String" />
                    <asp:Parameter Name="original_job" Type="String" />
                    <asp:Parameter Name="original_mgr" Type="Int32" />
                    <asp:Parameter DbType="Date" Name="original_hiredate" />
                    <asp:Parameter Name="original_sal" Type="Int32" />
                    <asp:Parameter Name="original_comm" Type="Int32" />
                    <asp:Parameter Name="original_deptno" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
            <asp:Label ID="Status" runat="server" Text="Status"></asp:Label>
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                        DataKeyNames="empno" DataSourceID="SqlDataSource1" 
                     OnPreRender="GridView1_PreRender" OnRowDataBound="GridView1_RowDataBound" 
OnSelectedIndexChanged="GridView1_SelectedIndexChanged" 
OnRowDeleting="GridView1_RowDeleting">
                <Columns>
                    <asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" />
                    <asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" />
                    <asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" />
                    <asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" />
                    <asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" />
                    <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" />
                </Columns>
            </asp:GridView>

        </div>
    </form>
</body>

Also while updating nothing gets changed.

Please help I will be very much thankful to you. I have wasted lot of time understanding how the data flows from gridview control to sqldatasource's object for updating deleting and inserting but I am getting lot of confusion.

Monday, December 2, 2019 10:32 AM

Answers

User1535942433 posted

Hi Vishal Rana,

Update and Delete Gridview

As far as I know,OldValuesParameterFormatString applied to the names of any oldValues parameters passed to the Delete() or Update() methods.So, int the DeleteCommand,you must set empno is equal to original_empno.

At the same time,when you update the gridview, if you couldn't bind all columns to gridview,these columns will be NULL.Then it will not update successfully.

Besides,I think you could simplify your code.In UpdateParameters,you may couldn't write original_. The format string is applied to each parameter name in the oldValues collection.

More details ,you could refer to below code:

<div>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
                ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542ConnectionString %>"
                OldValuesParameterFormatString="original_{0}"
                SelectCommand="SELECT * FROM [Emp]"
                UpdateCommand="UPDATE [Emp]
                SET [ename] = @ename,
                [job] = @job, [mgr] = @mgr,
                [hiredate] = @hiredate, [sal] = @sal,
                [comm] = @comm, [deptno] = @deptno
                WHERE
                [empno] = @original_empno AND [ename] = @original_ename
                AND [job] = @original_job AND (([mgr] = @original_mgr)
                OR ([mgr] IS NULL AND @original_mgr IS NULL))
                AND (([hiredate] = @original_hiredate)
                OR ([hiredate] IS NULL AND @original_hiredate IS NULL))
                AND (([sal] = @original_sal) OR
                ([sal] IS NULL AND @original_sal IS NULL)) AND
                (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))"
                DeleteCommand="DELETE FROM [Emp] WHERE empno = @original_empno">
                <DeleteParameters>
                    <asp:Parameter Name="empno" Type="Int32" />
                </DeleteParameters>
                <UpdateParameters>
                    <asp:Parameter Name="empno" Type="Int32" />
                    <asp:Parameter Name="ename" Type="String" />
                    <asp:Parameter Name="job" Type="String" />
                    <asp:Parameter Name="mgr" Type="Int32" />
                    <asp:Parameter DbType="Date" Name="hiredate" />
                    <asp:Parameter Name="sal" Type="Int32" />
                    <asp:Parameter Name="comm" Type="Int32" />
                    <asp:Parameter Name="deptno" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" />
                    <asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" />
                    <asp:BoundField DataField="job" HeaderText="job" SortExpression="job" Visible="false" />
                    <asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" />
                    <asp:BoundField DataField="hiredate" HeaderText="hiredate" SortExpression="hiredate" InsertVisible="False" />
                    <asp:BoundField DataField="sal" HeaderText="sal" SortExpression="sal" />
                    <asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" />
                    <asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" />
                    <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Link" />
                </Columns>
            </asp:GridView>
        </div>

Result:

Best regards,

Yijing Sun

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Tuesday, December 3, 2019 10:05 AM

User1535942433 posted

Hi Vishal Rana,

How can I check those values just before updating the data in database?

I suggest you could use e.Command.Parameters to get the updated value in the updating method.

More details ,you could refer to below code:

ASPX:

<div>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"

                ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542ConnectionString %>"

                OldValuesParameterFormatString="original_{0}"

                SelectCommand="SELECT * FROM [Emp]"

                UpdateCommand="UPDATE [Emp]

                SET [ename] = @ename,

                [job] = @job, [mgr] = @mgr,

                [hiredate] = @hiredate, [sal] = @sal,

                [comm] = @comm, [deptno] = @deptno

                WHERE

                [empno] = @original_empno AND [ename] = @original_ename

                AND [job] = @original_job AND (([mgr] = @original_mgr)

                OR ([mgr] IS NULL AND @original_mgr IS NULL))

                AND (([hiredate] = @original_hiredate)

                OR ([hiredate] IS NULL AND @original_hiredate IS NULL))

                AND (([sal] = @original_sal) OR

                ([sal] IS NULL AND @original_sal IS NULL)) AND

                (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))"

                DeleteCommand="DELETE FROM [Emp] WHERE empno = @original_empno" OnUpdating="SqlDataSource1_Updating">

                <DeleteParameters>

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

                </DeleteParameters>

                <UpdateParameters>

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

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

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

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

                    <asp:Parameter DbType="Date" Name="hiredate" />

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

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

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

                </UpdateParameters>

            </asp:SqlDataSource>

            <asp:Label ID="Status" runat="server" Text="Status"></asp:Label>

            <br />

            <br />

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1" >

                <Columns>

                    <asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" />

                    <asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" />

                    <asp:BoundField DataField="job" HeaderText="job" SortExpression="job" />

                    <asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" />

                    <asp:BoundField DataField="hiredate" HeaderText="hiredate" SortExpression="hiredate" InsertVisible="False" />

                    <asp:BoundField DataField="sal" HeaderText="sal" SortExpression="sal" />

                    <asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" />

                    <asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" />

                    <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Link" />

                </Columns>

            </asp:GridView>

        </div>

Code-Behind:

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)

        {

 

            Status.Text = "";

            DbCommand command = e.Command;

            for (int i = 0; i < SqlDataSource1.UpdateParameters.Count; i++)

            {

                Status.Text += command.Parameters[i].Value+ "<br/>";

            }

        }

Result:

Best regards,

Yijing Sun

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Wednesday, December 4, 2019 9:59 AM

All replies

User1535942433 posted

Hi Vishal Rana,

Update and Delete Gridview

At the same time,when you update the gridview, if you couldn't bind all columns to gridview,these columns will be NULL.Then it will not update successfully.

Besides,I think you could simplify your code.In UpdateParameters,you may couldn't write original_. The format string is applied to each parameter name in the oldValues collection.

More details ,you could refer to below code:

<div>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
                ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542ConnectionString %>"
                OldValuesParameterFormatString="original_{0}"
                SelectCommand="SELECT * FROM [Emp]"
                UpdateCommand="UPDATE [Emp]
                SET [ename] = @ename,
                [job] = @job, [mgr] = @mgr,
                [hiredate] = @hiredate, [sal] = @sal,
                [comm] = @comm, [deptno] = @deptno
                WHERE
                [empno] = @original_empno AND [ename] = @original_ename
                AND [job] = @original_job AND (([mgr] = @original_mgr)
                OR ([mgr] IS NULL AND @original_mgr IS NULL))
                AND (([hiredate] = @original_hiredate)
                OR ([hiredate] IS NULL AND @original_hiredate IS NULL))
                AND (([sal] = @original_sal) OR
                ([sal] IS NULL AND @original_sal IS NULL)) AND
                (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))"
                DeleteCommand="DELETE FROM [Emp] WHERE empno = @original_empno">
                <DeleteParameters>
                    <asp:Parameter Name="empno" Type="Int32" />
                </DeleteParameters>
                <UpdateParameters>
                    <asp:Parameter Name="empno" Type="Int32" />
                    <asp:Parameter Name="ename" Type="String" />
                    <asp:Parameter Name="job" Type="String" />
                    <asp:Parameter Name="mgr" Type="Int32" />
                    <asp:Parameter DbType="Date" Name="hiredate" />
                    <asp:Parameter Name="sal" Type="Int32" />
                    <asp:Parameter Name="comm" Type="Int32" />
                    <asp:Parameter Name="deptno" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" />
                    <asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" />
                    <asp:BoundField DataField="job" HeaderText="job" SortExpression="job" Visible="false" />
                    <asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" />
                    <asp:BoundField DataField="hiredate" HeaderText="hiredate" SortExpression="hiredate" InsertVisible="False" />
                    <asp:BoundField DataField="sal" HeaderText="sal" SortExpression="sal" />
                    <asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" />
                    <asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" />
                    <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Link" />
                </Columns>
            </asp:GridView>
        </div>

Result:

Best regards,

Yijing Sun

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Tuesday, December 3, 2019 10:05 AM

User1535942433 posted

Hi Vishal Rana,

How can I check those values just before updating the data in database?

I suggest you could use e.Command.Parameters to get the updated value in the updating method.

More details ,you could refer to below code:

ASPX:

<div>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"

                ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542ConnectionString %>"

                OldValuesParameterFormatString="original_{0}"

                SelectCommand="SELECT * FROM [Emp]"

                UpdateCommand="UPDATE [Emp]

                SET [ename] = @ename,

                [job] = @job, [mgr] = @mgr,

                [hiredate] = @hiredate, [sal] = @sal,

                [comm] = @comm, [deptno] = @deptno

                WHERE

                [empno] = @original_empno AND [ename] = @original_ename

                AND [job] = @original_job AND (([mgr] = @original_mgr)

                OR ([mgr] IS NULL AND @original_mgr IS NULL))

                AND (([hiredate] = @original_hiredate)

                OR ([hiredate] IS NULL AND @original_hiredate IS NULL))

                AND (([sal] = @original_sal) OR

                ([sal] IS NULL AND @original_sal IS NULL)) AND

                (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))"

                DeleteCommand="DELETE FROM [Emp] WHERE empno = @original_empno" OnUpdating="SqlDataSource1_Updating">

                <DeleteParameters>

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

                </DeleteParameters>

                <UpdateParameters>

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

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

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

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

                    <asp:Parameter DbType="Date" Name="hiredate" />

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

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

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

                </UpdateParameters>

            </asp:SqlDataSource>

            <asp:Label ID="Status" runat="server" Text="Status"></asp:Label>

            <br />

            <br />

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1" >

                <Columns>

                    <asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" />

                    <asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" />

                    <asp:BoundField DataField="job" HeaderText="job" SortExpression="job" />

                    <asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" />

                    <asp:BoundField DataField="hiredate" HeaderText="hiredate" SortExpression="hiredate" InsertVisible="False" />

                    <asp:BoundField DataField="sal" HeaderText="sal" SortExpression="sal" />

                    <asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" />

                    <asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" />

                    <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Link" />

                </Columns>

            </asp:GridView>

        </div>

Code-Behind:

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)

        {

 

            Status.Text = "";

            DbCommand command = e.Command;

            for (int i = 0; i < SqlDataSource1.UpdateParameters.Count; i++)

            {

                Status.Text += command.Parameters[i].Value+ "<br/>";

            }

        }

Result:

Best regards,

Yijing Sun

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Wednesday, December 4, 2019 9:59 AM

Answered by:

Unable to Delete from a GridView

Question

Answers

All replies