Row delete from database or Viewstate/Datatable

Question

User-367318540 posted

i have gridview,i want condition to delete row from gridview,

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"  HorizontalAlign="Center" AutoGenerateColumns="false" CssClass="mygrdContent" HeaderStyle-BackColor="#66ccff" HeaderStyle-CssClass="header" HeaderStyle-ForeColor="White" OnRowDataBound="GridView1_RowDataBound" OnRowDeleting="GridView1_RowDeleting"   PagerStyle-CssClass="pager" RowStyle-CssClass="rows" Width="452px" ShowFooter="True">
                                                         <Columns>
  
 
                                                                <asp:TemplateField HeaderText="ID">
                                                                 <ItemTemplate>
                                                                     <asp:Label ID="D_CID" runat="server" Text='<%#Bind("D_CID")%>'></asp:Label>
                                                                 </ItemTemplate>
                                                             </asp:TemplateField>
                                                             <asp:TemplateField HeaderText="Container">
                                                                 <ItemTemplate>
                                                                     <asp:Label ID="Contnam" runat="server" Text='<%#Bind("Contnam")%>'></asp:Label>
                                                                 </ItemTemplate>
                                                             </asp:TemplateField>
 
 
                                                             <asp:TemplateField HeaderText="QTY">
                                                                 <ItemTemplate>
                                                                     <asp:Label ID="QTY" runat="server" Text='<%#Bind("QTY")%>'></asp:Label>
                                                                 </ItemTemplate>
                                                                   <FooterTemplate>
                                       <asp:Label ID="lblTotal1" runat="server"></asp:Label>
                                   </FooterTemplate>
                                                             </asp:TemplateField>
                                                             <asp:TemplateField>
                                                                 <ItemTemplate>
              
                                                                 </ItemTemplate>
                                                                 <EditItemTemplate>
   
                                                                 </EditItemTemplate>
                                                             </asp:TemplateField>
                                                             <asp:CommandField ButtonType="Button" ShowDeleteButton="True" />
                                                         </Columns>
                                                         <HeaderStyle BackColor="#66CCFF" ForeColor="White" />
                                                         <SelectedRowStyle BackColor="Yellow" />
                                                     </asp:GridView>

if i have data in gridview in datatable/Viewstate then it delete from viewstate if first column id does not exit ,

if i have ID in first column ,which i retrieved from database then it deleted from database below i tried but it is not working

  protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
            int index = Convert.ToInt32(e.RowIndex);
            DataTable dt = (DataTable)ViewState["dt"];
            // Delete from ViewState.
            dt.Rows[index].Delete();
            ViewState["dt"] = dt;

            string query = "delete from  tbl_Import_Detail  WHERE D_CID = @Id";
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@Id", id);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
            BindGridView();

Answer 1

User-939850651 posted

Hi, akhterr

I create a demo and delete row from database or DataTable. I think this may be what you expect.

More details, please refer below code:

DeleteRow.aspx
<body>
    <form id="form" runat="server">
        <div>
            <asp:GridView runat="server" ID="GridView1" DataKeyNames="id" AutoGenerateColumns="false" OnRowDeleting="GridView1_RowDeleting">
                <Columns>
                    <asp:TemplateField HeaderText="ID">
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%#Eval("id")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="First Column">
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%#Eval("firstCol")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Second Column">
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%#Eval("secondCol")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Third Column">
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%#Eval("thirdCol")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:CommandField ShowDeleteButton="true" ButtonType="Button" />
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>

DeleteRow.aspx.cs
public partial class DeleteRow : System.Web.UI.Page
    {
        String conStr = "data source =.; database=TestDB; integrated security = SSPI";
        //public static DataTable newData;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                bindData();
            }
        }

        private void bindData()
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                String query = "select * from tableTest";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    //newData = dt;
                    GridView1.DataBind();
                    Session["Table"] = dt;
                }
            }
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //delete from database
            //get Column id
            int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                string strsql = "delete from tableTest where id=@id";
                using (SqlCommand cmd = new SqlCommand(strsql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    cmd.ExecuteReader();
                    conn.Close();
                    //Response.Write("<script>alert('delete row success!');</script>");
                    bindData();
                }
            }
            //delete row from Datatable
            //newData.Rows.RemoveAt(e.RowIndex);
            //GridView1.DataSource = newData;
            //GridView1.DataBind();        
      }      
}

If you want to delete from datatable, you could try code in yellow.

Result:

If there is any other questions, please feel free to let me know.

Hope this can help you.

Best regards,

Xudong Peng

Answer 2

User-367318540 posted

Hi XuDong Peng,

i am getting error below

There is no row at position 0.

Line 536:
Line 537:
Line 538:            newData.Rows.RemoveAt(e.RowIndex);
Line 539:            GridView1.DataSource = newData;
Line 540:            GridView1.DataBind();

Record is deleted from table ,because it  has id gridview,

please view image ,i want condition to handle ,if ID exit in gridview then it delete from table ,if ID does not exit then it delete from datatable.

https://ibb.co/DL96drP

Answer 3

User-939850651 posted

Hi, akhterr

Based on your description, I think I may not have explained my solution correctly. And I can't reproduce your issues.

I create a demo and delete row from datatable without id.

More details, please refer below code:

DeleteRow.aspx
<body>
    <form id="form" runat="server">
        <div>
            <asp:GridView runat="server" ID="GridView1" AutoGenerateColumns="false" OnRowDeleting="GridView1_RowDeleting">             <Columns>
                    <asp:TemplateField HeaderText="ID">
                        <ItemTemplate>
                            <%--<asp:Label runat="server" ID="txt_id" Text='<%#Eval("id")%>'></asp:Label>--%>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="First Column">
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%#Eval("firstCol")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Second Column">
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%#Eval("secondCol")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:CommandField ShowDeleteButton="true" ButtonType="Button" />
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>

DeleteRow.aspx.cs
public partial class DeleteRow : System.Web.UI.Page
    {
        public static DataTable newData;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                bindData();
            }
        }

        private void bindData()
        {
            String conStr = "data source =.; database=TestDB; integrated security = SSPI";
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                String query = "select * from tableTest";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    newData = dt;
                    GridView1.DataBind();
                    //store datatable to session, if need you can get it
                    Session["Table"] = dt;
                }
            }
        }

       protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            newData.Rows.RemoveAt(e.RowIndex);
            GridView1.DataSource = newData;
            GridView1.DataBind();
        }
    }

Result:

Best regards,

Xudong Peng