How to insert every cell in columns with datetime from ms excel to mysql database?

Question

0.00/5 (No votes)

See more:

, +

I'm trying to write code in this language which imports data from MS excel (Office 365) to datagridview then to mysql database. In this case i have a table which i do it the sheet:

https://i.stack.imgur.com/AD96e.png

When i import the data to datagridview then i try to insert the data into mysql database. All code is in the below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Data.OleDb;
using System.IO;
using System.Configuration;

namespace ControlDataBase
{
    public partial class New_Tables : Form
    {
        public New_Tables()
        {
            InitializeComponent();
        }
        Form1 frm1 = (Form1)Application.OpenForms["Form1"];

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void ImportData_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Files|*.xlsx;*.xlsm;*.xlsb;*.xltx;*.xltm;*.xls;*.xlt;*.xls;*.xml;*.xml;*.xlam;*.xla;*.xlw;*.xlr;", ValidateNames = true })
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    FileInfo fi = new FileInfo(ofd.FileName);
                    string FileName1 = ofd.FileName;

                    string excel = fi.FullName;

                    if (ofd.FileName.EndsWith(".xlsx"))
                    {
                        StrConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=\"Excel 12.0;\"";
                    }

                    if (ofd.FileName.EndsWith(".xls"))
                    {
                        StrConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excel + ";Extended Properties=\"Excel 1.0;HDR=Yes;IMEX=1\"";
                    }
                    OleDbConnection oledbconn = new OleDbConnection(StrConn);

                    OleDbDataAdapter dta5 = new OleDbDataAdapter("SELECT * FROM [Order_status$]", oledbconn);
                    oledbconn.Open();

                    DataSet dsole5 = new DataSet();
                    dta5.Fill(dsole5, "Order_status$");
                    datagrdStatus_order.DataSource = dsole5.Tables["Order_status$"];

                    oledbconn.Close();

                    MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=");
                    connection.Open();

                    for (int i = 0; i < datagrdStatus_order.Rows.Count; i++)
                    {
                        MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO try1.order_status(ID_WORKER, ID_ORDER, ID_MODULE, ID_PROJECT, AMOUNT_OF_PRODUCTS, BEGIN_DATE, END_DATE) SELECT workers.ID_WORKER, orders.ID_ORDER, module.ID_MODULE, projects.ID, @AMOUNT_OF_PRODUCTS, @BEGIN_DATE, @END_DATE FROM try1.workers INNER JOIN try1.orders INNER JOIN try1.modules INNER JOIN try1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND workers.ID_WORKER = @ID_WORKER AND orders.ORDER_DESC = @ORDER_DESC AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME", connection);

                    DateTime begin_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[8].Value);
                    DateTime end_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[9].Value);

                        cmd.Parameters.AddWithValue("@ID_WORKER", datagrdStatus_order.Rows[i].Cells[0].Value);
                        cmd.Parameters.AddWithValue("@FNAME", datagrdStatus_order.Rows[i].Cells[1].Value);
                        cmd.Parameters.AddWithValue("@LNAME", datagrdStatus_order.Rows[i].Cells[2].Value);
                        cmd.Parameters.AddWithValue("@ORDER_DESC", datagrdStatus_order.Rows[i].Cells[3].Value);
                        cmd.Parameters.AddWithValue("@NUMBER_ORDER", datagrdStatus_order.Rows[i].Cells[4].Value);
                        cmd.Parameters.AddWithValue("@MODULES_NAME", datagrdStatus_order.Rows[i].Cells[5].Value);
                        cmd.Parameters.AddWithValue("@PROJECT_NAME", datagrdStatus_order.Rows[i].Cells[6].Value);
                        cmd.Parameters.AddWithValue("@AMOUNT_OF_PRODUCTS", datagrdStatus_order.Rows[i].Cells[7].Value);
                        cmd.Parameters.AddWithValue("@BEGIN_DATE", begin_date);
                        cmd.Parameters.AddWithValue("@END_DATE", end_date);
                        cmd.ExecuteNonQuery();
                    }
                    connection.Close();
                    MessageBox.Show("The data are imported correctly");

                    loaddataalldatagridview();
                }
            }
        }

        private void loaddataalldatagridview()
        {
            frm1.loaddata5();
        }
    }
}

