Adding datatable row and column and updating datagridview

Question

            string conString = "Server=192.168.1.100;Database=product;Uid=newuser;Pwd=password";
            MySqlConnection conn = new MySqlConnection(conString);
            DataTable dt = new DataTable();
            DataRow row = dt.NewRow();
            conn.Open();
            //cmd = conn.CreateCommand();
            //cmd.CommandText = "Select * From tblindividualproduct";
            if (e.KeyCode == Keys.Enter)
            {
                if (txtBarcode.Text == "")
                {
                    MessageBox.Show("Please Fill the correct ProductID");
                }
                else
                {
                    string sql = "Select * From tblindividualproduct where ProductID = @ProductIdText";

                    using (var adapt = new MySqlDataAdapter(sql, conn))
                    using (var cmd = new MySqlCommandBuilder(adapt)) //Not sure what you need this for unless you are going to update the database later.
                    {
                        adapt.SelectCommand.Parameters.AddWithValue("@ProductIdText", txtBarcode.Text);
                        BindingSource bs = new BindingSource();
                        adapt.Fill(dt);
                        bs.DataSource = dt;
                        dgItems.ReadOnly = true;
                        dgItems.DataSource = bs;
                    }
                }
            }

How do I make the results to add, not just replace the last result. This is the whole code as requested. I just dont know if it needs manually adding of rows or there is an easy way to make it. Thank in advance

You aren't using your `BindingSource`, you assign it but never bind to it. It should read `dgItems.DataSource = bs;` Aside from that, do you mean you would like to add rows/columns to `dt`? — Evan L, Mar 15 '14 at 16:30
when i input 2 in my textbox it will show the product with the product id of 2 and when i want to input 3 i want it to add new row and display the product 2 and 3 in the gridview. — Mix Austria, Mar 15 '14 at 16:38

score 1 · Accepted Answer · edited May 23 '17 at 12:21

First of all, you should use Parameterized SQL to avoid SQL Injection.

Then you just need to put the code you have in an event handler of some sort. I would think the user would probably hit the enter key or something in the TextBox.

As long as you don't clear your DataTable it will just keep adding rows every time you run the query. If you want to clear the table at some other point in your code just call dt.Clear().

Something like this should get what you want and be safe from injection:

    private void txtBarCode_KeyUp(object sender, KeyEventArgs e)
    {
        if (e.KeyCode == Keys.Enter)
        {
            if(String.IsNullOrEmpty(txtBarcode.Text)) 
            {
                MessageBox.Show("Please Fill the correct ProductID");
            } 
            else 
            {
                 string sql = "Select * From tblindividualproduct where ProductID = @ProductIdText";

                 using (var adapt = new MySqlDataAdapter(sql, conn))
                 using (var cmd = new MySqlCommandBuilder(adapt)) //Not sure what you need this for unless you are going to update the database later.
                 {
                     adapt.SelectCommand.Parameters.AddWithValue("@ProductIdText", txtBarCode.Text);
                     BindingSource bs = new BindingSource();
                     adapt.Fill(dt);
                     bs.DataSource = dt;
                     dgItems.ReadOnly = true;
                     dgItems.DataSource = bs;
                 }
            }
        }
    }

Then make sure you have attached this event to your TextBox somewhere in your Form class like this:

this.txtBarCode.KeyUP += txtBarCode_KeyUp;

Now when the user types in the box and presses enter the query will run and the results will display in the DataGridView. No need to manually add columns and rows, the adapt.Fill(dt); Will do that for you.

thanks for this but gridview is still updating and not adding any rows. it just only display 1 result everytime i press the enter key. — Mix Austria, Mar 15 '14 at 17:05
Would need to see the code where you create the `DataTable` you are probably creating the `DataTable` over and over which clears it. You should declare it at the Class level. — Evan L, Mar 15 '14 at 17:15
@MixAustria Did you figure it out? Just declare the `DataTable` at the class level and use it through the lifetime of the form. Though I would recommend considering separating your data access layer from your presentation layer. — Evan L, Mar 15 '14 at 18:48
That works! Just make sure you call `Dispose()` when you are done with it as it is unmanaged. — Evan L, Mar 16 '14 at 03:28

Adding datatable row and column and updating datagridview

1 Answers1