System.Data.SQLite

Yes, I tried again now for the 3rd time.
The mails were not bounced back ...

... otherwise I need the authorization to attach the file to the post .. it's only 3kb!
Bye.

FWIW: I was getting this error message until I started putting together a small demo to isolate the problem ... the demo worked fine. Turned out there was one date value that had a day of "00" in my data and that was causing the problem. My data was being populated by an outside source (direct SQL) rather than an application, which allowed the invalid date field. Daniele, it might be worth checking your date fields if they are being populated externally too. I've had no other problems with dataAdapter.Fill().

My DB has only 4 records ... It was populated with SQLite Admin 0.8.2.5 Beta (by Orbmu2k).
No date has the day at 00, the values are those posted in this discussion thread ... At least "externally" there's no problem, but the problem exists .. I have seen it several times in different tables in different moments .. sometimes you add a record and everything is ok, sometimes it looks ok in SQLite admin .. but the record actually breaks the DataSet.Fill ...

Well, I still haven't gotten it.  I've enabled attachments on the forums, so just attach it to a post.

Robert

 

I used SQLite Administrator and found it gave me some odd results at times. May have been an older version though.

I'll suggest a couple things without knowing if they help or not, just based on my limited experience (just to rule some things out.)
 
I run your fields and query through sqlite3.exe (as found here: http://www.sqlite.org/download.html ... sqlite-3_3_6) and I don't get any output for the DATETIME(LastUpdated) on my machine. I'm in the US though ... may be regional formatting strings here.

As you mentioned, in the _datetimeFormats array of SQLiteConvert (from the source) there is no time format that accepts "." as a delimiter so that may be where the exception is originating. Can you temporarily change the LastUpdated field data to a ":" delimiter then retry the DataAdapter.Fill()? This will at least tell you if the provider exception is the "." delimiter.

Finally, I took a quick look at the SQLite source. A comment from date.c regarding CURRENT_TIMESTAMP reads:

/*
** If the library is compiled to omit the full-scale date and time
** handling (to get a smaller binary), the following minimal version
** of the functions current_time(), current_date() and current_timestamp()
** are included instead. This is to support column declarations that
** include "DEFAULT CURRENT_TIME" etc.
**
** This function uses the C-library functions time(), gmtime()
** and strftime(). The format string to pass to strftime() is supplied
** as the user-data for the function.
*/

I'm not sure what the C-library functions use but they may use regional settings. If that's the case you may be able to find a workaround at the SQLite site.

Hope something here helps.

Finally, here's the DB. Bye.

Thank you rgesswein for your suggestions. I'll try them, although I think that if the problem is the "." it should always be a problem, not sometimes ....
Back after some tests.

Just finished the tests. I changed the "International setup" of time separator (through the control panel) to ":". The SQLiteAdmin shows the : as separator, but the select resuls is the same.
The problem must be somewhere else ...

I'm not exactly sure where the error lies just yet -- more investigating is needed.  However, one thing I know for sure is that the dates in your database are NOT properly formatted.

This is the raw data from the database you attached:

Objects
1 1 1 1 1 Object1      Y 2006-06-26 10:03:34 2099-01-01 00:00:00 2006-06-26 10:03:34
2 222 2 1 1 Object2      Y 2006-06-26 16.04.32 2099-01-01 00:00:00 2006-06-26 16.04.32
3 1 1 1 1 Object3      Y 2006-07-27 06.21.03 2099-01-01 00.00.00 2006-07-27 06.21.03
4 11 1 1 1 AAA      Y 2006-07-27 11.00.41 2099-01-01 00.00.00 2006-07-27 11.00.41

ObjectType
1 Footwear product   Y 2006-06-15 12.05.59 2099-01-01 00.00.00 2006-06-15 12.05.59
 

Some of the lines in the table have colon-separated times, and others have period-separated times.  The period-separated times are all illegal.  The question becomes, where did they come from?  Was it SQLite itself through the "default" mechanism, or was it the db manager you used, or was it a SQL statement you executed?

I'm going to dig into the SQLite source code to see if it comes from there.

Robert

 

Thank you Robert for your precious help.

Probably the difference between period and colon was caused by me, I actually tried (after getting the DataSet.Fill errors) to change the international options and edit the dates. But the "." is the separator of time parts that is the standard in the Italian internationalization settings (Win XP pro SP2)

By the way, as far as I understand, the problem was originally generated "internally" by SQLite, as I inserted the records with the SQLite administrator by filling ONLY the not null fields and expecting that the timestamps were produced by the DB. So I never wrote a date or time, it was the system through the "DEFAULT CURRENT_TIMESTAMP" mechanism.Unless the interface of SQLite Administrator generates some value when a field is empty (but which value?), the offending data was produced by the DB.

What is strange for me is how an invalid date can be inserted in this way. If we pass through all the localized formatting routines .... yes, we can do all the mess we want, but if the DB engine directly writes the values I supposed it was done directly, without formatting ... in fact I don't have any control on it.
Unless ... the interface actually inserts the correct values, then displays them with the Italian formatting ... and then recognizing that the value has changed (in the interface) ... writes again the mismatched string ... Could it be?

I hope you will solve the mistery, I'll take a look at the sources too.

I've found this, if it helps.


A) SQLite administrator, or SQLite,  does not directly uses the "international settings" time separator.
Now it's set to ":", yet, if I insert a record, LastUpdated is still displayed this way: 29/07/2006 14.00.43