When i execute that code the i get the error at the line of code:

C#

DateTime begin_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[8].Value);

which shows:

Quote:
System.InvalidCastException: "You cannot cast from the DBNull element to other types."

But it inserted most of the imported data but not all of them. I wanna import all of the data.

What I have tried:

1) I'm added to datetime objects just

C#

.ToString();

for (int i = 0; i < datagrdStatus_order.Rows.Count; i++)
            {
                MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO try1.order_status(ID_WORKER, ID_ORDER, ID_MODULE, ID_PROJECT, AMOUNT_OF_PRODUCTS, BEGIN_DATE, END_DATE) SELECT workers.ID_WORKER, orders.ID_ORDER, module.ID_MODULE, projects.ID, @AMOUNT_OF_PRODUCTS, @BEGIN_DATE, @END_DATE FROM try1.workers INNER JOIN try1.orders INNER JOIN try1.modules INNER JOIN try1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND workers.ID_WORKER = @ID_WORKER AND orders.ORDER_DESC = @ORDER_DESC AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME", connection);

                DateTime begin_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[8].Value.ToString());
                DateTime end_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[9].Value.ToString());

                cmd.Parameters.AddWithValue("@ID_WORKER", datagrdStatus_order.Rows[i].Cells[0].Value);
                cmd.Parameters.AddWithValue("@FNAME", datagrdStatus_order.Rows[i].Cells[1].Value);
                cmd.Parameters.AddWithValue("@LNAME", datagrdStatus_order.Rows[i].Cells[2].Value);
                cmd.Parameters.AddWithValue("@ORDER_DESC", datagrdStatus_order.Rows[i].Cells[3].Value);
                cmd.Parameters.AddWithValue("@NUMBER_ORDER", datagrdStatus_order.Rows[i].Cells[4].Value);
                cmd.Parameters.AddWithValue("@MODULES_NAME", datagrdStatus_order.Rows[i].Cells[5].Value);
                cmd.Parameters.AddWithValue("@PROJECT_NAME", datagrdStatus_order.Rows[i].Cells[6].Value);
                cmd.Parameters.AddWithValue("@AMOUNT_OF_PRODUCTS", datagrdStatus_order.Rows[i].Cells[7].Value);
                cmd.Parameters.AddWithValue("@BEGIN_DATE", begin_date);
                cmd.Parameters.AddWithValue("@END_DATE", end_date);
                cmd.ExecuteNonQuery();
            }

But it shows:

Quote:
System.FormatException: "The string was not recognized as a valid DateTime."

2) I was trying it by adding string to objects then converting to datetime:

