updating data into gridview in asp.net

Question

I am relatively new to asp.net.So please bear with me if my query is childish my grid design:

<asp:GridView runat ="server"  GridLines = "Both" DataKeyNames="book_id"AutoGenerateColumns ="false" CellPadding ="5" CellSpacing ="5" allowpaging="True" allowsorting="True"
 ID="gv_table1" EmptyDataText ="No data exists"       OnRowEditing="gv_RowEditing"
OnRowCancelingEdit="gv_RowCancelingEdit" OnRowUpdating="gv_RowUpdating" OnRowDeleting="gv_RowDeleting">
    <Columns>

<asp:BoundField HeaderText="Book ID"  DataField="book_id">

</asp:BoundField>



<asp:BoundField DataField="book_name"   HeaderText="Book Name">

</asp:BoundField>
<asp:BoundField DataField="author_name" HeaderText="Author Name">

</asp:BoundField>
<asp:BoundField DataField="publisher" HeaderText="Publisher">

</asp:BoundField>
<asp:BoundField DataField="year_edition" HeaderText="Year/Edition">

</asp:BoundField>
<asp:BoundField DataField="total_no" HeaderText="Total No">

</asp:BoundField>
<asp:BoundField DataField="available" HeaderText="Available">

</asp:BoundField>
<asp:BoundField DataField="tags" HeaderText="Tags">

</asp:BoundField>
<asp:BoundField DataField="fare" HeaderText="Fare">

</asp:BoundField>
<asp:BoundField DataField="state" HeaderText="State">

</asp:BoundField>
 <asp:templatefield HeaderText ="Options">
                                <itemtemplate >

                                        <asp:linkbutton id="btnEdit" runat="server" commandname="Edit" text="Edit" />

                                        <asp:linkbutton id="btnDelete" runat="server" commandname="Delete" text="Delete" />
                                </itemtemplate>
                                <edititemtemplate>
                                        <asp:linkbutton id="btnUpdate" runat="server" commandname="Update" text="Update" />
                                        <asp:linkbutton id="btnCancel" runat="server" commandname="Cancel" text="Cancel" />
                                </edititemtemplate>
                        </asp:templatefield>





</Columns>
</asp:GridView>

my code behind:

public void gv_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

        int bookid = Convert.ToInt32(gv_table1.DataKeys[e.RowIndex].Values["book_id"].ToString());
        string bookname =(gv_table1.Rows[e.NewValues].Cells[1].Controls[0] as TextBox).Text;
        string bookname = (gv_table1.Rows[e.RowIndex].FindControl("author_name") as TextBox).Text;

        string book = gv_table1.Rows[e.RowIndex].Cells[1].Text ;
}

I am able to delete but not edit data.I am not able to get the changed values that i enter in the grid view .My best effort got me the value that was in gridview before editing or changing. Thanks in advance friends.

Answer 1

Instead of boundfiled, I prefer to use TemplateField coz of its simplicity.

Tested Sample code : Add new record from footer and Update the selected row

Default.aspx:

    <asp:GridView ID="gvstatus" runat="server" AutoGenerateColumns="False" 
             CellPadding="4" ForeColor="#333333" GridLines="None" 
            onrowcancelingedit="gvstatus_RowCancelingEdit" 
            onrowediting="gvstatus_RowEditing" onrowupdating="gvstatus_RowUpdating" 
            onselectedindexchanged="gvstatus_SelectedIndexChanged" ShowFooter="True" 
            onrowcommand="gvstatus_RowCommand" Width="600px" AllowPaging="True" 
            onpageindexchanging="gvstatus_PageIndexChanging">
            <Columns>

 <asp:TemplateField HeaderText="SrNo " HeaderStyle-HorizontalAlign="Left">
            <ItemTemplate>
                    <%# Container.DataItemIndex + 1 %>
                </ItemTemplate>
 </asp:TemplateField>

 <asp:TemplateField HeaderText="ID" Visible="false">
      <ItemTemplate>
      <asp:Label ID="lblid" runat="server" Text='<%# Bind("columnname_id") %>'> </asp:Label>
     </ItemTemplate>
</asp:TemplateField>


