example csv data:
datetime Miliseconds MachineAutoStartStop Pressure
25/10/2022 0:00 655 1 0.382796
25/10/2022 0:00 899 1 0.382796
25/10/2022 10:31 37 1 0.382796
25/10/2022 10:31 38 1 0.382796
25/10/2022 10:31 787 1 0.382796
error:error:System.InvalidCastException: 'Specified cast is not valid.'
Code
private void btnbrowse_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = ".csv";
ofd.Filter = "Comma Separated (*.csv)|*.csv";
ofd.ShowDialog();
txtfilename.Text = ofd.FileName;
DataTable dt = new DataTable();
dataGridView1.DataSource = dt.DefaultView;
{
}
}
private void btnclose_Click(object sender, EventArgs e)
{
this.Close();
}
private void btnimport_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
DataTable dt = GetDataFromFile();
if (dt == null) return;
SaveImportDataToDatabase(dt);
MessageBox.Show("Data Import success!");
txtfilename.Text = string.Empty;
Cursor = Cursors.Default;
}
private DataTable GetDataFromFile()
{
DataTable dt = new DataTable();
try
{
using (StreamReader sr = new StreamReader(txtfilename.Text))
{
string header = sr.ReadLine();
if (string.IsNullOrEmpty(header))
{
MessageBox.Show("no file data");
return null;
}
string[] headerColumns = header.Split(',');
foreach (string headerColumn in headerColumns)
{
dt.Columns.Add(headerColumn);
}
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (string.IsNullOrEmpty(line)) continue;
string[] fields = line.Split(',');
DataRow importedRow = dt.NewRow();
for (int i = 0; i < fields.Count(); i++)
{
importedRow[i] = fields[i];
}
dt.Rows.Add(importedRow);
}
}
}
catch (Exception e)
{
Console.WriteLine("the file could not be read:");
Console.WriteLine(e.Message);
}
return dt;
}
private void SaveImportDataToDatabase(DataTable S2P5)
{
using (SqlConnection conn =New SqlConnection(@"Data Source=BL03\SQLEXPRESS; Initial Catalog=HDB; User Id=sa; Password=00"))
{
conn.Open();
foreach (DataRow importRow in S2P5.Rows)
{
SqlCommand cmd = new SqlCommand ("INSERT INTO S2P5 (DateTime, Miliseconds, MachineAutoStartStop, Pressure)" +
"VALUES (@DateTime, @Miliseconds, @MachineAutoStartStop, @Pressure)", conn);
cmd.Parameters.AddWithValue("@DateTime", (DateTime)importRow["DateTime"]); //error line
cmd.Parameters.AddWithValue("@Miliseconds", importRow["Miliseconds"]);
cmd.Parameters.AddWithValue("@MachineAutoStartStop", importRow["MachineAutoStartStop"]);
cmd.Parameters.AddWithValue("@Pressure", importRow["Pressure"]);
cmd.ExecuteNonQuery();
}
}