How Do I Insert A Datatable Contents Into A Table In Mysql Database

Question

4.00/5 (1 vote)

See more:

i have imported an excel document through a fileupload control and bound it to a grid view in asp.net but i want to upload the datagrid's content into a table in a mysql database. thats where it gets tricky for me. here is my code

C#

protected void upload_Click(object sender, EventArgs e)
{
    string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    string fileLocation = Server.MapPath("~/App_Data/" + fileName);
    FileUpload1.SaveAs(fileLocation);
    if (FileUpload1.HasFile)
    {
        try
        {
            string connectionString ="";
            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                    fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; 
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
                   fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            } 
            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dtExcelRecords); 
            excel.DataSource = dtExcelRecords;
            excel.DataBind();

            // this is as far as it goes. inserting into a mysql database with the lines of code below doesn't work.

            MySqlConnection conn = new MySqlConnection(connection);
            MySqlCommand com = new MySqlCommand("insert into pla20132014.admissions (jambNo, Names, state, lga, course, department) values (@jambno, @name, @state, @lga, @course, @dept)", conn);
            conn.Open();

            com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = excel.Rows[0];
            com.Parameters.Add("@name", MySqlDbType.VarChar).Value = excel.Rows[1];
            com.Parameters.Add("@state", MySqlDbType.VarChar).Value = excel.Rows[2];
            com.Parameters.Add("@lga", MySqlDbType.VarChar).Value = excel.Rows[3];
            com.Parameters.Add("@course", MySqlDbType.VarChar).Value = excel.Rows[4];
            com.Parameters.Add("@dept", MySqlDbType.VarChar).Value = excel.Rows[5];

            com.ExecuteNonQuery();
            conn.Close();
            error1.ForeColor = Color.Green;
            error1.Text = "inserted successfully";
                   
        }
        catch (OleDbException oex)
        {
            error1.Text = oex.Message + "   /n    " + oex.StackTrace;
        }
        catch (OdbcException odex)
        {
            error1.Text = odex.Message + "   /n    " + odex.StackTrace;
        }
        catch (Exception ex)
        {
            error1.Text = ex.Message + "   /n    " + ex.StackTrace;
        }
    }
    else
    {
        error1.Text = "Select a file";
    }
}

Posted 10-Jul-15 19:57pm

EasyHero

Updated 10-Jul-15 20:03pm

Wendelius

v3

Add a Solution

Comments

Wendelius 11-Jul-15 2:05am

Do you get an error message or how the code isn't working?

EasyHero 11-Jul-15 2:25am

no error message, it doesn't insert the row data into the mysql table

3 solutions

Solution 1

Not sure if I read your code correctly, but it looks like you're trying to insert rows to single fields... Have a look at

C#

com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = excel.Rows[0];

If this is the case, you should build a loop which loops through the rows in excel data and for each row you assign the values for the command parameters and execute it.

In other words if you have 5 rows, you should execute the insert statement five times. Also you would set the values of the parameters with code something like

C#

com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = excel.Rows[rowcounter][1];

Posted 10-Jul-15 20:10pm

Wendelius

Updated 10-Jul-15 20:31pm

v2

Comments

EasyHero 11-Jul-15 7:10am

please what is d function of the [rowcounter]?

also, this is what i keep getting in the first row of my database( for each column) the code i use below isn't getting the values of the rows, else it gets these
System.Web.UI.W System.Web.UI.WebControls.Grid System.Web.UI.W System.Web.UI.WebControls System.Web.UI.WebControls.GridViewR System.Web.UI.WebControls.GridViewRow
here is the code i'm using now

MySqlConnection conn = new MySqlConnection(connection);
MySqlCommand com = new MySqlCommand("insert into pla20132014.admissions (jambNo, Names, state, lga, course, department) values (@jambno, @name, @state, @lga, @course, @dept)", conn);
conn.Open();

//int rowcounter = 0;
foreach (TableRow td in excel.Rows)
{
com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = excel.Rows.ToString();
com.Parameters.Add("@name", MySqlDbType.VarChar).Value = excel.Rows.ToString();
com.Parameters.Add("@state", MySqlDbType.VarChar).Value = excel.Rows[2];
com.Parameters.Add("@lga", MySqlDbType.VarChar).Value = excel.Rows[3];
com.Parameters.Add("@course", MySqlDbType.VarChar).Value = excel.Rows[4];
com.Parameters.Add("@dept", MySqlDbType.VarChar).Value = excel.Rows[5];
com.ExecuteNonQuery();
}

