How to add all datagridview row to mysql using C#?

Question

0.00/5 (No votes)

See more:

Hello
I want to import all or multi - select rows in datagridview from excel to mysql.
I divide this task in 3 step
1/ load excel to datagridview
2/ load datagridview to textbox
3/ add textbox value to mysql
It is done. But now How to add all datagridview row to mysql using C#?
Now I want
1/ load excel to datagridview
2/ And then press button and insert all rows in datagridview to mysql (no need load datagridview to textbox)
Please help me. Thanks.
Here is my code for three step:

C#

   private void btn_open_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Excel 2003(*.xls)|*.xls|Excel 2013(*.xlsx)|*.xlsx|ALL Files(*.*)|*.*";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                String picPath = dlg.FileName.ToString();
                txt_path.Text = picPath;
                
            }
        }
 private void btn_show_Click(object sender, EventArgs e)
        {
            string constr = "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=" + txt_path.Text + "; Extended Properties =\"Excel 8.0; HDR = Yes;\";";
            OleDbConnection con = new OleDbConnection(constr);
            OleDbDataAdapter sda = new OleDbDataAdapter("Select* From["+txt_sheet.Text+"$]",con);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
        }
 private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
   {
   DataGridViewCell cell = null;
            foreach (DataGridViewCell selectedCell in dataGridView1.SelectedCells)
            {
                cell = selectedCell;
                break;
            }
            if (cell != null)
            {
                    DataGridViewRow row = cell.OwningRow;
                    txt_stt.Text = row.Cells["STT"].Value.ToString();
                    txt_ngay_nhap.Text = row.Cells["Ngày Nhập"].Value.ToString();
                    txt_ten_tb.Text = row.Cells["Tên thiết bị"].Value.ToString();
                    txt_nha_sx.Text = row.Cells["Nhà sản xuất"].Value.ToString();
                    txt_donvi.Text = row.Cells["Đơn vị"].Value.ToString();
                    txt_soluong.Text = row.Cells["Số lượng"].Value.ToString();
                    txt_nhan_tu.Text = row.Cells["Nhận từ"].Value.ToString();
                    txt_chip_ID.Text = row.Cells["Chip ID"].Value.ToString();
                    txt_mac.Text = row.Cells["MAC address"].Value.ToString();
                    txt_sn.Text = row.Cells["SN"].Value.ToString();
                    txt_note.Text = row.Cells["Ghi chú"].Value.ToString();
            }
   }
 private void btn_add_Click(object sender, EventArgs e)
    {
      // insert to mysql
.......
    }

Posted 9-Aug-15 0:15am

Member 10390715

Add a Solution

Comments

[no name] 10-Aug-15 11:46am

You have assigned the "dt" object to gridview control on btn_show_Click but you did not bind it. Use dataGridView1.DataBind() to load contents of dt in grid control.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 10390715 · Answer 1 · 2015-08-10T22:25:00

Thank you, I have solved my problem

C#

private void btn_all_Click(object sender, EventArgs e)
        {
            try
            {
                connect_data();
                byte[] imageBt = null;

                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    string STT = dataGridView1.Rows[i].Cells["STT"].Value.ToString();
                    string Ngay_nhap = dataGridView1.Rows[i].Cells["Ngày nhập"].Value.ToString();
                    string Ten_thiet_bi = dataGridView1.Rows[i].Cells["Tên thiết bị"].Value.ToString();
                    string Nha_san_xuat = dataGridView1.Rows[i].Cells["Nhà sản xuất"].Value.ToString();
                    string Don_vi = dataGridView1.Rows[i].Cells["Đơn vị"].Value.ToString();
                    string So_luong = dataGridView1.Rows[i].Cells["Số lượng"].Value.ToString();
                    string Nhan_tu = dataGridView1.Rows[i].Cells["Nhận từ"].Value.ToString();
                    string Chip_ID = dataGridView1.Rows[i].Cells["Chip ID"].Value.ToString();
                    string MAC_address = dataGridView1.Rows[i].Cells["MAC address"].Value.ToString();
                    string SN = dataGridView1.Rows[i].Cells["SN"].Value.ToString();
                    string Ghi_chu = dataGridView1.Rows[i].Cells["Ghi chú"].Value.ToString();
                    string Ngay_xuat = "";
                    string Ngay_nhan_lai = "";
                    string Query = "INSERT into nhap_2015 (STT,Ngay_nhap,Ten_thiet_bi,Nha_san_xuat,Don_vi,So_luong,Nhan_tu,Chip_ID,MAC_address,SN,Ghi_chu,Hinh,Ngay_xuat,Ngay_nhan_lai) values ('" + STT + "','" + Ngay_nhap + "','" + Ten_thiet_bi + "','" + Nha_san_xuat + "','" + Don_vi + "','" + So_luong + "','" + Nhan_tu + "','" + Chip_ID + "','" + MAC_address + "','" + SN + "','" + Ghi_chu + "',@hinh,'" + Ngay_xuat + "','" + Ngay_nhan_lai + "');";
                    MySqlCommand cmdDataBase = new MySqlCommand(Query, conDatabase);
                    MySqlDataReader myReader;
                    try
                    {
                        conDatabase.Open();
                        cmdDataBase.Parameters.Add(new MySqlParameter("@hinh", imageBt));
                        myReader = cmdDataBase.ExecuteReader();
                        //MessageBox.Show("SAVE");
                        while (myReader.Read())
                        {
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    conDatabase.Close();
                }
                MessageBox.Show("ADD COMPLETED");
            }
            catch
            {
                MessageBox.Show("Error!!!!!");
            }
        }