<asp:TemplateField HeaderText="EmpName">
      <ItemTemplate>
        <asp:Label ID="lblEmpName" runat="server" Text='<%# Bind("columnname_EmpName") %>'></asp:Label>
       </ItemTemplate>
       <EditItemTemplate>
           <asp:TextBox ID="txtEmpName" runat="server"  Text='<%# Bind("columnname_EmpName") %>'></asp:TextBox>
        </EditItemTemplate>
        <FooterTemplate>
              <asp:TextBox ID="txtfEmpName"  runat="server"></asp:TextBox>
        </FooterTemplate>
</asp:TemplateField>

 <asp:TemplateField HeaderText="empSalary" >
        <ItemTemplate>
           <asp:Label ID="lblempSalary" runat="server" Text='<%# Bind("columnname_EmpSalary") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
             <asp:TextBox ID="txtempSalary" runat="server"  Text='<%# Bind("columnname_EmpSalary") %>'></asp:TextBox>
         </EditItemTemplate>
         <FooterTemplate>
            <asp:TextBox ID="txtfempSalary" runat="server" ></asp:TextBox>
         </FooterTemplate>
</asp:TemplateField>

 <asp:TemplateField ShowHeader="False" ItemStyle-Width="190px">
         <ItemTemplate>
             <asp:Button ID="btnedit" runat="server" CausesValidation="False" 
                            CommandName="Edit" Text="Edit"></asp:Button>
          </ItemTemplate>
          <EditItemTemplate>
               <asp:Button ID="btnupdate" runat="server" CausesValidation="True" 
                            CommandName="Update" Text="Update"></asp:Button>
                        &nbsp;<asp:Button ID="btncancel" runat="server" CausesValidation="False" 
                            CommandName="Cancel" Text="Cancel"></asp:Button>
         </EditItemTemplate>
        <FooterTemplate>
            <asp:Button ID="btnadd" runat="server" Text="Add" CommandName="Add" />
        </FooterTemplate>
 </asp:TemplateField>
            </Columns>
            <PagerStyle BackColor="#A86E07" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
          <HeaderStyle BackColor="#A86E07" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#d9d9d9" />
    <AlternatingRowStyle BackColor="White" ForeColor="#A86E07" />
 </asp:GridView>

CodeBehind:

 protected void Page_Load(object sender, EventArgs e)
    {
       if (!Page.IsPostBack)
       {
         gvBind(); //Bind gridview 
       }
     }

public void gvBind()
{
    SqlDataAdapter dap = new SqlDataAdapter("select id, empName,empSalary from myTable", conn);
     DataSet ds = new DataSet();
     dap.Fill(ds);
     gvstatus.DataSource = ds.Tables[0];
     gvstatus.DataBind();
}

---

protected void gvstatus_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        //Update the select row from girdview
        lblmsg.Text = "";
        try
        {
            GridViewRow row = (GridViewRow)gvstatus.Rows[e.RowIndex];
            Label lblid = (Label)gvstatus.Rows[e.RowIndex].FindControl("lblid");
            TextBox txtname = (TextBox)gvstatus.Rows[e.RowIndex].FindControl("txtEmpName");
            TextBox txtSalary = (TextBox)gvstatus.Rows[e.RowIndex].FindControl("txtempSalary");
            string empName = txtname.Text;
            string empSalary = txtSalary.Text;
            string lblID=lblid.Text;
            int result = UpdateQuery(empName, empSalary,lblID);
            if (result > 0)
            {
                lblmsg.Text = "Record is updated successfully.";
            }
            gvstatus.EditIndex = -1;
            gvBind();
        }
        catch (Exception ae)
        {
            Response.Write(ae.Message);
        }

    }

---

protected void gvstatus_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvstatus.EditIndex = -1;
        gvBind();
    }
 protected void gvstatus_RowEditing(object sender, GridViewEditEventArgs e)
    {
        lblmsg.Text = "";
        gvstatus.EditIndex = e.NewEditIndex;
        gvBind();
    }

  protected void gvstatus_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        //Add new record to database form girdview footer
        if (e.CommandName == "Add")
        {
            string empName = ((TextBox)gvstatus.FooterRow.FindControl("txtfempName")).Text;
            string empSalry = ((TextBox)gvstatus.FooterRow.FindControl("txtfempSalary")).Text;
            int result = InsertNewRecord(empName, empSalry);
            if (result > 0)
            {
                lblmsg.Text = "Record is added successfully.";
            }
            gvstatus.EditIndex = -1;
            gvBind();

        }
    }

