Row delete from database or Viewstate/Datatable[Answered] RSS

• • Reply
  akhterr

  Member

  96 Points

  230 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();

• • Reply
  XuDong Peng

  Participant

  1310 Points

  399 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

• • Reply
  akhterr

  Member

  96 Points

  230 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

• • Reply
  XuDong Peng

  Participant

  1310 Points

  399 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