conn.Close();

Wendelius 11-Jul-15 9:41am

If you're using foreach loop then you don't use indexer. You just have to define the column index (or name) when getting the value from the data row. So something like

foreach (TableRow td in excel.Rows)
{
  com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = td[0].ToString();
  com.Parameters.Add("@name", MySqlDbType.VarChar).Value = td[1].ToString();
...

EasyHero 11-Jul-15 9:59am

syntax error
cannot apply indexing with [] to an expression of type 'System.Web.UI.WebControls.Gridview'

Wendelius 11-Jul-15 10:27am

I see. The variable named excel points to a gridview not to a data table. Can you use the data table instead? I believe it would be the easiest way. So the loop could look like
foreach (DataRow dr in dtExcelRecords.Rows)
{
com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = dr[0].ToString();
com.Parameters.Add("@name", MySqlDbType.VarChar).Value = dr[1].ToString();

Solution 2

If this is the case, you should build a loop which loops through the rows in excel data and for each row you assign the values for the command parameters and execute it.

In other words if you have 5 rows, you should execute the insert statement five times. Also you would set the values of the parameters with code something like
com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = excel.Rows[0];
and at the place of Index value you should write [1]

do the changes and enjoy

Posted 10-Jul-15 22:39pm

Abhik kashyap singh

Comments

EasyHero 11-Jul-15 7:11am

this is what i keep getting in the first row of my database( for each column) the code i use below isn't getting the values of the rows, else it gets these
System.Web.UI.W, System.Web.UI.WebControls.Grid, System.Web.UI.W System.Web.UI.WebControls, System.Web.UI.WebControls.GridViewR, System.Web.UI.WebControls.GridViewRow
here is the code i'm using now

MySqlConnection conn = new MySqlConnection(connection);
MySqlCommand com = new MySqlCommand("insert into pla20132014.admissions (jambNo, Names, state, lga, course, department) values (@jambno, @name, @state, @lga, @course, @dept)", conn);
conn.Open();

//int rowcounter = 0;
foreach (TableRow td in excel.Rows)
{
com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = excel.Rows.ToString();
com.Parameters.Add("@name", MySqlDbType.VarChar).Value = excel.Rows.ToString();
com.Parameters.Add("@state", MySqlDbType.VarChar).Value = excel.Rows[2];
com.Parameters.Add("@lga", MySqlDbType.VarChar).Value = excel.Rows[3];
com.Parameters.Add("@course", MySqlDbType.VarChar).Value = excel.Rows[4];
com.Parameters.Add("@dept", MySqlDbType.VarChar).Value = excel.Rows[5];
com.ExecuteNonQuery();
}

conn.Close();

is that the way u said i should loop it?

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)

EasyHero · Accepted Answer · 2015-07-12T22:01:00

these finally worked for me. Thanks a lot guys n girls

C#

foreach (GridViewRow dr in excel.Rows)
                    {
                        for (int i = 0; i <= excel.Columns.Count; i++)
                        {
                            try
                            {
                                MySqlConnection conn = new MySqlConnection(connection);
                                MySqlCommand com = new MySqlCommand("insert into pla20132014.admissions (jambNo, Names, state, lga, course, department) values (@jambno, @name, @state, @lga, @course, @dept)", conn);
                                MySqlDataReader read;
                                conn.Open();
                                com.Parameters.Add("@jambno", MySqlDbType.VarChar).Value = dr.Cells[3].Text;
                                com.Parameters.Add("@name", MySqlDbType.VarChar).Value = dr.Cells[0].Text;
                                com.Parameters.Add("@state", MySqlDbType.VarChar).Value = dr.Cells[1].Text;
                                com.Parameters.Add("@lga", MySqlDbType.VarChar).Value = dr.Cells[2].Text;
                                com.Parameters.Add("@course", MySqlDbType.VarChar).Value = dr.Cells[4].Text;
                                com.Parameters.Add("@dept", MySqlDbType.VarChar).Value = dr.Cells[5].Text;

                                read = com.ExecuteReader();
                                read.Close();
                                conn.Close();

                            }
                            catch (MySqlException ex)
                            {

                            }
                        }
                    }

How Do I Insert A Datatable Contents Into A Table In Mysql Database

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us