C# – Delete selected row from datagrid view and update thesql database in C#

cdatagriddatagridviewMySQLwinforms

m developing application for my office and in my application there is a datagrid view that linked to a mysql database. local users can update the database using datagridview but they cant delete any recodes. i want to implement a method that user's are select exact raw in the datagridview and delete it as well as delete the database record soon. i managed to make select and delete datagridview row using below code but it not update the database

private void button60_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewCell oneCell in dataGridView1.SelectedCells)
            {
                if (oneCell.Selected)
                    dataGridView1.Rows.RemoveAt(oneCell.RowIndex);
            }

        }

and this is my connection string that i normally use to view the database data in datagrid view. i don't have good knowledge to combined these two.can someone please show me how to do that

my connection string

private void showdatagrid()
{

        string constring = string.Format("datasource='{0}';username=******;port=3306;password=***********;Connect Timeout=20000;Command Timeout=28800", dbserverip.Text);
        MySqlConnection conwaqDatabase = new MySqlConnection(constring);
        MySqlCommand cmdwaqDatabase = new MySqlCommand(" select * from warit.loans ; ", conwaqDatabase);


        try
        {

            MySqlDataAdapter sda = new MySqlDataAdapter();
            sda.SelectCommand = cmdwaqDatabase;
            dbdataset = new DataTable();
            sda.Fill(dbdataset);
            BindingSource bsource = new BindingSource();

            bsource.DataSource = dbdataset;
            dataGridView1.DataSource = bsource;
            sda.Update(dbdataset);

        }


        catch (Exception ex)
        {


            MessageBox.Show(ex.Message);

        }

Best Answer

try this code

private void button60_Click(object sender, EventArgs e)
{
    foreach (DataGridViewCell oneCell in dataGridView1.SelectedCells)
    {
        if (oneCell.Selected)
        {
            dataGridView1.Rows.RemoveAt(oneCell.RowIndex);
            int loannumber = dataGridView1.Rows[oneCell.RowIndex].Cells['index of loannumber column in datagridview'].Value; // assuming loannmber is integer
            string username = dataGridView1.Rows[oneCell.RowIndex].Cells['index of username column in datagridview'].Value; // assuming username is string
            /* Now create an object of MySqlConnection and MySqlCommand
             * and the execute following query
             */
            string query = string.Format("DELETE FROM table_name WHERE loannumber = {0} AND username = '{1}'", loannumber, username);
        }
    }

}

Related Solutions

MySQL Error 1093 – Can’t specify target table for update in FROM clause

Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question

In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

Maybe you can just join the table to itself

If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Alternatively, try nesting the subquery deeper into a from clause ...

If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating.

... but watch out for the query optimiser

However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.

SET optimizer_switch = 'derived_merge=off';

Thanks to Peter V. Mørch for this advice in the comments.

Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.

Mysql – Create new user in MySQL and give it full access to one database

Try this to create the user:

CREATE USER 'user'@'hostname';

Try this to give it access to the database dbTest:

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

If you are running the code/site accessing MySQL on the same machine, hostname would be localhost.

Now, the break down.

GRANT - This is the command used to create users and grant rights to databases, tables, etc.

ALL PRIVILEGES - This tells it the user will have all standard privileges. This does not include the privilege to use the GRANT command however.

dbtest.* - This instructions MySQL to apply these rights for use in the entire dbtest database. You can replace the * with specific table names or store routines if you wish.

TO 'user'@'hostname' - 'user' is the username of the user account you are creating. Note: You must have the single quotes in there. 'hostname' tells MySQL what hosts the user can connect from. If you only want it from the same machine, use localhost

IDENTIFIED BY 'password' - As you would have guessed, this sets the password for that user.

Best Answer

Related Solutions

MySQL Error 1093 – Can’t specify target table for update in FROM clause

Mysql – Create new user in MySQL and give it full access to one database

Related Topic