Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Converting string to Date for sql C#

Posted on 2012-04-13
5
Medium Priority
?
275 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

581 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