B) I created a trigger On update, always using SQLite Administrator, to update the "LastUpdate" field with the CURRENT_TIMESTAMP value:

UPDATE Objects SET LastUpdated = CURRENT_TIMESTAMP WHERE ObjectId = Old.ObjectID;

Now, if I insert a new record the behaviour is as before, and querying DateTime (LastUpdate) gives an empty string for that record, but if I modify the record using the interface (thus causing the trigger to update the "LastUpdate" field .... well, DateTime (LastUpdate) works!
2006-07-29 14:00:43
And as you can see DateTime() uses the ":" separator.

So the problem is either the interface, or the first insertion of the record ( the DEFAULT generation).

I Finally tracked down the problem to this.

I created an Insert by hand:

INSERT INTO Objects(MomObjectID, MomID, ObjectTypeId, OwnerID, Name) VALUES ('1','1','1','1', 'CICCIO');


It works, and inserts the default values in the timestamp fields.
If I query the following:

SELECT LastUpdated, DateTime(LastUpdated) from Objects

I get

29/07/2006 14.09.08      2006-07-29 14:09:08

the difference being the "." and ":". Looks like SQLAdmin always uses ".".

But the values are OK.

Now if I insert another record with the interface (not the insert statement) , so I think that the Zeos controls are used, I get the lastUpdate field filled with 29/07/2006 6.55.19
Who modified the date? Looks like the interface actually inserts someting strange, some 7 hours
difference ....

and then the query

SELECT LastUpdated, DateTime(LastUpdated) from Objects

gives

29/07/2006 6.55.19

So DateTime() thinks that the date is invalid, and nothing is displayed.

I'm starting to suspect that the culprit is the SQLiteAdmin interface ....

Can anybody suggest a VALID and TESTED alternative?

Guess its time to download SQLiteAdmin and do some tests for myself :)

Robert

 

You might want to try SQLiteSpy instead, since ZeosLib encodes strings incorrectly anyway. I've only tested the string encoding though.

DateTime strings have to be ISO 8601 formatted but you can insert an incorrectly formatted date only to discover the error at retrieval when the SQLite adapter tries to convert the string to a DateTime object.

If you're building SQL strings, format the date properly. E.g. DateTime.Now.ToString("s", DateTimeFormatInfo.InvariantInfo);