using gridview to update/delete .

anisha bathija

hey ,
How do I update or delete entries into a database by using datagrid view wizard?
I have already established a database connection using the wizard and set the autogeneratedeletebutton and autogenerateedit button properties to true.
How do you please assist me on what exactly the code must be inside the row update event ?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak14y ago
Accepted answer
Hi Nisha,

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

    Untitled Page





            DataKeyNames="VendorId"
        AllowPaging="True"
        CellPadding="3"
        OnPageIndexChanging="ResultGridView_PageIndexChanging"
        OnRowDeleting="ResultGridView_RowDeleting"
        OnRowEditing="ResultGridView_RowEditing"
            OnRowUpdating="ResultGridView_RowUpdating"
            OnRowCancelingEdit="ResultGridView_RowCancelingEdit" PageSize="5"
            BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
            CellSpacing="2" OnRowCommand="ResultGridView_RowCommand" AllowSorting="true"
            onsorting="ResultGridView_Sorting">

                                    ReadOnly="True" SortExpression="VendorId" />

































































































            onclick="btn_search_Click" />







using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    //SqlConnection conn = new SqlConnection(connStr);
    SqlDataAdapter ad = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand();
    DataTable dataTable;
    SqlDataAdapter sqlda;
    DataSet ds;
    string str;
    protected void Page_Load(object sender, EventArgs e)
    {
        Session["sortBy"] = null;
        if (!IsPostBack)
        {
            FillVendorGrid();
        }
    }
    private void FillVendorGrid()
    {
        SqlConnection conn = new SqlConnection(connStr);
        dataTable = new DataTable();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM Vendor";
        ad = new SqlDataAdapter(cmd);
        ad.Fill(dataTable);
        ResultGridView.DataSource = dataTable;
        ResultGridView.DataBind();

    }

    protected void ResultGridView_RowEditing(object sender, GridViewEditEventArgs e)
    {
        ResultGridView.EditIndex = e.NewEditIndex;
        FillVendorGrid();
    }

    protected void ResultGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        ResultGridView.PageIndex = e.NewPageIndex;
        FillVendorGrid();
    }

    protected void ResultGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        SqlConnection conn = new SqlConnection(connStr);
        cmd.Connection = conn;
        cmd.CommandText = "DELETE FROM Vendor WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        FillVendorGrid();

    }

    protected void ResultGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        TextBox txtFName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtFName");
        TextBox txtLName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtLName");
        TextBox txtCity = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCity");
        TextBox txtState = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtState");
        TextBox txtCountry = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCountry");
        TextBox txtDescription = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtDescription");

        SqlConnection conn = new SqlConnection(connStr);
        cmd.Connection = conn;
        cmd.CommandText = "UPDATE Vendor SET VendorFName ='" + txtFName.Text + "',VendorLName ='" + txtLName.Text + "',VendorCity ='" + txtCity.Text + "',VendorState ='" + txtState.Text + "',VendorCountry ='" + txtCountry.Text + "',VendorDescription ='" + txtDescription.Text + "'   WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
        conn.Open();
        cmd.ExecuteNonQuery();
        ResultGridView.EditIndex = -1;
        FillVendorGrid();
        conn.Close();

    }

    protected void ResultGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        ResultGridView.EditIndex = -1;
        FillVendorGrid();

    }

    protected void ResultGridView_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {

            TextBox txtFName = (TextBox)ResultGridView.FooterRow.FindControl("txtFName1");
            TextBox txtLName = (TextBox)ResultGridView.FooterRow.FindControl("txtLName1");
            TextBox txtCity = (TextBox)ResultGridView.FooterRow.FindControl("txtCity1");
            TextBox txtState = (TextBox)ResultGridView.FooterRow.FindControl("txtState1");
            TextBox txtCountry = (TextBox)ResultGridView.FooterRow.FindControl("txtCountry1");
            TextBox txtDescription = (TextBox)ResultGridView.FooterRow.FindControl("txtDescription1");
            SqlConnection conn = new SqlConnection(connStr);
            cmd.Connection = conn;
            cmd.CommandText = "INSERT INTO Vendor(VendorFName, VendorLName,VendorCity,VendorState,VendorCountry,VendorDescription) Values('" + txtFName.Text + "', '" + txtLName.Text + "', '" + txtCity.Text + "', '" + txtState.Text + "', '" + txtCountry.Text + "' , '" + txtDescription.Text + "')";
            conn.Open();
            cmd.ExecuteNonQuery();
            FillVendorGrid();
            conn.Close();
        }
    }
    protected void btn_search_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();
        str = "select * from Vendor where VendorFName like '" + TextBox1.Text + "%'";
        cmd = new SqlCommand(str, conn);
        sqlda = new SqlDataAdapter(cmd);
        ds = new DataSet();
        sqlda.Fill(ds, "Vendor");
        conn.Close();

        ResultGridView.DataSource = ds;
        ResultGridView.DataMember = "Vendor";
        ResultGridView.DataBind();

    }
    protected void ResultGridView_Sorting(object sender, GridViewSortEventArgs e)
    {
        Session["sortBy"] = e.SortExpression;
        FillVendorGrid();
    }
}

