none
Delete a all records, not a single records RRS feed

  • Question

  • i have been doing like the code below, but why when i clicking the "delete" button, it is delete all records in the datagridview, but it does not updating to the database. And, how do i make only the selected row that has been selected by the user that gonna be deleted. I already can retrieve the "ID", which is that "ID" i gonna use as WHERE clause in my query.

    Here is the code:

    deleteButton.Click += new System.EventHandler(this.DeleteRecord);
    
     private void DeleteRecord(object sender, EventArgs e)
            {
                int i = dataGridView.SelectedCells[0].RowIndex;
                string strID = dataGridView.Rows[i].Cells[0].Value.ToString();
    
                if (fifthForm.comboBox1.Text == "English")
                {
                    using (OleDbConnection conn = new OleDbConnection(connectionString))
                    {
                        string query = "DELETE FROM [Record] WHERE [ID] = @ID";
                        conn.Open();
    
                        using (OleDbCommand cmd = new OleDbCommand(query, conn))
                        {
                            cmd.Parameters.Add("ID", System.Data.OleDb.OleDbType.Integer);
    
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
                            {
                                DataTable ds = new DataTable();
                                cmd.Parameters["ID"].Value = strID;
                                adapter.Update(ds);
                                dataGridView.DataSource = ds;
                                cmd.ExecuteNonQuery();
                            }
                        }
    
                        conn.Close();
                    }
                }
           }

    Could anyone help me? Thanks

    Wednesday, September 11, 2013 4:22 PM