for (int i = 0; i < datagrdStatus_order.Rows.Count; i++)
            {
                MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO try1.order_status(ID_WORKER, ID_ORDER, ID_MODULE, ID_PROJECT, AMOUNT_OF_PRODUCTS, BEGIN_DATE, END_DATE) SELECT workers.ID_WORKER, orders.ID_ORDER, module.ID_MODULE, projects.ID, @AMOUNT_OF_PRODUCTS, @BEGIN_DATE, @END_DATE FROM try1.workers INNER JOIN try1.orders INNER JOIN try1.modules INNER JOIN try1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND workers.ID_WORKER = @ID_WORKER AND orders.ORDER_DESC = @ORDER_DESC AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME", connection);

                DateTime begin_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[8].Value);
                string MysqlFormatDate = begin_date.ToString("yyyy-MM-dd HH:mm:ss");
                DateTime begin_date2 = Convert.ToDateTime(MysqlFormatDate);

                DateTime end_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[9].Value);
                string MysqlFormatDate2 = end_date.ToString("yyyy-MM-dd HH:mm:ss");
                DateTime end_date2 = Convert.ToDateTime(MysqlFormatDate2);

                cmd.Parameters.AddWithValue("@ID_WORKER", datagrdStatus_order.Rows[i].Cells[0].Value);
                cmd.Parameters.AddWithValue("@FNAME", datagrdStatus_order.Rows[i].Cells[1].Value);
                cmd.Parameters.AddWithValue("@LNAME", datagrdStatus_order.Rows[i].Cells[2].Value);
                cmd.Parameters.AddWithValue("@ORDER_DESC", datagrdStatus_order.Rows[i].Cells[3].Value);
                cmd.Parameters.AddWithValue("@NUMBER_ORDER", datagrdStatus_order.Rows[i].Cells[4].Value);
                cmd.Parameters.AddWithValue("@MODULES_NAME", datagrdStatus_order.Rows[i].Cells[5].Value);
                cmd.Parameters.AddWithValue("@PROJECT_NAME", datagrdStatus_order.Rows[i].Cells[6].Value);
                cmd.Parameters.AddWithValue("@AMOUNT_OF_PRODUCTS", datagrdStatus_order.Rows[i].Cells[7].Value);
                cmd.Parameters.AddWithValue("@BEGIN_DATE", begin_date2);
                cmd.Parameters.AddWithValue("@END_DATE", end_date2);
                cmd.ExecuteNonQuery();
            }

But it didn't work too (it shew the error like in 1-st try).

3) I declared string object and convert to DateTime:

for (int i = 0; i < datagrdStatus_order.Rows.Count; i++)
            {
                MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO try1.order_status(ID_WORKER, ID_ORDER, ID_MODULE, ID_PROJECT, AMOUNT_OF_PRODUCTS, BEGIN_DATE, END_DATE) SELECT workers.ID_WORKER, orders.ID_ORDER, module.ID_MODULE, projects.ID, @AMOUNT_OF_PRODUCTS, @BEGIN_DATE, @END_DATE FROM try1.workers INNER JOIN try1.orders INNER JOIN try1.modules INNER JOIN try1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND workers.ID_WORKER = @ID_WORKER AND orders.ORDER_DESC = @ORDER_DESC AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME", connection);

                string begin_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[8].Value).ToString("yyyy-MM-dd HH:mm:ss);
                string end_date = Convert.ToDateTime(datagrdStatus_order.Rows[i].Cells[9].Value).ToString("yyyy-MM-dd HH:mm:ss");

                cmd.Parameters.AddWithValue("@ID_WORKER", datagrdStatus_order.Rows[i].Cells[0].Value);
                cmd.Parameters.AddWithValue("@FNAME", datagrdStatus_order.Rows[i].Cells[1].Value);
                cmd.Parameters.AddWithValue("@LNAME", datagrdStatus_order.Rows[i].Cells[2].Value);
                cmd.Parameters.AddWithValue("@ORDER_DESC", datagrdStatus_order.Rows[i].Cells[3].Value);
                cmd.Parameters.AddWithValue("@NUMBER_ORDER", datagrdStatus_order.Rows[i].Cells[4].Value);
                cmd.Parameters.AddWithValue("@MODULES_NAME", datagrdStatus_order.Rows[i].Cells[5].Value);
                cmd.Parameters.AddWithValue("@PROJECT_NAME", datagrdStatus_order.Rows[i].Cells[6].Value);
                cmd.Parameters.AddWithValue("@AMOUNT_OF_PRODUCTS", datagrdStatus_order.Rows[i].Cells[7].Value);
                cmd.Parameters.AddWithValue("@BEGIN_DATE", begin_date);
                cmd.Parameters.AddWithValue("@END_DATE", end_date);
                cmd.ExecuteNonQuery();
            }

But then it shew the error:

Quote:
System.InvalidCastException: "You cannot cast from the DBNull element to other types."

I have no clue and idea how to solve it. I was searching for solution i haven't found it? Can someone please explain how to solve it? Any ideas? Thank you for any help and advice. :)

Posted 11-Jun-19 19:34pm

Member 10696161

Updated 14-Jul-19 22:20pm

Add a Solution

2 solutions

