Error inserting a date into an SqlCe DataTable

Paul Hasler

Yes, Ok, I know this is a common problem, but I've read through a lot of similar threads and still can't seem to see what I'm doing wrong. :confused: Some fresh eyes might see what is probably staring me in the face. I've written the following sub to insert a record into an SqlCe database DataTable called BRDataTable. On executing sqlInsert.ExecuteNonQuery() near the end of the code, I get an error message "There was an error in a part of the date format.". I've put a breakpoint at sqlInsert.ExecuteNonQuery for debugging, and checked the contents of sqlInsert.CommandText, which is: "INSERT INTO BRDataTable VALUES('Test', '13/01/2010', '', '', '1', '7', '0', '0')" I've also tried setting CurrentRecord.WhenReady as DateTime instead of just Date, giving the following CommandText, but I still get the same error: "INSERT INTO BRDataTable VALUES('Test', '13/01/2010 11:08:40 AM', '', '', '1', '7', '0', '0')" I've double checked, and the DataType for the second column in the DataTable is definitely set to DateTime. Thanks in advance. Paul

Private Sub AddCurrentRecordToDatabase()

    '#####################################################
    '# Add the contents of CurrentRecord to the database #
    '#####################################################

    'Make a connection to the database
    Dim sqlConn As New SqlCeConnection(sqlConnString)

    'Set up the insert command
    Dim sqlInsert As SqlCeCommand = sqlConn.CreateCommand

    sqlInsert.CommandText = "INSERT INTO BRDataTable VALUES('" \_
    & CurrentRecord.Name & "', '" \_
    & CurrentRecord.WhenReady & "', '" \_
    & CurrentRecord.Address & "', '" \_
    & CurrentRecord.Notes & "', '" \_
    & ConvertBooleanToBit(CurrentRecord.Reminder) & "', '" \_
    & CurrentRecord.DaysWarning & "', '" \_
    & ConvertBooleanToBit(CurrentRecord.Acknowledged) & "', '" \_
    & CurrentRecord.Age & "')"

    'Execute the insert command
    sqlConn.Open()
    sqlInsert.ExecuteNonQuery()
    sqlConn.Close()

End Sub

Andy_L_J

Have you tried with date formatted mm/yy/dddd? Just a thought...

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

Paul Hasler

Thanks for your reply Andy_L_J. I got it to work by formating the date as "yyyyMMdd" :thumbsup:

Paul Hasler

Ok, I've got it working by formating the date component of my CommandText as "yyyyMMdd", but I don't really understand why I had to do it this way. Most of the threads I read regarding similar problems talked about formating the date as; "MM/dd/yyyy" or "MM dd yyyy" or "MM.dd.yyyy" or "MM-dd-yyyy" or "dd/MM/yyyy" etc etc None of these worked though. Only "yyyyMMdd" worked. This doesn't line up with my regonal settings either. We have the date format as "dd/MM/yyyy" here in Australia. Can anyone give a good explanation why only "yyyyMMdd" works? :confused::confused::confused:

Dave Kreskowiak

Google for "vb.net parameterize query" and you'll find that, done without all the string concantentation, this would not have been a problem at all.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

_Damian S_

Paul Hasler wrote:

Can anyone give a good explanation why only "yyyyMMdd" works?

yyyymmdd is the best date format in my books... it means all dates come out in date order when you return them in a query. yyyymmdd is a standard to get around the horrors that we all have faced (I'm sure) back in the old days when someone would insert a date like "03/04/95", and depending on regional settings you would get 3 April 1995 or 4 March 1995.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

Paul Hasler

Thanks Dave! I Googled your suggestion and it was a bit of a revelation! Someone needs to find the button marked PURGE on that "little black box with the flashing red light" that is the internet. I've obviously only been mucking about with SqlCe for a week or so, and as a noob most of my searches for tutorials etc seem to have come up with the old dinosaur methods, so I assumed that's how it's done. Parameterization is SO much cleaner and useable! Cheers again. Paul