---

    public void UpdateQuery(string empName, string empSalary, string lblID)
    {
        SqlCommand cmd = new SqlCommand("update myTable set empName='" + empName + "',empSalary='" + empSalary + "' where  id='" + lblID + "'", conn);
        conn.Open();
        int temp = cmd.ExecuteNonQuery();
        conn.Close();
        return temp;
    }


public void InsertNewRecord(string empName, string empSalary)
    {
        SqlCommand cmd = new SqlCommand("your insert query ", conn);
        conn.Open();
        int temp = cmd.ExecuteNonQuery();
        conn.Close();
        return temp;
    }

http://satindersinght.blogspot.in/2012/08/how-to-addupdate-record-using-gridview.html

Mark answer if you find it helpfull :)

Answer 2

I wrote out all the code for you even though you probably dont need some of it. Hopefully you can learn from it and apply to yours. But this code does work. But first, if I were you, I would create an Item Template for book id and make it a label. Then that will be your control. You also need to use parameters to prevent sql injection. There are so many ways to do this, but this is just how I do it. Hopefully it will help.

 protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    //Identify your control i.e. the primary key (lblbookid is the name of the item template)
    GridViewRow row = GridView1.Rows[e.RowIndex];
    Label bookidLabel = (Label)row.FindControl("lblbookid");

    //connect to db which you probably already have
    string strSQLConnection = ("server=blah;database=blah;uid=blah;pwd=blah");
    SqlConnection sqlConnection = new SqlConnection(strSQLConnection);

    SqlCommand cmd = new SqlCommand();

    cmd.CommandText = "UPDATE yourtable SET book_name = @book_name WHERE book_id = @book_id";

    //parameters
    cmd.Parameters.Add("@bookid", SqlDbType.Char).Value = bookidLabel.Text;
    cmd.Parameters.Add("@book_name", SqlDbType.Char).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
    cmd.Parameters.Add("@book_author", SqlDbType.Char).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text;

    cmd.Connection = sqlConnection;
    sqlConnection.Open();
    cmd.ExecuteNonQuery();



    sqlConnection.Close();

    GridView1.EditIndex = -1;
    BindData();
}

Answer 3

I don't think we can access bound field using Find control method. author_name is the name of datafield inside the bound feild you can't access it using FindControl, it is not a control.

Use the e.RowIndex to get the updated values.

public void gv_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    string bookname = ((TextBox)(gv_table1.Rows[e.RowIndex].Cells[1].Controls[0])).Text;

                         // OR
    string bookname =(gv_table1.Rows[e.RowIndex].Cells[1].Controls[0] as TextBox).Text;
    gv_table1.EditIndex = -1;  // reset the edit index
    // Again Bind the gridview to show updated data
}

Updated Answer:

Problem: The problem is that you are binding your gridview on each post back

Solution:

To test it,I create a gridview add two column, and I got old values if I bind gridview on each postback. To avoid from it just add the Page.IsPostBack check in your before binding the grid.

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack) // If you not place this check then you will get the old values because GridView in Bind on every postback
    {
        BindGrid(); // Bind you grid here
    }
}

My Complete code:

// Aspx Code
<asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server" 
    AllowPaging="True" 
     onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating" onrowcancelingedit="GridView1_RowCancelingEdit" 
    >        
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="City" HeaderText="Name" />
        <asp:CommandField ShowEditButton ="true" ShowCancelButton="true"  ShowDeleteButton="true" />
    </Columns>
</asp:GridView>

// Aspx Code Behind
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack) // If you not place this check then you will get the old values because GridView in Bind on every postback
    {
        BindGrid();
    }
}
private void BindGrid() // function for binding gridview
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Name");
    dt.Columns.Add("City");

    DataRow r = dt.NewRow();
    r[0] = "Name 1";
    r[1] = "City 1";

    DataRow r1 = dt.NewRow();
    r1[0] = "Name 2";
    r1[1] = "City 2";

    dt.Rows.Add(r);
    dt.Rows.Add(r1);

    GridView1.DataSource = dt;
    GridView1.DataBind();
}


protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
    GridView1.EditIndex = e.NewEditIndex; // setting new index
    BindGrid();
}


protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow row = GridView1.Rows[e.RowIndex];
    string newvalue = ((TextBox)row.Cells[0].Controls[0]).Text;
     GridView1.EditIndex = -1; // Again reset
}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    GridView1.EditIndex = -1; // reseting grid view
    BindGrid();
}