how to use delete command in grid view for updation

Question

i have 3 web pages. admin,student and teacher. i have used grid view on each of this page.my table is having a column as 'status'.which is having default value 0. I want to update this value to 1 when i click on delete in gridview. I have written following code. But it is not working.pls help.

this is .aspx file code-

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TEMPRUJUConnectionString %>" 
 SelectCommand="SELECT * FROM [login] WHERE ([username] = @username)" 
 DeleteCommand="UPDATE [login] SET [status]= 1 WHERE [id]=@id" 
 InsertCommand="INSERT INTO [login] ([name], [midname], [surname], [username], [password], [contact], [dob], [email], [address], [occupation], [ltype]) VALUES (@name, @midname, @surname, @username, @password, @contact, @dob, @email, @address, @occupation, @ltype)" 
 UpdateCommand="UPDATE [login] SET [name] = @name, [midname] = @midname, [surname] = @surname, [username] = @username, [password] = @password, [contact] = @contact, [dob] = @dob, [email] = @email, [address] = @address, [occupation] = @occupation, [ltype] = @ltype WHERE [Id] = @Id">
        <DeleteParameters>
            <asp:Parameter Name="Id" Type="Int32" />
        </DeleteParameters>  

and this is my code behind-

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    SqlConnection con = new SqlConnection("Data Source=SNEHAL-PC\\SNEHAL1;Initial Catalog=TEMPRUJU;Integrated Security=True");
    SqlCommand cmd;
    SqlDataReader dr;
    con.Open();
    GridViewRow row = GridView1.Rows[e.RowIndex];
    cmd=new SqlCommand("Update login set status=1 where id='" + GridView1.DataKeys[e.RowIndex].Value.ToString() + "'",con);
    dr = cmd.ExecuteReader();
}

Answer 1

when you have SqlDataSource you have no need to implement GridView1_RowDeleting,

first you must tell to SqlDataSource that where it can find the value of delete command parametr(s), in your case @id... for this, you need to set DataKeyNames property on your GridView by database unique key field name, in your case id...

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

then, to set delete command parameter on SqlDataSource you must tell that, find value of @id from SelectedDataKey property, from GridView like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TEMPRUJUConnectionString %>"
        DeleteCommand="UPDATE [login] SET [status]= 1 WHERE [id]=@id">
        <DeleteParameters>
            <asp:ControlParameter ControlID="GridView1" Name="id" PropertyName="SelectedDataKey" />
        </DeleteParameters>
    </asp:SqlDataSource>

and finaly, you need to, send a Delete command with your gridView, for this, you can enable deleteing on gridview control to add this <asp:CommandField ShowDeleteButton="True" /> into your grid view markup, or you can convert this field into a TemplateFiled and create your own template with any control _which have_ CommandName property, and set that property (CommandName) to keyword Delete like this :

<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Delete" Text="Delete me"></asp:LinkButton>

this is a sample with Template Field:

<asp:GridView ID="GridView2" runat="server" DataKeyNames="ID" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:TemplateField ShowHeader="False">
                <ItemTemplate>
                    <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"></asp:LinkButton>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="UserID" SortExpression="UserID">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("UserID") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("UserID") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Age" SortExpression="Age">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Age") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label3" runat="server" Text='<%# Bind("Age") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 
        DeleteCommand="UPDATE Profiles SET Age = Age+1 where id = @id" SelectCommand="SELECT * FROM Profiles">
        <DeleteParameters>
            <asp:ControlParameter ControlID="GridView1" Name="id" PropertyName="SelectedDataKey" />
        </DeleteParameters>
    </asp:SqlDataSource>

and this is a sample with just enable deleteing :

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:CommandField ShowDeleteButton="True" />
            <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
            <asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />
            <asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 
        DeleteCommand="UPDATE Profiles SET Age = Age+1 where id = @id" SelectCommand="SELECT * FROM Profiles">
        <DeleteParameters>
            <asp:ControlParameter ControlID="GridView1" Name="id" PropertyName="SelectedDataKey" />
        </DeleteParameters>
    </asp:SqlDataSource>

i increment age in my sample by every delete command