Delete selected row in datagridview and also delete row in database

Question

I want delete selected row in datagridview and also delete row in Mysql.

private void deleteOrderButton_Click(object sender, EventArgs e)
{
    int selectedIndex = orderDataGridView.CurrentCell.RowIndex;

    if (selectedIndex > -1)
    {
        orderDataGridView.Rows.RemoveAt(selectedIndex);
        orderDataGridView.Refresh();
    }

    string constring = "datasource=localhost;port=3306;username=admin;password=acw123";
    string Query = "delete from database.tem_order where temp_orderID = ????
    MySqlConnection conDatabase = new MySqlConnection(constring);
    MySqlCommand cmdDataBase = new MySqlCommand(Query, conDatabase);
    MySqlDataReader myReader;

    conDatabase.Open();
    myReader = cmdDataBase.ExecuteReader();
    MessageBox.Show("Updated");

I stuck in MySql command there; someone help?

the datasource was from same table, i retrive out to display and i want to delete selected item in datagrid and also in database — user2601433
– user2601433, Commented Jul 31, 2013 at 17:04

Martijn van Put · Accepted Answer · 2013-07-31 17:25:47Z

2

When using a datasource on the DataGridView you can retreive the object of the selected row.

DataRow row = (dataGridView.SelectedRows[0].DataBoundItem as DataRowView).Row;

The DataRow 'row' should contain the ID which allows you to delete the record in MySQL. Replace "ID-column-name" with the real column name

using(MySqlConnection sqlConn = new MySqlConnection("datasource=localhost;port=3306;username=admin;password=acw123"))
{
    sqlConn.Open();

    using(MySqlCommand sqlCommand = new MySqlCommand("DELETE FROM tem_order WHERE temp_orderID = " + row["ID-column-name"],sqlConn))
    { 
        sqlCommand.ExecuteNonQuery();
    }
}

edited Jul 31, 2013 at 17:25

answered Jul 31, 2013 at 17:07

Martijn van Put

3,31320 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2601433 Over a year ago

how about the query to delete the row?

Martijn van Put Over a year ago

Take a look at the anwser again, i've added a sample for the delete query.

user2601433 Over a year ago

error occur Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

Martijn van Put Over a year ago

Do you have a selected row?

WinHtaikAung · Accepted Answer · 2013-07-31 17:15:42Z

0

I hope it could help

dataGridView1.Rows.RemoveAt(item.Index);

By the way You should use transaction Scope to make those two actions my advice

edited Jul 31, 2013 at 17:15

answered Jul 31, 2013 at 17:07

WinHtaikAung

4242 silver badges11 bronze badges

3 Comments

Martijn van Put Over a year ago

RemoveAt doesn't give an ID back which he needs to perform an MySQL delete.

user2601433 Over a year ago

i want to ask what shoud i type in database query string Query = "delete from database.tem_order where temp_orderID = ????

odlan yer Over a year ago

use for do loop to get selected row and assign like int ordrID = YourDatagrid.Rows[i].Cells[c].Value; and your Query = "delete from database.tem_order where temp_orderID = ordrID

Huy Hoàng Phạm · Accepted Answer · 2013-08-01 01:33:22Z

0

You can get the OrderID from the selected cell.

        int selectedIndex = orderDataGridView.CurrentCell.RowIndex;
        String selectedOrderID = "";
        if (selectedIndex > -1)
        {

            orderDataGridView.Rows.RemoveAt(selectedIndex);
            //Replace "OrderID" with the column name of the Order's ID
            selectedOrderID = orderDataGridView.Rows[selectedIndex].Cells["OrderID"].Value.ToString();

        }

Your query will be like this:

        string Query = "delete from database.tem_order where temp_orderID = " + selectedOrderID ;
        //This is only an example, you should set the params to avoid SQL Injection

Then you just delete and refresh the gridview, done:

        conDatabase.Open();
        myReader = cmdDataBase.ExecuteReader();
        orderDataGridView.Refresh();

edited Aug 1, 2013 at 1:33

answered Jul 31, 2013 at 17:14

Huy Hoàng Phạm

1451 gold badge2 silver badges10 bronze badges

2 Comments

user2601433 Over a year ago

string selectedOrderID = 0; Cannot implicitly convert type 'int' to 'string'

Huy Hoàng Phạm Over a year ago

Sorry, my bad, it must be: String selectedOrderID = "";

kleopatra · Accepted Answer · 2014-03-12 09:20:34Z

DialogResult dr = XtraMessageBox.Show(Global.lk, "Are you sure to Delete this record ?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Information);

            if (dr.ToString() == "Yes")
            {
                int[] selectedRows = gridViewDeduction.GetSelectedRows();
                string type_id = gridViewDeduction.GetRowCellValue(selectedRows[0], gridViewDeduction.Columns["TypeId"]).ToString();


                if (type_id != "")
                {
                    string xsql = "DELETE FROM Pay_DeductionTypeMaster WHERE TypeId = " + type_id + " AND CompanyID = " + Global.CompanyID + "";
                    bool del = Database.ExecuteSQL(xsql);
                    if (!del)
                    {
                        XtraMessageBox.Show(Global.lk, "Unable to delete record.This deduction details is in use.", "Deduction Type");                          
                        return;
                    }
                    dt.Rows.RemoveAt(gridViewDeduction.FocusedRowHandle);
                }
                else
                    dt.Rows.RemoveAt(gridViewDeduction.FocusedRowHandle);
                dt.AcceptChanges();
                gridViewDeduction.RefreshData();

Roland Lee · Accepted Answer · 2017-03-02 04:14:48Z

0

("delete from TableName where ID = ('"+Grid1.Rows[Grid1.CurrentRow.Index].Cells["ID"].Value+"');"); //get the value of selected cell

answered Mar 2, 2017 at 4:14

Roland Lee

1

1 Comment

Sid Over a year ago

Please format it correctly and add some description.

Collectives™ on Stack Overflow

Delete selected row in datagridview and also delete row in database

5 Answers 5

4 Comments

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related