Importing data from excel to database mysql - how to insert cmd datetime parameter in format yyyy-MM-dd HH:mm:ss

Question

0.00/5 (No votes)

See more:

, +

This time i've created like "Sheet5" in excel Workbook:

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

When I load data from this excel to datagridview i get this view (in datagridview of course):

+--------+---------+---------+-------------+---------+----------+-----------+--------------------------+----------------+
    |FNAME |LNAME  |ORDER_DESC|ORDER_NUMBER|MODULE NAME|PROJECT NAME|AMOUNT_OF_PRODUCTS|   BEGIN_DATE     |    END_DATE     |
    +--------+---------+---------+---------+------------+-----------+----------+--------------------------------------------+
    |Jonhy | Blank | make sth |    e/1     |S-BOX      |    box     |       20         | 22.05.2019 06:32 | 22.05.2019 15:13|
    +--------+---------+---------+----------------------+-----------+----------+--------------------------------------------+

Then I'd like to insert to MYSQL database:

for (int i = 0; i < datagrdStatus_order.Rows.Count; i++)
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO project1.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 project1.workers INNER JOIN project1.orders INNER JOIN project1.modules INNER JOIN project1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND orders.ORDER_DESC = @ORDER_DESC AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME", connection);
cmd.Parameters.Add("@FNAME", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[0].Value.ToString();
cmd.Parameters.Add("@LNAME", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[1].Value.ToString();
cmd.Parameters.Add("@ORDER_DESC", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[2].Value.ToString();
cmd.Parameters.Add("@ORDER_NUMBER", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[3].Value.ToString();
cmd.Parameters.Add("@MODULES_NAME", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[4].Value.ToString();
cmd.Parameters.Add("@PROJECT_NAME", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[5].Value.ToString();
cmd.Parameters.Add("@AMOUNT_OF_PRODUCTS", MySqlDbType.Int64).Value = datagrdStatus_order.Rows[i].Cells[6].Value.ToString();
cmd.Parameters.AddWithValue("@BEGIN_DATE", MySqlDbType.DateTime).Value = datagrdStatus_order.Rows[i].Cells[7].Value.ToString();
cmd.Parameters.AddWithValue("@END_DATE", MySqlDbType.DateTime).Value = datagrdStatus_order.Rows[i].Cells[8].Value.ToString();
cmd.ExecuteNonQuery();
}

That above code insert most of data to database but by as for as BEGIN_DATE AND END_DATE are concerned i get error exception that these values are inserted as NULL (in sense is like 0000-00-00 00:00:00).

That trouble is here:

cmd.Parameters.AddWithValue("@BEGIN_DATE", MySqlDbType.DateTime).Value = datagrdStatus_order.Rows[i].Cells[7].Value.ToString();
cmd.Parameters.AddWithValue("@END_DATE", MySqlDbType.DateTime).Value = datagrdStatus_order.Rows[i].Cells[8].Value.ToString();

I don't know what can i do? Can i count someone for any help? Thx in advance.

There is my code:

C#

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 [Sheet5$]", oledbconn);
                    oledbconn.Open();

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

                    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 INTO project1.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 project1.workers INNER JOIN project1.orders INNER JOIN project1.modules INNER JOIN project1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND orders.ORDER_DESC = @ORDER_DESC AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME", connection);
                        cmd.Parameters.Add("@FNAME", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[0].Value.ToString();
                        cmd.Parameters.Add("@LNAME", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[1].Value.ToString();
                        cmd.Parameters.Add("@ORDER_DESC", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[2].Value.ToString();
                        cmd.Parameters.Add("@ORDER_NUMBER", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[3].Value.ToString();
                        cmd.Parameters.Add("@MODULES_NAME", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[4].Value.ToString();
                        cmd.Parameters.Add("@PROJECT_NAME", MySqlDbType.VarChar).Value = datagrdStatus_order.Rows[i].Cells[5].Value.ToString();
                        cmd.Parameters.Add("@AMOUNT_OF_PRODUCTS", MySqlDbType.Int64).Value = datagrdStatus_order.Rows[i].Cells[6].Value.ToString();
                        cmd.Parameters.AddWithValue("@BEGIN_DATE", MySqlDbType.DateTime).Value = datagrdStatus_order.Rows[i].Cells[7].Value.ToString();
                        cmd.Parameters.AddWithValue("@END_DATE", MySqlDbType.DateTime).Value = datagrdStatus_order.Rows[i].Cells[8].Value.ToString();
                        cmd.ExecuteNonQuery();
                    }
                    connection.Close();
                    MessageBox.Show("The data are imported correctly");

                    loaddataalldatagridview();
                }
            }
        }

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

What I have tried:

I've tried in 2 methods:

1) Trying parse format:

cmd.Parameters.AddWithValue("@BEGIN_DATE", MySqlDbType.DateTime).Value = datagrdStatus_order.Rows[i].Cells[7].Value.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.AddWithValue("@END_DATE", MySqlDbType.DateTime).Value = datagrdStatus_order.Rows[i].Cells[8].Value.ToString("yyyy-MM-dd HH:mm:ss");

But i have an error:

Quote:
No overloading of the "ToString" method takes the following number of arguments: "1"

2) Assign 2 last line codes in "for datagridview" loop like that that:

//if (datagrdStatus_zl.Rows[i].Cells[7].Value is DateTime)
//   cmd.Parameters.AddWithValue("@DATA_ROZPOCZECIA", MySqlDbType.DateTime).Value = (DateTime)datagrdStatus_zl.Rows[i].Cells[7].Value;
//if (datagrdStatus_zl.Rows[i].Cells[8].Value is DateTime)
//    cmd.Parameters.AddWithValue("@DATA_ZAKONCZENIA", MySqlDbType.DateTime).Value = (DateTime)datagrdStatus_zl.Rows[i].Cells[8].Value;

After running application i get an error too:

Quote:
"Incorrect projection from the 'DateTime' element to the 'Int32' element.

Posted 22-May-19 20:42pm

Member 10696161

Updated 22-May-19 21:20pm

Add a Solution

2 solutions

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)

lmoelleb · Answer 1 · 2019-05-22T21:20:00

yyyy-MM-dd HH:mm:ss is a format "for human consumption". It is not a good format for a database, nor a good format for a program. And it is certainly not a good format for a program to communicate with a database. It isn't even a good format for Excel, so most likely this is not what is stored in your excel sheet - I expect it will store a date/time, and use the current settings of the user (or a setting on the cells formatting) to convert this value to a string representation when rendering the value in the cell.

You should go through these steps one by one. Do not move from one step to the next before you are certain it is completed:

Make sure your database is set to store a date (or datetime field)

Make sure you read a DateTime or DateTimeOffset out of Excel, and not a string (check with the debugger). If you can't make this work, google that specific problem: Read datetime from Excel, not how to store it in your database. Always google how to solve one step in your problem, not the entire solution you are trying to build.

Check the timezone is as expected. DateTime will have either your system local or UTC time. DateTimeOffset can specify the exact offset from UTC, and hence represent any local time (and UTC of course, that is just offset 0).

Make sure you add the DateTime or DateTimeOffset to your SQL command parameters. Do not call ToString on it. The conversion between the .NET native date time format and the database date time format will be done for you automatically.

Check the data in the database. Remember it will not be stored in yyyy-MM-dd HH:mm:ss format, so if it is displayed in a different way, it does not mean it is stored wrong. It just means whatever software you use to check the values choose to display it in a different format. :)

If you get stuck, then ask for help about the specific step.

mohamedenew · Answer 2 · 2019-05-22T21:14:00

Solution 1

//Try to use method like that ...
cmd.Parameters.AddWithValue("@DATA_ROZPOCZECIA", (DateTime)datagrdStatus_zl.Rows[i].Cells[7].Value);

// otherwise i can help u on your pc ..

Posted 22-May-19 21:14pm

mohamedenew

Comments

Member 10696161 23-May-19 4:09am

I have tested this line and inserted all of them but after that i have calling na exception:

System.NullReferenceException: "The object reference has not been set to the instance of the object."

and it shows at the code line:

cmd.Parameters.Add("@FNAME", MySqlDbType.VarChar).Value = datagrdStatus_zl.Rows[i].Cells[0].Value.ToString();

Can you help how to solve it next? I have to change from Parameters.Add to Parameters.AddWithValue?

mohamedenew 27-May-19 6:28am

May be datagrdStatus_zl.Rows[i].Cells[0].Value does not have a value
datagrdStatus_zl.Rows[i].Cells[0].Value!=null ?datagrdStatus_zl.Rows[i].Cells[0].Value.ToString():"";