Solved

SqlCe The format of inserting as date into record

Posted on 2013-11-07
2
1,517 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Transaction Number 19 50
Learning About The VB.NET TableLayoutPanel Control 5 44
Help with Report Viewer in VS2013 6 34
Run software updates from the website 6 51
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…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 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