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:
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
.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. :)