Click here to Skip to main content
14,599,518 members
Rate this:
Please Sign up or sign in to vote.
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:

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
Updated 22-May-19 21:20pm
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
   
v2
Rate this:
Please Sign up or sign in to vote.

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 ..
   
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():"";

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100