Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4696
  • Last Modified:

C# - delete some selected rows from DataGrid - Winform and then delete them from MS Access Database

Hi,

Hope you can help soon.

I have a datagrid winform (Not DataGridView) - that has DataSet in MS Access DataBase as DataSource , and I am using Oledb amd sql statemants in order to make changes to teh Access Database.

I have a datagrid that has CheckBox as first column, and when I select one or more of the checkboxes and then press delete I want to delete the rows from the WinForm DataGrid ( That is ok - done), but I could not delete them from teh MS Access Database.

Code is attached .

Youc an see there   datatable.Rows.Remove(theRowToDelete); whic deletes the row from teh winform datagrid.

and you can find in teh code a call to  DeleteRowFromAccessDataBase(theRowToDelete) - which in turn will delete the row from the Access Database. This is not implemented yet.

Can you tell me how can I implement it??

Or if there is any way to tell the Access Database to be updated automatically according to teh changes in teh WinForm DataGrid.

Thank you
public void DeleteSelectedCheckBoxes()
        {
 
            listOfCheckedRows.Clear();
            for (int i = 0; i < datatable.Rows.Count; i++)
            {
                if ((bool)datatable.Rows[i][0] == true)
                {
                    int index = datatable.Rows.IndexOf(datatable.Rows[i]);
                    listOfCheckedRows.Add(index);
                }
            }
 
            for (int j = listOfCheckedRows.Count-1 ; j>=0 ; j--)
            {
           DataRow theRowToDelete = datatable.Rows[Convert.ToInt32(listOfCheckedRows[j])];
                datatable.Rows.Remove(theRowToDelete);
                listOfCheckedRows.RemoveAt(j);
                DeleteRowFromAccessDataBase(theRowToDelete);
            }
 
            datatable.AcceptChanges();
            listOfCheckedRows.Clear();
            m_NumberOfSelectedCheckBoxes = 0;
            this.DataGirdClients.Show();
 
        }

Open in new window

1.JPG
0
waelali
Asked:
waelali
  • 7
  • 3
1 Solution
 
waelaliAuthor Commented:
Someone can help please??
0
 
angus_young_acdcCommented:
You could try the following:

string sql = "DELETE FROM [YOURTABLE] WHERE [YOURCOLUMN]='[WHATYOUWANTTODELETE]'";
OleDbConnection conn = null;
conn = new OleDbConnection([your database path etc]);
conn.Open();
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.ExecuteNonQuery();
0
 
waelaliAuthor Commented:
I want to delete a row man , and not a column , and I guess in this way in order to delete the whole row , I must walk over all the column in this row , and delete the data there. I have 31 column :-)

It is hard to do that
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
angus_young_acdcCommented:
That does delete the row.  I use exactly the same code myself to update an access database after deleting a row from a grid.  Your column would simply be, for example, the column containing ID numbers.  EG, row 1, row 2, row 3 etc.  In essence using the rows ID to delete it.
0
 
waelaliAuthor Commented:
Ok I understand that.
as you can see that the function that I want to implement is

public void DeleteRowFromAccessDataBase(DataRow RowToDelete)

The parameter is "DataRow RowToDelet"

So may you can tell me please the right format exactly how can I write the sql statements format exactly using the RowToDelet??

Actually, this is my problem, and compiler gives exception here always.

Thank you
Wael
0
 
angus_young_acdcCommented:
What error message does it give?  I'm guessing cannot convert from type A to type B?  

And why are you passing a parameter of type DataRow?  You shouldn't need to do that.  Heck you could just use your datatable.Rows index to create the delete.
0
 
waelaliAuthor Commented:
Have  alook on the code that I have pasted here - in the begining of the page. I am trying to delete just checked rows. The attached image can show you that , and the code implement that.
0
 
waelaliAuthor Commented:
The exception that I get is :

 "Data type mismatch in criteria expression."

       public void DeleteRowFromAccessDataBase(DataRow RowToDelete)
        {
            con.Close();
            con.Open();

            try
            {
                 OleDbCommand command = new OleDbCommand();
                cmd.CommandText = "DELETE FROM Clients WHERE " + RowToDelete["ID"].ToString() + "=" + " ' " + RowToDelete.ItemArray[4] + " ' ";
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception e)
            {
                System.Diagnostics.Debug.WriteLine("Error: " + e.Message);
            }
            con.Close();

        }
0
 
waelaliAuthor Commented:
I have figuered out

            con.Close();
            con.Open();

            try
            {
                OleDbCommand command = new OleDbCommand();
                cmd.CommandText = "DELETE FROM Clients WHERE Status= -1";
                cmd.Connection = con;
                adapter.DeleteCommand = cmd;
                adapter.DeleteCommand.ExecuteNonQuery();
            }
            catch (System.Exception e)
            {
                System.Diagnostics.Debug.WriteLine("Error: " + e.Message);
            }
            con.Close();


as you can see Status= -1 - in Acess True=-1

0
 
waelaliAuthor Commented:
Solution was found
0
 
PigdogmonsterCommented:
Hi waelali,  I hope you do not mind me asking but did you find a soltion for this?  I am having the exact same problem you were having :)

I am trying to delete a row from my winform datagrid but I cannot seem to trigger the event.  

If you have any help I would appreciate it very much.

Thanks

Stuart
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now