Solved

Converting string to Date for sql C#

Posted on 2012-04-13
5
266 Views
Last Modified: 2012-04-18
I have a C# application that loops through an excel file and grabs the values from the fields to insert into our sql database. One of the fields in the excel file has dates in the following format 4/13/2012. This value is supposed to go into a datetime field in sql server, yet no matter what i try I keep getting an error inserting it. What is the best way to go about taking a string such as 4/12/2012 and getting it into the database. Also will i need single quotes around the value in the query or not? Thanks for any help.
0
Comment
Question by:nextmedstaff
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Ashok
ID: 37843565
use parameter

DbCommand cmd = db.GetSqlStringCommand(@"INSERT INTO [XXX] (
    ...
                                                          ,[FirstDate]
    ...
    ) VALUES (@FirstDate,...");

   db.AddInParameter(cmd, "@FirstDate", DbType.DateTime, DateTime.Now );

HTH
Ashok
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37843580
cmdInsert = New SqlCommand("INSERT INTO [empbill] ([deptcode], [personNo], [entrydate]) VALUES (@deptcode, @personNo, @entrydate);", db)
cmdInsert.Parameters.AddWithValue("deptcode", eno.Text)
cmdInsert.Parameters.AddWithValue("personNo", TextBox5.Text)
cmdInsert.Parameters.AddWithValue("entrydate", dd.Text)

HTH
0
 

Author Comment

by:nextmedstaff
ID: 37843855
So I figured out what the problem is, the excel file we receive has the cells formatted as a date.. for whatever reason when my application reads in the value it wants to read in a double value. I went to the excel file and converted the cells to General and im now seeing where my program is pulling that double value from. 41085 is the format they are in. Is there a way to convert that to a date in C#??
0
 

Accepted Solution

by:
nextmedstaff earned 0 total points
ID: 37843876
I finally figured it out, I had to use the conversion below to convert from the format it was stored in excel in.

 double tempDate = (Double)oWs.get_Range("e"+i,"e"+i).Cells.Value2;
 oJob.startdate = DateTime.FromOADate(tempDate);
0
 

Author Closing Comment

by:nextmedstaff
ID: 37859708
After hours of troulbe shooting and researching google I found the solution and it worked.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question