Solved

Converting string to Date for sql C#

Posted on 2012-04-13
5
267 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 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