How to insert datagridview record into mysql database using foreach

Question

This code runs fine Values from the dataGridview are also saved succesfully but after saving the entered values in database a null row is also added in the tabl

    private void save_Click(object sender, EventArgs e)
    {
       foreach (DataGridViewRow row in dataGridView1.Rows)// picks data from dataGridview                
        {

            try   // MySql connection
            {
                string MyConnectionString = "Server=localhost; Database=markcreations; Uid=root; Pwd=admin";
                MySqlConnection connection = new MySqlConnection(MyConnectionString);
                MySqlCommand cmd = new MySqlCommand();
                cmd = connection.CreateCommand();
                cmd.Parameters.AddWithValue("@invoice", row.Cells["Invoice"].Value);
                cmd.Parameters.AddWithValue("@jobOrder", row.Cells["jobOrder"].Value);
                cmd.Parameters.AddWithValue("@dateTime", row.Cells["Date"].Value);
                cmd.Parameters.AddWithValue("@clientCode", row.Cells["Client Code"].Value);
                cmd.Parameters.AddWithValue("@clientName", row.Cells["Client Name"].Value);

                cmd.CommandText = "INSERT INTO record(invoice, jobOrder, dateTime, clientCode, clientName)VALUES(@invoice, @jobOrder, @dateTime, @clientCode, @clientName)";
                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        MessageBox.Show("Records inserted.");
    }

HSS · Accepted Answer · 2015-12-05 10:01:43Z

1

It looks like you have an empty row in the DataGridView, probably the last one, which is used for a new row. That row has null values for each column and if you dont check for it, it would insert the NULL's.

You can use row.IsNewRow, to check for the new row.

edited Dec 5, 2015 at 10:01

answered Dec 5, 2015 at 9:56

HSS

385 bronze badges

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

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

Thanks buddy it worked.

Here is the Correct code along with row.IsNewRow

    private void button5_Click(object sender, EventArgs e)
    {
        string MyConnectionString = "Server=localhost; Database=markcreations; Uid=root; Pwd=admin";
        MySqlConnection connection = new MySqlConnection(MyConnectionString);

        foreach (DataGridViewRow row in dataGridView1.Rows)                   
        {
         try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd = connection.CreateCommand();
                if (row.IsNewRow) continue;
                cmd.Parameters.AddWithValue("@invoice", row.Cells["Invoice"].Value);
                cmd.Parameters.AddWithValue("@jobOrder", row.Cells["jobOrder"].Value);
                cmd.Parameters.AddWithValue("@dateTime", row.Cells["Date"].Value);
                cmd.Parameters.AddWithValue("@clientCode", row.Cells["Client Code"].Value);
                cmd.Parameters.AddWithValue("@clientName", row.Cells["Client Name"].Value);
                cmd.Parameters.AddWithValue("@jobName", row.Cells["Job Name"].Value);
                cmd.Parameters.AddWithValue("@flexQuality", row.Cells["Flex Quality"].Value);
                cmd.Parameters.AddWithValue("@sizeLength", row.Cells["Size Length"].Value);
                cmd.Parameters.AddWithValue("@sizeWidth", row.Cells["Size Width"].Value);
                cmd.Parameters.AddWithValue("@rate", row.Cells["Rate"].Value);
                cmd.CommandText = "INSERT INTO record(invoice, jobOrder, dateTime, clientCode, clientName, jobName, flexQuality, sizeLength, sizeWidth, rate)VALUES(@invoice, @jobOrder, @dateTime, @clientCode, @clientName, @jobName, @flexQuality, @sizeLength, @sizeWidth, @rate)";
                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        MessageBox.Show("Records inserted.");
    }

Collectives™ on Stack Overflow

How to insert datagridview record into mysql database using foreach

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related