[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SqlCe The format of inserting as date into record

Posted on 2013-11-07
2
Medium Priority
?
1,586 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 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