Unable to Delete from a GridView [Answered]RSS

2 replies

Last post Dec 04, 2019 09:59 AM by yij sun

‹ Previous Thread|Next Thread ›

Shortcuts

Related Links

Vishal Rana

Member

2 Points

1 Post

Unable to Delete from a GridView

Dec 02, 2019 10:32 AM|Vishal Rana|LINK

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.

yij sun

Member

310 Points

120 Posts

Re: Unable to Delete from a GridView

Dec 03, 2019 10:05 AM|yij sun|LINK

Hi Vishal Rana,

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

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

yij sun

Member

310 Points

120 Posts

Re: Unable to Delete from a GridView

Dec 04, 2019 09:59 AM|yij sun|LINK

Hi Vishal Rana,

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

‹ Previous Thread|Next Thread ›