We help IT Professionals succeed at work.
Get Started

Excel date format programmically in C#

moosetracker
moosetracker asked
on
597 Views
Last Modified: 2012-05-10
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;
                        cmd.ExecuteNonQuery();
                    }
                }
            excelConnection.Close();

Open in new window

Comment
Watch Question
Senior Software Engineer
CERTIFIED EXPERT
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE