Solved

SqlCe The format of inserting as date into record

Posted on 2013-11-07
2
1,485 Views
Last Modified: 2013-11-09
I have a Sqlce database that I insert a row with a date.

i.e

 Dim insertAssum As String = "INSERT INTO componentDataEntry (modelName,    lastModificationDate VALUES('" & localModelName & " , '" & localDateCreated & "')"


            Dim assumptionCmd As New SqlCeCommand(insertAssum, connection)
         
            connection.Open()
            assumptionCmd.ExecuteNonQuery()
           
            connection.Close()



The local Regional date format is "dd/MM/yyyy" and the PC is set to this. when it displays a date it will show this format.

 If I try to enter this format into the DB I get an error of "There was an error in part of the date format".


 If I enter the date in the format
dim datecreated as string = cdate(localdatecreated).tostring("MM/dd/yyyy hh:mm;ss")

it will work


Although this works, it bugs me that I have to do this

Any ideas?
0
Comment
Question by:milkwood
2 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39633712
You have to get a date that the DBMS recognizes, unless you use Year-Month-Day format, since that is the only unambiguous date form.
0
 
LVL 16

Accepted Solution

by:
hjgode earned 500 total points
ID: 39633813
I assume the LCID defines which data format you can use with a SQL server (regardless of being SQL, SQLCE, mySQL or any other).

The Locale Identifier is defined on the creation of a database and cannot be changed afterwards:

ocale identifier-or-Lcid-or-initial lcid
	
The locale ID (LCID) to use with the database. Valid LCID values are listed in the CultureInfo class.
Note
This property is a database creation time option and is ignored when connecting to an existing database.

Open in new window


by [http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring%28v=vs.100%29.aspx]

Afterwards you have to use the locale date format as accepted and used by the DB.

The generic datetime format "YYYY-MM-DD " is accepted always: http://technet.microsoft.com/en-us/library/bb630352.aspx.

You may use a datetime var and use that in a param query like
 void SetDate(int recordID, datetime timeStamp)
 {
    string SQL = "UPDATE [sometable] SET someDateTimeColumn= @NewTime WHERE ID= @ID";

    using (var cn = new SqlCeConnection("connection string here"))
    using (var cmd = new SqlCeCommand(SQL, cn))
    {
        cmd.Parameters.Add("@NewTime", SqlDbType.DateTime).Value = timeStamp;
        cmd.Parameters.Add("@ID", SqlDbType.Integer).Value = recordID;

        cn.Open();
        cmd.ExecuteNonQuery();
    }
} 

Open in new window

<competing site link removed - GaryC123>
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

791 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