Solved

SqlCe The format of inserting as date into record

Posted on 2013-11-07
2
1,445 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 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

14 Experts available now in Live!

Get 1:1 Help Now