none
How to Delete selected row in data grid view and database by C#

    Question

  • I wish to ask how i can delete the both database and selected row from data grid view by using C# windows form button??

    This was the code i using --

     private void del_list_Click(object sender, EventArgs e)
        {
          System.Collections.ArrayList alv = new System.Collections.ArrayList();
          int i = 0;
    
          OleDbCommand delcmd = new OleDbCommand();
    
          for (i = 0; i < dataGridView1.Rows.Count; i++)
          {
            DataGridViewRow delrow = dataGridView1.Rows[i];
            if (delrow.Selected == true)
            {
              dataGridView1.Rows.RemoveAt(i);
              try
              {
                delcmd.CommandText = "DELETE FROM Receive WHERE CustCode=" + i + "";
                int count = delcmd.ExecuteNonQuery();
              }
              catch (Exception ex)
              {
                MessageBox.Show(ex.ToString());
              }
    
            }
          }
          
        }
    

    and getting some kind of error like this--

    http://www.mypicx.com/02212011/Error_Msg/

     

    Monday, February 21, 2011 3:22 PM

Answers

  • That picture shows that you didnt open the connection before calling ExecureNonQuery() method.  And you need to initialize a new connection too.

    So you do:

     using(OleDdConnection oleConn = new OldDbConnection("connectionString")) //ADDED
     {
      OleDbCommand delcmd = new OleDbCommand();
    
       for (i = 0; i < dataGridView1.Rows.Count; i++)
       {
        DataGridViewRow delrow = dataGridView1.Rows[i];
        if (delrow.Selected == true)
        {
         dataGridView1.Rows.RemoveAt(i);
         try
         {
          delcmd.CommandText = "DELETE FROM Receive WHERE CustCode=" + i + "";
          oleConn.Open(); //ADDED
          int count = delcmd.ExecuteNonQuery();
         }
         catch (Exception ex)
         {
          MessageBox.Show(ex.ToString());
         }
        }
       }
     }
    

     

    And how to delete selected row in dgv:

          foreach (DataGridViewRow row in dataGridView1.Rows)
          {
            if (row.Selected)
            {
              dataGridview1.Rows.RemoveAt[row.Index];
              break; //use break if you only select one row! 
              //if selected more, then remove it!
            }
          }
    

    Hope it helps,

    Mitja

    Monday, February 21, 2011 4:17 PM
  • Few more addition to Mitja Bonca's code

     

                using (OleDbConnection oleConn = new OleDbConnection("connectionString")) //ADDED
                {
                    OleDbCommand delcmd = new OleDbCommand();
                    oleConn.Open(); 
                    for (int i = 0; i < dataGridView1.Rows.Count; i++)
                    {
                        DataGridViewRow delrow = dataGridView1.Rows[i];
                        if (delrow.Selected == true)
                        {
                            dataGridView1.Rows.RemoveAt(i);
                            try
                            {
                                delcmd.CommandText = "DELETE FROM Receive WHERE CustCode=" + i + "";
                                delcmd.Connection = oleConn;
                                int count = delcmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show(ex.ToString());
                            }
                        }
                    }
                    oleConn.Close();
                }

     

    And you can get connection string for your OleDbConnection from following link

    http://connectionstrings.com/


    Gaurav Khanna
    Monday, February 21, 2011 6:48 PM

