Avatar of moosetracker
moosetracker asked on

Excel date format programmically in C#

I coded this Excel process and it was working ok.. I got a new Excel file to update the data.. So I tried to run it through.. This time the date fields though they show as dates in the excel worksheet. Looking at them in debug of the program, they are no longer dates. I am unsure what is different from this excel & the last..

Example.  Excel field = 9/1/1996     when pulled into C# code = 35309
                Excel field = 9/28/1996   when pulled into C# code = 35336

This is the setup for opening the Excel Code

string connectionInfo = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
               "Data Source=" + @"C:\word\boyscouts\District\WannalancitTrainings.xls" + ";" +
               "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\";";
             string ConnectionString = connectionInfo;
            excelConnection = new OleDbConnection(ConnectionString); 
            excelConnection.Open(); // This code will open excel file. 
            string connectionInfo2 = conSettings.ConnectionString;
            ConnectionString = connectionInfo2;

            string CommandText = "SELECT * FROM [AllTrainings$]"; 

            cmd = new OleDbCommand(CommandText);
            cmd.Connection = excelConnection;
            rdr = cmd.ExecuteReader();
             while (rdr.Read())
                                 for (int i = 0; i < 200; i++)
                        aryDetail[i] = (rdr[i].ToString()).Trim();

Now reading this next code the array is read and the aryDetail[i] that moves to @DateOfCourse is not the date field it should be..
                for (int i = 23; i < 200; i++)
                    if (aryDetail[i] != string.Empty)
                        cmd = new SqlCommand("SetCourses");
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Process", "Indiv");
                        cmd.Parameters.AddWithValue("@CourseNo", aryCourseType[i]);
                        cmd.Parameters.AddWithValue("@PersonID", aryDetail[0]);
                        cmd.Parameters.AddWithValue("@DateOfCourse", aryDetail[i]);
                        cmd.Connection = con;

Open in new window


Avatar of undefined
Last Comment

8/22/2022 - Mon
Miguel Oz

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

thanks.. I am sure that will work.. Unfortunately when I went back to it, for some reason, they started being passed through as valid dates again.

So.. I am now checking, if valid date I do it one way, if not, I have the code you have given to me..
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck