Unable to update current time in a excel using oledb

Question

I am trying to insert current time to a Time column in the excel using the below code through an oledb connection but when I check the excel the value inserted is in Date format.

Value updated in excel - 1/0/1900 3:54:11 PM

Expected Value - 3:54:11 PM

         string currentTime = DateTime.Now.ToString("hh:mm:ss.fff tt");
         string cmnd1 = "Create Table [" + currentDate + "] (TestCase char(100), ExecutionTime Time, Result char(20))";
         string cmnd2 = "Insert Into [" + currentDate + "] (TestCase, ExecutionTime, Result) values ("+ "'" + tName + "',@dd,'" + result +"')" ;
         using (OleDbConnection conn = new OleDbConnection(ConnectionStringtd))
         {
             OleDbCommand createSheet = new OleDbCommand(cmnd1, conn);
             OleDbCommand insertResult = new OleDbCommand(cmnd2, conn);
             insertResult.Parameters.AddWithValue("@dd", DateTime.Now.TimeOfDay);
             conn.Open();
             try
             {
                 createSheet.ExecuteNonQuery();
             }
             catch(OleDbException) {}
             insertResult.ExecuteNonQuery();
         }

     }

Do not use AddWithValue. The method infers the type of the parameter by itself and a failed guess is very easy with datetime values. Use Add and specify the OleDbType. Try the various options. By the way, why use only one parameter and string concatantion for the other values? — Steve, Jul 11 '15 at 10:38
Also: Empty Try/Catch are an error. If the first command fails the second one has no way to execute, but you don't know because the empty catch swallowed the exception — Steve, Jul 11 '15 at 10:41
@Steve - Thanks for your reply. Actually I will be calling this function several times in a day, so I used the empty catch to make sure no exceptions are thrown when the table already exists. Do you have any alternative for handling this — Ash1994, Jul 11 '15 at 12:08

Soner Gönül · Accepted Answer · 2015-07-11T10:54:32.023

0

AFAIK, when you enter pure time value stored as a datetime with the entered time portion, and a date part will be January 0, 1900 automatically since the days before 1900 are incorrect in Excel.

Instead of that, pass your DateTime.Now directly to parameter and change your column format type to Time with h:mm:ss tt format in your format cells part. By the way, you just paramterized @dd part. Use parameters for the other values that you try to insert. Don't concatenate them.

insertResult.Parameters.AddWithValue("@dd", DateTime.Now);

enter image description here

And don't use AddWithValue anymore. It may generate unexpected and suprising results sometimes. Use Add method overload to specify your parameter type and it's size.

Also use using statement to dispose your commands as you did for the connection.

edited Jul 11 '15 at 10:54

answered Jul 11 '15 at 10:47

Soner Gönül

97,193
102
206
364

Thanks for your valuable comments. I – Ash1994 Jul 11 '15 at 12:10
string currentDate = DateTime.Now.ToString("yyyyMMdd"); string currentTime = DateTime.Now.ToString("h:mm:ss tt"); string cmnd1 = "Create Table [" + currentDate + "] (TestCase char(100), ExecutionTime char(100), Result char(20))"; string cmnd2 = "Insert Into [" + currentDate + "] (TestCase, ExecutionTime, Result) values (@tc,@et,@r)"; – Ash1994 Jul 11 '15 at 12:20
OleDbConnection(ConnectionStringtd)) { conn.Open(); using (OleDbCommand createSheet = new OleDbCommand(cmnd1, conn)) { try { Console.WriteLine(cmnd1); createSheet.ExecuteNonQuery(); } catch(OleDbException) {} } – Ash1994 Jul 11 '15 at 12:20
using (OleDbCommand insertResult = new OleDbCommand(cmnd2, conn)) { insertResult.Parameters.Add("@tc", OleDbType.Char).Value = tName; insertResult.Parameters.Add("@et", OleDbType.Char).Value = currentTime; insertResult.Parameters.Add("@r", OleDbType.Char).Value=result; insertResult.ExecuteNonQuery(); } } – Ash1994 Jul 11 '15 at 12:21
I also have a doubt, while using Parameters.Add function, is there any significance for the order in which we add Parameters. Also is there anyway to convert the time column in excel to Time format through code itself? – Ash1994 Jul 11 '15 at 12:21

Unable to update current time in a excel using oledb

1 Answers1