It only imports 1 row in mysql database

Question

I have a problem I'm trying to import an excel file to mysql database. It's working but it only imports 1 row.

OleDbConnection olconn = new OleDbConnection(conStr);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * From [" + comboBox1.Text + "]", olconn);
dt = new DataTable();
DataSet ds = new DataSet();
myDataAdapter.Fill(ds);
olconn.Close();
dataGridView1.DataSource = dt;
//gridControl1.DataSource = dt;
connExcel.Close();


for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    MySqlConnection con = new MySqlConnection(connStr);
    string query = "Insert into excel(IDExcel, studentnumber, fullname, course, yearandsection) Values('" +
    ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" + ds.Tables[0].Rows[i][2].ToString() + "','" + ds.Tables[0].Rows[i][3].ToString() + "','" + ds.Tables[0].Rows[i][4].ToString() + "')";
    con.Open();
    MySqlCommand cmd = new MySqlCommand(query, con);
    cmd.ExecuteNonQuery();
    con.Close();
}
MessageBox.Show("Done Importing!", "Congratulations!", MessageBoxButtons.OK,MessageBoxIcon.Information);

Can i suggest you to put `con.Open();` before loop and `con.Close();` after loop. — Farhad, Sep 21 '17 at 17:56
Its really hard to tell without debugging to tell the truth. I would say, like others have suggested, put in a break point and check to see if the datatable has more then one row. Depending on how the excel sheet is formatted, the data could be coming in differently. — Hack, Sep 21 '17 at 18:04
You dont need any of that code. The datatable you import can be saved all at once to a different datastore — Ňɏssa Pøngjǣrdenlarp, Sep 21 '17 at 19:00

Sam Marion · Answer 1 · 2017-09-21T18:01:44.423

0

First of all your query is open to sql injection I would look into parameterized queries and fix that. https://www.dotnetperls.com/sqlparameter

You should open the connection, and only use one insert statement much like how you would do it on MySQL. Concat all the values and do everything once. Close the connection when you are done. It is very inefficient the way you have it now.

Look to this answer for more help. Most efficient way to insert Rows into MySQL Database

edited Sep 21 '17 at 18:01

answered Sep 21 '17 at 17:52

Sam Marion

690
1
4
17

while the code is a little clunky, he appears to be fetching the data for each pass of the for loop, since he's using the i index... – Daniel Sampson Sep 21 '17 at 17:58
1

Oh weird, I didn't see the loop maybe it was added when edited? I'll edit my answer thanks. – Sam Marion Sep 21 '17 at 17:59

score 0 · Answer 2 · answered Sep 21 '17 at 17:56

dataGridView1.DataSource = dt;

Well one thing I see is the above line doesn't mean anything because dt is not linked to the dataset... you could initialize dt or just use ds.Tables[0].

Could you also put a breakout somewhere in your code, and then execute the following line in an Debug -> Windows -> Immediate Window: ?ds.Tables[0].Rows.Count

Just to verify that you actually are getting back multiple records

It only imports 1 row in mysql database

2 Answers2