Thanks
If this post helps you mark it as answer
Dorababu Meka14y ago
Try this article as per your requirement

http://www.aspdotnet-suresh.com/2011/02/normal-0-false-false-false-en-us-x-none.html
anisha bathija14y ago
hey thank you so much for the constant help and patience , but I think there is something that I am missing out on.
I tried to run the new code with my database and i got the following error.
I didnot try the row update method yet as I thought I must check delete first.
anisha bathija14y ago
hey thank you so much for the constant help and patience , but I think there is something that I am missing out on.
I tried to run the new code with my database and i got the following error.
I didnot try the row update method yet as I thought I must check delete first.
Dorababu Meka14y ago
Can you post your design of gridview
anisha bathija14y ago
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    { string sqlcon =    ConfigurationManager.ConnectionStrings["BPSTConnectionString"].ToString();
        SqlConnection con = new SqlConnection(sqlcon);
        con.Open();
        string UserID = GridView1.Rows[e.RowIndex].Cells[0].Text; //ID
        string Username = ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text;
        string password = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
        string Firstname = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text;
        string Lastname = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text;

        SqlDataAdapter da = new SqlDataAdapter("update User_Table set User_Name='" + Username + "' , Password= '" + password + "', First_Name = '" + Firstname + "', Last_Name = '" + Lastname + "'where User_ID='" + UserID + "'", con);
        //cmd.ExecuteNonQuery();
        GridView1.EditIndex = -1;
        BindData();

        DataSet ds = new DataSet();
        da.Fill(ds);
        //return (ds);
        con.Close();


    }

I am getting the following error:
Dorababu Meka14y ago
Try this

sample gridview

OnRowUpdating="grd_RowUpdating">

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
}
}

public void bind()
{
string strQuey = "select Id,Name from tbldemo"; // here your table name
MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString); // your connection stirng
MySqlCommand cmd = new MySqlCommand(strQuey, con);
con.Open();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
grd.DataSource = ds;
grd.DataBind();
}

Edit and Update

protected void grd_RowEditing(object sender, GridViewEditEventArgs e)
{
grd.EditIndex = e.NewEditIndex; // turn to edit mode
bind();
}
protected void grd_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string id = grd.Rows[e.RowIndex].Cells[1].Text; //ID
string company = ((TextBox)grd.Rows[e.RowIndex].Cells[2].Controls[0]).Text; //Company
MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString);
MySqlCommand cmd = new MySqlCommand("update tbldemo set Name='" + company + "' where Id='" + id + "'", con);
con.Open();
cmd.ExecuteNonQuery();
grd.EditIndex = -1;
bind();
}

protected void grd_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grd.EditIndex = -1;
bind();
}

Similarly for delete