Solution 1

DateTime begin_date;

bool ok = DateTime.TryParse(datagrdStatus_order.Rows[i].Cells[8].Value, out begin_date);

if (!ok){
   begin_date = ...
}

DateTime.TryParse Method (System) | Microsoft Docs[^]

Posted 11-Jun-19 21:41pm

Gerry Schmitz

Comments

Member 10696161 12-Jun-19 4:09am

I've just written the line of code:

bool ok = DateTime.TryParse(datagrdStatus_order.Rows[i].Cells[8].Value, out begin_date);

and it "shouts" an error : "Argument "1": can not be converted from "object" to "string" ".

How to deal with that?

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 10696161 · Accepted Answer · 2019-07-14T22:20:00

Allright i found that solution that i use in foreach loop:

string constring = "datasource = localhost; port = 3306; username = root; password = ";
 string query5 = "INSERT IGNORE INTO try1.order_status(ID_WORKER, ID_ORDER, ID_MODULE, ID_PROJECT, AMOUNT_OF_PRODUCTS, BEGIN_DATE, END_DATE) SELECT workers.ID_WORKER, orders.ID_ORDER, module.ID_MODULE, projects.ID, @AMOUNT_OF_PRODUCTS, @BEGIN_DATE, @END_DATE FROM try1.workers INNER JOIN try1.orders INNER JOIN try1.modules INNER JOIN try1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND workers.ID_WORKER = @ID_WORKER AND orders.DESC_ORDER = @DESC_ORDER AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME";

 using (MySqlConnection con = new MySqlConnection(constring))
 {
     using (MySqlCommand cmd = new MySqlCommand(query5, con))
     {
         cmd.Parameters.Add("@ID_WORKER", MySqlDbType.Int32);
         cmd.Parameters.Add("@FNAME", MySqlDbType.VarChar);
         cmd.Parameters.Add("@LNAME", MySqlDbType.VarChar);
         cmd.Parameters.Add("@DESC_ORDER", MySqlDbType.VarChar);
         cmd.Parameters.Add("@ORDER_NUMBER", MySqlDbType.VarChar);
         cmd.Parameters.Add("@MODULES_NAME", MySqlDbType.VarChar);
         cmd.Parameters.Add("@PROJECT_NAME", MySqlDbType.VarChar);
         cmd.Parameters.Add("@AMOUNT_OF_PRODUCTS", MySqlDbType.Int32);
         cmd.Parameters.Add("@BEGIN_DATE", MySqlDbType.DateTime);
         cmd.Parameters.Add("@END_DATE", MySqlDbType.DateTime);
         con.Open();

         foreach (DataGridViewRow row in datagrdStatus_order.Rows)
         {
             cmd.Parameters.AddWithValue("@ID_WORKER", row.Cells["ID_WORKER"].Value);
             cmd.Parameters.AddWithValue("@FNAME", row.Cells["FNAME"].Value);
             cmd.Parameters.AddWithValue("@LNAME", row.Cells["LNAME"].Value);
             cmd.Parameters.AddWithValue("@DESC_ORDER", row.Cells["DESC_ORDER"].Value);
             cmd.Parameters.AddWithValue("@ORDER_NUMBER", row.Cells["ORDER_NUMBER"].Value);
             cmd.Parameters.AddWithValue("@MODULES_NAME", row.Cells["NAME"].Value);
             cmd.Parameters.AddWithValue("@PROJECT_NAME", row.Cells["PROJECT_NAME"].Value);
             cmd.Parameters.AddWithValue("@AMOUNT_OF_PRODUCTS", row.Cells["AMOUNT_OF_PRODUCTS"].Value);
             cmd.Parameters.AddWithValue("@BEGIN_DATE", row.Cells["BEGIN_DATE"].Value);
             cmd.Parameters.AddWithValue("@END_DATE", row.Cells["END_DATE"].Value);
             cmd.ExecuteNonQuery();
         }
         con.Close();
     }
 }
 MessageBox.Show("Imported correctly");
 loaddataalldatagridview();