C# FormatException while Passing DataTable as Parameter

Question

I am trying to read Excel file and I want to send the records to the stored procedure for Insertion into a table, taking DataTable as Parameter. This is my StoredProcedure

    create PROCEDURE [dbo].[spInsertInvoice]
    @tblInvoice InvoiceType READONLY
    AS
    BEGIN
          SET NOCOUNT ON;
          INSERT INTO Excel
          SELECT Template, Cust_Name, Invoice_No, InvoiceDate FROM @tblInvoice
    END

And here is the c# code :

if (FileUpload1.HasFile)
        {
            string path = string.Concat((Server.MapPath("~/temp/" + FileUpload1.FileName)));
            FileUpload1.PostedFile.SaveAs(path);
            OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + path + ";Extended Properties = Excel 12.0;");
            OleDbCommand Olecmd = new OleDbCommand("select * from [Sheet1$]", oleCon);
            OleDbDataAdapter dtap = new OleDbDataAdapter(Olecmd);
            DataSet ds = new DataSet();
            dtap.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[4] { new DataColumn("Template", typeof(string)),
                    new DataColumn("Cust_Name", typeof(string)),
                    new DataColumn("Invoice_No", typeof(int)),
                    new DataColumn("InvoiceDate",typeof(DateTime)) });
            foreach (GridViewRow row in GridView1.Rows)
            {
                string Template = row.Cells[1].Text;
                string Cust_Name = row.Cells[2].Text;
                int Invoice_No = int.Parse(row.Cells[3].Text);
                DateTime InvoiceDate = DateTime.ParseExact(row.Cells[4].Text, "yyyyMMdd", CultureInfo.InvariantCulture);
                dt.Rows.Add(Template, Cust_Name, Invoice_No);



            }

            if (dt.Rows.Count > 0)
            {
                string consString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlCommand cmd = new SqlCommand("spInsertInvoice"))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@tblInvoice", dt);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }
            Response.Write("<script>alert('Inserted')</script>");
        }

When I am running my code I get FormatException

String was not recognized as a valid DateTime

 DateTime InvoiceDate = DateTime.ParseExact(row.Cells[3].Text, "yyyyMMdd", CultureInfo.InvariantCulture);

What am I doing wrong?

Answer 1

The format part of the call to ParseExact is the format you are trying to parse from, so if the date format of the string in excel is 5-Jun-15 you should be using

DateTime InvoiceDate = DateTime.ParseExact(row.Cells[3].Text, "d-MMM-yy", CultureInfo.InvariantCulture);