Getting error while import csv file datetime value into SQL server usi

Nov 29 2022 4:14 AM

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();
    }
}

 


Answers (3)