Solved

Converting string to Date for sql C#

Posted on 2012-04-13
5
264 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
After hours of troulbe shooting and researching google I found the solution and it worked.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now