We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >
Row delete from database or Viewstate/Datatable[Answered]
3 replies
Last post Jun 09, 2020 05:29 AM by XuDong Peng
Last post Jun 09, 2020 05:29 AM by XuDong Peng
Member
118 Points
284 Posts
Row delete from database or Viewstate/Datatable
Jun 04, 2020 06:56 PM|akhterr|LINK
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();Contributor
2400 Points
759 Posts
Re: Row delete from database or Viewstate/Datatable
Jun 05, 2020 05:21 AM|XuDong Peng|LINK
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
Member
118 Points
284 Posts
Re: Row delete from database or Viewstate/Datatable
Jun 05, 2020 09:22 AM|akhterr|LINK
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
Contributor
2400 Points
759 Posts
Re: Row delete from database or Viewstate/Datatable
Jun 09, 2020 05:29 AM|XuDong Peng|LINK
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