Answers

  • Hi Fuhan,

    Your grid's DataSource is a DataTable. All the database updates should be done via that DataTable. DataAccess has nothing to do with UI components, such as grids, listboxes, textboxes, etc. In fact, in reality you should never have your DataAccess directly in your UI. But, that's another topic that I won't bore anyone with at the moment.

    For your question, what you need to do is the following:

    private void DeleteRecord(object sender, EventArgs e)
    {
        if (fifthForm.comboBox1.Text == "English")
        {
            DataTable dt = (DataTable)dataGridView.DataSource;
            int rowNum = dataGridView.CurrentRow.Index;
            int id = dt.DefaultView[rowNum]["id"];
            dt.DefaultView[rowNum].Delete();
    
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                string query = "DELETE FROM [SeranneRecord] WHERE id = @id";
                conn.Open();
    
                OleDbCommand cmd = new OleDbCommand(query, conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
                
                System.Media.SoundPlayer sound = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                sound.Play();
                MessageBox.Show("Deleted Successfully", "Deleted");
            }    
    }            
    



    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, September 11, 2013 8:01 PM

All replies

  • Hai, i have a problem. This is happen when i tried to delete a single record in the database when the program runs. The delete record is working, but it is delete all record in the database. How do i fix that? First of all, i view data in my database using datagridview, and once the user click certain row, and the user click delete, it is supposed to delete a certain row that user has been selected, but it is delete all records.

    Here is the code:

    private void DeleteRecord(object sender, EventArgs e)
            {
                if (fifthForm.comboBox1.Text == "English")
                {
                    using (OleDbConnection conn = new OleDbConnection(connectionString))
                    {
                        string query = "DELETE FROM [SeranneRecord]";
                        conn.Open();
    
                        using (OleDbCommand cmd = new OleDbCommand(query, conn))
                        {
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
                            {
                                DataTable ds = new DataTable();
                                dataGridView.Rows.RemoveAt(dataGridView.CurrentRow.Index);
                                adapter.Update(ds);
                                dataGridView.DataSource = ds;
                                cmd.ExecuteNonQuery();
    
                                System.Media.SoundPlayer sound = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                                sound.Play();
                                MessageBox.Show("Deleted Successfully", "Deleted");
                            }
                        }
                    }
                }

    Thanks, also i want to ask about how to modify the records using datagridview. Thanks

    • Moved by BonnieBMVP Wednesday, September 11, 2013 8:09 PM
    • Merged by Fred BaoModerator Thursday, September 12, 2013 1:08 AM They are the same.
    Tuesday, September 10, 2013 1:48 PM
  • It is deleting all your rows in the table because of the query

    string query = "DELETE FROM [SeranneRecord]";

    This query will delete all recordes in [SeranneRecord]

    So when you pass this as the select statement in

    //query is the SelectCommand for the adapter

    using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))

    it will delete all the records in the table when the adapter executes the select statement.

    To get the records in the database use

    string query = "SELECT * FROM [SeranneRecord]";
    This will get all the records from the table.

    • Proposed as answer by CoolDadTx Tuesday, September 10, 2013 2:11 PM
    • Unproposed as answer by FuhansPujiSaputra Wednesday, September 11, 2013 1:44 PM
    Tuesday, September 10, 2013 2:03 PM
  • I miss the WHERE clause in the DELETE command isn't it? But how do i let the computer know that the only the selected row that has been selected that's gonna be delete? if not using datagridview, i have to use PARAMETERS right? but i don't know how to do that when using datagridview. How should i do?
    Tuesday, September 10, 2013 2:13 PM
  • you should keep the key id of the row in the db somewhere in ur app and use it in the where

    Tuesday, September 10, 2013 2:50 PM
  • how do i do that @Bresleveloper?
    Tuesday, September 10, 2013 3:15 PM
  • hi:  this might help you: 

    http://stackoverflow.com/questions/12362191/updating-multiple-rows-in-datagridview-programmatically

    regards,

    Tuesday, September 10, 2013 4:17 PM
  • Hello ,

    I would suggest you to have a look at the DeleteCommand of your OleDbAdapter

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.deletecommand.aspx

    You have even a code example that you could use to create your DeleteCommand.

    You have only to replace the name of your primary key and its value retrieved from the selected row of your DataGridView.

    Have a nice day

    PS : without knowing the structure of your table , it is difficult to help you more efficiently.

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Tuesday, September 10, 2013 5:43 PM
  • Hai @Papu Normand. I have the design like this on my database:

    Product Code | Quantity | Description | SubTotal | Total | Dates | Times | IssuedBy | To

    I want to delete the data from datagridview and also update it to the database based on the user selection and ProductCode..

    How do i do that? 

    Wednesday, September 11, 2013 10:03 AM
  • I would recommend using OleDbCommandBuilder which will create the right commands for deleting, updating etc for you.

     

    Noam B.



    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...

    • Proposed as answer by Noam B Wednesday, September 11, 2013 10:34 AM
    • Unproposed as answer by FuhansPujiSaputra Wednesday, September 11, 2013 1:44 PM
    Wednesday, September 11, 2013 10:33 AM
  • Hello ,

    Please , could you tell us if in your table , there is a column ( maybe Product Code ) which can have an unique value ? If yes , you will have to use

    DELETE FROM [SeranneRecord] WHERE [Product Code] = "Value of Product Code to delete"

    as command for the deletion ( I am supposing that there is only one row by Product Code ).

    The CREATE TABLE [SeranneRecord] T-SQL definition full statement ( including using keys ) would be appreciated to help you more efficiently.

    Please , also , could you explain what you mean when you are writing " update it to the database based on the user selection and ProductCode" ? Do you mean that the [Product Code] is not the only one criterion to delete a row ? Do you want to delete several rows in the same Delete commands ?

    Please , could you have a look at these 3 links ?

    http://msdn.microsoft.com/en-us/library/system.data.datarow.rowstate.aspx

    http://msdn.microsoft.com/en-us/library/system.data.datarow.acceptchanges.aspx

    http://msdn.microsoft.com/en-us/library/system.data.datarow.delete.aspx

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Wednesday, September 11, 2013 12:21 PM
  • Hello for moderators ,

    I think that the best forum for this kind of problem is

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataset&filter=alltypes&sort=lastpostdesc

    Is it possible for a moderator to move this thread towards this forum ? It is what I am doing when I find this kind of problem in the SQL Server Data Access Forum as I have discovered that similar threads are more quickly answered on this forum .

    Thanks beforehand and have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Wednesday, September 11, 2013 12:30 PM
  • Hai Papy Normand,

    Here is the datagridview when i run the program:

    I want when the user select "ID 9" and click the "delete" button, it will delete the selected row also delete it at database.

    How could i do that? I mean, what should i put in my command? I know my command is missing the "WHERE" clause, but i don't know what to put in the "WHERE" clause.

    Please help. Thanks

    i already tried this code:
    using (OleDbConnection conn = new OleDbConnection(connectionString))
                    {
                        string query = "DELETE FROM [Record] WHERE [ID] = 9";
                        conn.Open();
    
                        using (OleDbCommand cmd = new OleDbCommand(query, conn))
                        {
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
                            {
                                DataTable ds = new DataTable();
                                adapter.Update(ds);
                                dataGridView.DataSource = ds;
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }

    The above code is working, but it is select the row where the ID is 9, what if i got ID 10, ID 11 and so on until ID 100, i didn't want to put the query one by one. Is there any ways to do that? I mean, when the user select a row, no matter what ID it is, it will be delete the row and update it to the database.. Thanks
    Wednesday, September 11, 2013 1:49 PM
  • Here is the datagridview when i run the program:

    I want when the user select "ID 9" and click the "delete" button, it will delete the selected row also delete it at database.

    How could i do that? I mean, what should i put in my command? I know my command is missing the "WHERE" clause, but i don't know what to put in the "WHERE" clause.

    Here is the code that i have been doing right now (the following code is delete all row in a single click)

    using (OleDbConnection conn = new OleDbConnection(connectionString))
                    {
                        string query = "DELETE FROM [Record]";
                        conn.Open();
    
                        using (OleDbCommand cmd = new OleDbCommand(query, conn))
                        {
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
                            {
                                DataTable ds = new DataTable();
                                adapter.Update(ds);
                                dataGridView.DataSource = ds;
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }

    Now, my problem is, how do i delete it from datagridview and update it to the database when the user select a single row? Please help. Thanks.

    I already tried this code:

    using (OleDbConnection conn = new OleDbConnection(connectionString))
                    {
                        string query = "DELETE FROM [Record] WHERE [ID] = 9";
                        conn.Open();
    
                        using (OleDbCommand cmd = new OleDbCommand(query, conn))
                        {
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
                            {
                                DataTable ds = new DataTable();
                                adapter.Update(ds);
                                dataGridView.DataSource = ds;
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }

    The above code is working, but it is select the row where the ID is 9, what if i got ID 10, ID 11 and so on until ID 100, i didn't want to put the query one by one. Is there any ways to do that? I mean, when the user select a row, no matter what ID it is, it will be delete the row and update it to the database.. Thanks


    Wednesday, September 11, 2013 1:52 PM
  • Hello ,

    I gave you a link in my last post of the September, 10th which is including a code example of the way to create a Delete command. You should study it.

    I am not using OLEDB because it is a depreciated provider which should disappear in less than 3 years. Another reason ( a less convincing reason is that it is mainly used with Access that I don't like and I rejected Access when I discovered SQL Server 6.5 around 15 years ago  ). I gave you some ideas that you should work ( it is the way I followed and now I am moderator on several SQL Server forums including SQL Server Data Access since 2008 ).

    I recognize that you will find more examples about the System.Data.SqlClient and System.Data.ODBC namespaces but the code examples for ODBC are similar to the ones related to ODBC and the translation ODBC ==> OLEDB should be very easy.

    Have a nice day

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Wednesday, September 11, 2013 6:28 PM
  • Hello ,

    I have reread your last post and I think I missed something : there is the possibility that you have several rows to delete in the same time. The best way is to modify the rows of your datatable to mark them as delete, to call acceptchange and to use the datatable.update() method. See my 1st reply I have posted today.

    I am going on but I am thinking that your thread should be moved towards the ADONetDataSet forum. I cannot do that as I am not a moderator on this forum, this move can only be done by a moderator. I would suggest you to ask to the moderators to do this move. The full link towards this forum is at the end of my 2nd reply posted today.

    Have a nice day 


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Wednesday, September 11, 2013 6:55 PM
  • Hi Fuhan,

    Your grid's DataSource is a DataTable. All the database updates should be done via that DataTable. DataAccess has nothing to do with UI components, such as grids, listboxes, textboxes, etc. In fact, in reality you should never have your DataAccess directly in your UI. But, that's another topic that I won't bore anyone with at the moment.

    For your question, what you need to do is the following:

    private void DeleteRecord(object sender, EventArgs e)
    {
        if (fifthForm.comboBox1.Text == "English")
        {
            DataTable dt = (DataTable)dataGridView.DataSource;
            int rowNum = dataGridView.CurrentRow.Index;
            int id = dt.DefaultView[rowNum]["id"];
            dt.DefaultView[rowNum].Delete();
    
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                string query = "DELETE FROM [SeranneRecord] WHERE id = @id";
                conn.Open();
    
                OleDbCommand cmd = new OleDbCommand(query, conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
                
                System.Media.SoundPlayer sound = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                sound.Play();
                MessageBox.Show("Deleted Successfully", "Deleted");
            }    
    }            
    



    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, September 11, 2013 8:01 PM
  • I was  about to move this over to the ADO.NET forum, but I see that @Fuhan has re-asked the question (twice) over there. So, I'm going to ahead and move this thread over there anyway, and Merge the other two threads to this one.

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, September 11, 2013 8:05 PM
  • Ooops, can't do that ... I moved this thread to the ADO.NET forum (because I am a C# moderator), but now I have no way of merging them all together, because I'm not a moderator on the ADO.NET forum. Darn!

    I'll put links to this thread on those two threads. 


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, September 11, 2013 8:12 PM
    • Edited by BonnieBMVP Wednesday, September 11, 2013 8:14 PM
    Wednesday, September 11, 2013 8:13 PM
  • This question has already been asked in this thread:

    http://social.msdn.microsoft.com/Forums/en-US/031b14f4-ba70-4d6e-8445-0e8a993abc89/delete-a-record-successful-but-it-delete-all-records-in-the-database-in-windows-forms

    I'm not a Moderator here, so I can't merge the threads.


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, September 11, 2013 8:14 PM
  • Hi Bonnie ,

    Thanks anyway. I had met your problem : the solution is to do the merge before the move ( an advice that Arnie Rowlands gave me when I began to be a moderator ).

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Wednesday, September 11, 2013 8:55 PM
  • Hi Bonnie ,

    Thanks anyway. I had met your problem : the solution is to do the merge before the move ( an advice that Arnie Rowlands gave me when I began to be a moderator ).

    Yes, I should have thought of that before I moved it. I've done it before, but I guess I forgot about it. Somebody did merge everything later, so now it's all good.  =0)

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, September 12, 2013 4:37 AM
  • Hai Ms. BonnieB. Amazing! Your code is working fine for me. You were helped me a lot.

    Thank you so much, glad to have you in this forum :)

    Sincerely,

    Fuhans

    Thursday, September 12, 2013 4:37 AM
  • You're welcome, Fuhans! I'm glad that I could help you!  =0)

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, September 12, 2013 4:40 AM
  • I'm glad too that you been in this forum! :)

    Thanks anyway
    Thursday, September 12, 2013 4:42 AM