MySQL updating a entire column in datagrid

Question

im trying to update my stores stock levels with the use of a button, when this button is pressed i want all the quantities to be increased by the amount in the text box, ive had a go implementing some code to do this but it always hits the data not inserted message...

 using System;
 using System.Data;
 using System.Windows.Forms;
 using MySql.Data.MySqlClient;

 namespace Aliena_Store
{
public partial class Form3 : Form
{
    MySqlConnection connection = new MySqlConnection("server=localhost;user=root;database=Aliena_Store;port=3306;password=Blackie");
    public Form3()
    {
        InitializeComponent();
    }

    private void Form3_Load(object sender, EventArgs e)
    {
        {




          //MySqlConnection(VarribleKeeper.MySQLConnectionString);
            connection.Open();

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string sqlSelectAll = "SELECT * From Aliena_Store.Game_Details";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, connection);

            DataTable table = new DataTable();
            MyDA.Fill(table);

            BindingSource bSource = new BindingSource();
            bSource.DataSource = table;


            dataGridView1.DataSource = bSource;

        }


    }

    private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {


    }

    private void SeeForm2_Click(object sender, EventArgs e)
    {
        Hide();
        Form2 f = new Form2(); // This is bad
        f.Show();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        string updateQuery = ("UPDATE Aliena_Store.Game_details SET Quantity = '" + AddStock.Text + "'");


        try
        {
            MySqlCommand command = new MySqlCommand(updateQuery, connection);
            if (command.ExecuteNonQuery() == 1)
            {
                MessageBox.Show("DATA UPDATED");
            }
            else
            {
                MessageBox.Show("Data NOT UPDATED");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

    }

    private void AddStock_TextChanged(object sender, EventArgs e)
    {

    }
}
}

any clue where my code is going wrong?

You should always use parameterized SQL to have your code more clear, avoids conversion issues, and avoids SQL injection attacks. — Salah Akbari, Jan 17 '18 at 17:08
0 point currently as its a university project that isnt meant to show security features — Dusky, Jan 17 '18 at 17:08
In`ExecuteNonQuery` *For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.* See this post https://stackoverflow.com/a/38060528/2946329 — Salah Akbari, Jan 17 '18 at 17:09
i currently have 5 items so 5 unique games so it should update the quantity of all 5 to say 5 if thats what is entered into the text box.. mind explaining how to do a where clause? (very new to mysql and c#) — Dusky, Jan 17 '18 at 17:11

Steve · Accepted Answer · 2018-01-17T17:21:48.470

Your update query doesn't have a WHERE clause, so every record is set to the new quantity and ExecuteNonQuery will give you back a number with the count of the rows changed.
The code will hit the correct if case only if you have just one row in the table.

An easy fix is the following

if (command.ExecuteNonQuery() > 0)
    ... ok ...

Instead if you want to update only a single record then you need to add a WHERE condition to your query. But this WHERE condition requires that you provide the value of the PrimaryKey of your database table allowing the engine to identify the record to change.

So for example

string updateQuery = @"UPDATE Aliena_Store.Game_details 
                       SET Quantity = @qty 
                       WHERE GameID = @id";

This query will update only the record with the specified GameID (where GameID is the hypothetical name of your field with the primary key for the table)

Notice that I use parameter placeholders in the query. While this is not the main subject of your question it's worth to note that writing correct SQL code will give you many advantages apart from the security point. No problem in parsing string text to correct datatype, more readability of the sql command.

MySqlCommand command = new MySqlCommand(updateQuery, connection);
command.Parameters.Add("@qty", MySqlDbType.VarChar).Value = AddStock.Text;
command.Parameters.Add("@id", MySqlDbType.Int32).Value = Convert.ToInt32(txtGameID.Text);
if (command.ExecuteNonQuery() > 0)
     ....

MySQL updating a entire column in datagrid

1 Answers1