We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

waelali
waelali asked
on
Medium Priority
5,020 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

Author

Commented:
Someone can help please??
CERTIFIED EXPERT

Commented:
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();

Author

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
CERTIFIED EXPERT

Commented:
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.

Author

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
CERTIFIED EXPERT

Commented:
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.

Author

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.

Author

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

        }
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Solution was found
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.