Solved

SqlCe The format of inserting as date into record

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 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