All replies

  • That picture shows that you didnt open the connection before calling ExecureNonQuery() method.  And you need to initialize a new connection too.

    So you do:

     using(OleDdConnection oleConn = new OldDbConnection("connectionString")) //ADDED
     {
      OleDbCommand delcmd = new OleDbCommand();
    
       for (i = 0; i < dataGridView1.Rows.Count; i++)
       {
        DataGridViewRow delrow = dataGridView1.Rows[i];
        if (delrow.Selected == true)
        {
         dataGridView1.Rows.RemoveAt(i);
         try
         {
          delcmd.CommandText = "DELETE FROM Receive WHERE CustCode=" + i + "";
          oleConn.Open(); //ADDED
          int count = delcmd.ExecuteNonQuery();
         }
         catch (Exception ex)
         {
          MessageBox.Show(ex.ToString());
         }
        }
       }
     }
    

     

    And how to delete selected row in dgv:

          foreach (DataGridViewRow row in dataGridView1.Rows)
          {
            if (row.Selected)
            {
              dataGridview1.Rows.RemoveAt[row.Index];
              break; //use break if you only select one row! 
              //if selected more, then remove it!
            }
          }
    

    Hope it helps,

    Mitja

    Monday, February 21, 2011 4:17 PM
  • Few more addition to Mitja Bonca's code

     

                using (OleDbConnection oleConn = new OleDbConnection("connectionString")) //ADDED
                {
                    OleDbCommand delcmd = new OleDbCommand();
                    oleConn.Open(); 
                    for (int i = 0; i < dataGridView1.Rows.Count; i++)
                    {
                        DataGridViewRow delrow = dataGridView1.Rows[i];
                        if (delrow.Selected == true)
                        {
                            dataGridView1.Rows.RemoveAt(i);
                            try
                            {
                                delcmd.CommandText = "DELETE FROM Receive WHERE CustCode=" + i + "";
                                delcmd.Connection = oleConn;
                                int count = delcmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show(ex.ToString());
                            }
                        }
                    }
                    oleConn.Close();
                }

     

    And you can get connection string for your OleDbConnection from following link

    http://connectionstrings.com/


    Gaurav Khanna
    Monday, February 21, 2011 6:48 PM
  • Or simply do:

    OleDbCommand delcmd = new OleDbCommand();
    oleConn.Connection.Open(); //and no need to Open it again!
    
    

    .. and no need to Close the connection, becuase of using "using" keyword" statement the connection will be disposed outside the scope.

    Am I right Khanna?

    Monday, February 21, 2011 7:13 PM
  • Or simply do:

     

    OleDbCommand delcmd = new
     OleDbCommand();
    oleConn.Connection.Open(); //and no need to Open it again!
    
    
    

     

    .. and no need to Close the connection, becuase of using "using" keyword" statement the connection will be disposed outside the scope.

    Am I right Khanna?


    But I'm already open OleDbConnection on the Window Form load. Does it really need to open again ??
    Tuesday, February 22, 2011 1:11 AM
  • This code only delete data grid view rows, but cannot delete the data in database. If i want both delete, How should i using the query here??
     foreach
     (DataGridViewRow row in
     dataGridView1.Rows)
    {
    if (row.Selected)
    {
    dataGridview1.Rows.RemoveAt[row.Index];
    break ; //use break if you only select one row!
    //if selected more, then remove it!
    }
    }
    Tuesday, February 22, 2011 1:22 PM
  • Hi Exodia,

    >But I'm already open OleDbConnection on the Window Form load. Does it really need to open again ??

    I think you should to check in your code whether the connection is available. According to the code message, I agree with Mitja.

    >This code only delete data grid view rows, but cannot delete the data in database. If i want both delete, How should i using the query here??

    I think Mitja has already provide your solution.

     using(OleDdConnection oleConn = new OldDbConnection("connectionString")) //ADDED
     {
     OleDbCommand delcmd = new OleDbCommand();
    
      for (i = 0; i < dataGridView1.Rows.Count; i++)
      {
      DataGridViewRow delrow = dataGridView1.Rows[i];
      if (delrow.Selected == true)
      {
       dataGridView1.Rows.RemoveAt(i);
       try
       {
       delcmd.CommandText = "DELETE FROM Receive WHERE CustCode=" + i + "";
       oleConn.Open(); //ADDED
       int count = delcmd.ExecuteNonQuery();
       }
       catch (Exception ex)
       {
       MessageBox.Show(ex.ToString());
       }
      }
      }
     }
    Here is the code Mitja provided that you can delete data in database.
    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 23, 2011 8:04 AM
  • Because besides this delete button. I have done the find button with select query in the same window form. It still can work.
    Wednesday, February 23, 2011 9:46 AM
  • Hi Exodia,

    >Because besides this delete button. I have done the find button with select query in the same window form. It still can work.

    Also, what is wrong with the code to delete records? Ans waht's wrong with the find button? You mean "it still can work" that it works for you now? And if it does not work, it that some error messages pop up? Please clarify your request. Thanks.


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 24, 2011 7:10 AM
  • My Whole window form code have some few button. Search button, delete button and others.

    Normally I will open the connection string with connection open in private void form_load. This means open the connection when open the window form right ??

     

    Other button work ok with database. Only this delete button will come out that error msg box, like the one above. Those in the same window form code.

     

    The code up there also came with same message.

     

    This was the whole code using

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    
    using System.Data.OleDb;
    
    namespace Management_System
    {
      public partial class list : Form
      {
        string strlpay;
        OleDbConnection cnlpay;
    
        OleDbCommand cmdlpay;
        DataTable dtpay = new DataTable();
        OleDbDataReader drlpay;
    
        public list()
        {
          InitializeComponent();
        }
    
        private void list_Load(object sender, EventArgs e) //Windows form load
        {
          dtpay.Columns.Add("Date");
          dtpay.Columns.Add("Customer Code");
          dtpay.Columns.Add("Customer Name");
          dtpay.Columns.Add("Pay (RM)");
    
          strlpay = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Fish Management System.mdb";
          cnlpay = new OleDbConnection(strlpay);
          
          string loadlpay ="SELECT Rdate, CustCode, CustName, Pay FROM Receive";
    
          cnlpay.Open();
    
          cmdlpay = new OleDbCommand(loadlpay, cnlpay);
          drlpay = cmdlpay.ExecuteReader();
    
          while (drlpay.Read())
          {
            DataRow row = dtpay.NewRow();
            row["Date"] = drlpay["Rdate"];
            row["Customer Code"] = drlpay["CustCode"];
            row["Customer Name"] = drlpay["CustName"];
            row["Pay (RM)"] = drlpay["Pay"];
    
            dtpay.Rows.Add(row);
          }
          dataGridView1.DataSource = dtpay;
    
        }
    
        private void can_list_Click(object sender, EventArgs e) //Close Button
        {
          cnlpay.Close();
          this.Close();
        }
    
        private void del_list_Click(object sender, EventArgs e) //Delete Button
        {
    
          using (OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Fish Management System.mdb")) //ADDED
          {
            OleDbCommand delcmd = new OleDbCommand();
            int i=0;
    
            for (i = 0; i < dataGridView1.Rows.Count; i++)
            {
              DataGridViewRow delrow = dataGridView1.Rows[i];
              if (delrow.Selected == true)
              {
                dataGridView1.Rows.RemoveAt(i);
                try
                {
                  delcmd.CommandText = "DELETE FROM Receive WHERE CustCode=" + i + "";
                  oleConn.Open(); //ADDED
                  int count = delcmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                  MessageBox.Show(ex.ToString());
                }
              }
            }
          }
          
        }
    
        private void schlpay_Click(object sender, EventArgs e) //Search Button
        {
          dataGridView1.ClearSelection();
    
          OleDbDataReader drfnd;
          DataTable dtfnd = new DataTable();
    
          dtfnd.Columns.Add("Date");
          dtfnd.Columns.Add("Customer Code");
          dtfnd.Columns.Add("Customer Name");
          dtfnd.Columns.Add("Pay (RM)");
    
          string fndlpay = "SELECT Rdate, CustCode, CustName, Pay FROM Receive WHERE Rdate = ?";
    
          OleDbCommand cmdfnd = new OleDbCommand(fndlpay, cnlpay);
          OleDbParameterCollection fndpc = cmdfnd.Parameters;
          fndpc.Add("@Rdate", OleDbType.Date, 255).Value = dateTimePicker1.Text;
          
          drfnd = cmdfnd.ExecuteReader();
    
          while (drfnd.Read())
          {
            DataRow row = dtfnd.NewRow();
            row["Date"] = drfnd["Rdate"];
            row["Customer Code"] = drfnd["CustCode"];
            row["Customer Name"] = drfnd["CustName"];
            row["Pay (RM)"] = drfnd["Pay"];
    
            dtfnd.Rows.Add(row);
          }
          dataGridView1.DataSource = dtfnd;
    
        }
    
      }
    }
    
    

    Thursday, February 24, 2011 8:36 AM
  • Hi Exodia,

    First, As you have defined a connection in your list_load event, why are you need to add a new connection in the delete click event?

    >But I'm already open OleDbConnection on the Window Form load. Does it really need to open again ??

    Then, In your original, the Mijta has asked you to "no need to add 'oleConn.Connection.Open();' again", what he means is that you have already open in the list_load event, so you do not need to add the code statement "oleConn.Connection.Open();", just comment out or delete the statement.


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 25, 2011 3:14 AM
  • I wish to ask how i can delete the both database and selected row from data grid view by using C# windows form button??

    This was the code i using --

     

     private void del_list_Click(object sender, EventArgs e)
      {
       System.Collections.ArrayList alv = new System.Collections.ArrayList();
       int i = 0;
    
       OleDbCommand delcmd = new OleDbCommand();
    
       for (i = 0; i < dataGridView1.Rows.Count; i++)
       {
        DataGridViewRow delrow = dataGridView1.Rows[i];
        if (delrow.Selected == true)
        {
         dataGridView1.Rows.RemoveAt(i);
         try
         {
          delcmd.CommandText = "DELETE FROM Receive WHERE CustCode=" + i + "";
          int count = delcmd.ExecuteNonQuery();
         }
         catch (Exception ex)
         {
          MessageBox.Show(ex.ToString());
         }
    
        }
       }
       
      }
    

     

    and getting some kind of error like this--

    http://www.mypicx.com/02212011/Error_Msg/

     

    Because by this error, The things given seem like no connection in it .
    Saturday, February 26, 2011 11:13 PM
  • Hi Exodia,

    First, As you have defined a connection in your list_load event, why are you need to add a new connection in the delete click event?

    >But I'm already open OleDbConnection on the Window Form load. Does it really need to open again ??

    Then, In your original, the Mijta has asked you to "no need to add 'oleConn.Connection.Open();' again", what he means is that you have already open in the list_load event, so you do not need to add the code statement "oleConn.Connection.Open();", just comment out or delete the statement.


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Because at the first, I get the error like no connection error.
    Saturday, February 26, 2011 11:15 PM
  • Hi,

    Exodia listen carefuly: you do NOT have to open a connection of form load or some place else where you don not need it. You OPEN a connection ONLY when and exactly there where you need it! Understood?

    So when you want to access to the database, only in that method you open a connection and just after finishing working with database, you HAVE to close it as well. If you will use "using" keyword, the connection will close by it self as soon as it will leave trhe "using(OleDbConnecton.... )){}" brackets.

    This is it. And please if you will follow these simple rules, you will never come into troubles again. Use my code from above and all will be find.

    Hope it helps,

    Mitja

    Sunday, February 27, 2011 8:43 AM