System.Data.SQLite

Hi,

I'm not sure if this is a bug or it's just me not knowing how to do things.

I have a dataset with a table that has a columnt type  System.DateTime. I created this dataset using the datase designer in VS2005, while connected to my sqlite.net database.

I use the tableadapter.fill method to populate the dataset in my form, and then bind a datagridview to it.

I needed to format the date on the datagridview to say.. "m/d/yyyy hh:mm". I did that by editing the datagridview columns, and change the DefaultCellStyle property to "DataGridViewCellStyle { Format=G }"

That's all good, except that when I manually edit the cell and save the data.... WOOPS!! it stores the date string in the "m/d/yyyy hh:mm" format instead of using the sqlite.net native string format "yyyy-mm-dd hh:mm:ss.ssssss"

Of course, that messes up my entire application, since that format is not recongnized as a valid datetime by sqlite.net when I read the field latter.

Am I doing somehting wrong?

Thanks 

Handling dates always was messy (even at MSAccess-times). I don't know how you update your database, but if you store the value as displayed you will mess up the field in the database. You must e.g. use the last form of the below table.

Thanks for the answer Mark2, but I'm not sure I understand what you mean.

The way I update the DB is: the user changes the data in the datagridview, which is binded to a dataset. The dataset is populated using tableadapter.fill and updated using tableadapter.update.

Where should I convert the data to the string SQLite needs? In the datagridview.CellValidated event? on datagridview.CellParsing event? 

And how? Remember I need to show the user a specific datetime format, and that's why I set the datagridview1.datecolumn.DefaultCellstype.DataGridViewCellStyle { Format=G }. And of course, the dataset1.table.column.datatype is System.datetime.

I was hoping SQLite.net would take care of converting the "System.date" value on the dataset, to the appropiate string in the DB. But i guess that does not happen.

I think mine is the typical case where the user needs to change a date on a datagridview using SQLite. What would be the best practice?

Any help is welcome

Thanks again

Alex 

Hrm ... the grid's underlying datatype should be datetime ... and the parameter passed during the tableadapter.Update() to SQLite should also be datetime.  I'll see if I can put together a test case for this and see what happens.

Hi Robert,

Have you got any updates on this issue?

Thanks!

No I haven't had time to write the code to test it.  I don't suppose you have some ready-made code for me to run?

 I emailed you a sample. I hope it helps. Let me know. Thanks!

Oh!! I see!!

I guess, since I read in so many places that SQLite does not have a "datetime" datatype, it never ocurred to me.

I cannot create a table with a column which type is datetime in sqlite.

However, I can do so using VS sql interface connected through sqlite.net

Thanks for your time Robert. I love your software!

Actually in SQLite you can create a table with a column of any type you want.  SQLite ignores the type declaration internally.  However, the .NET wrapper processes the datatype and will try and coerce your values in and out of the database to the types you've declared.