2

I am working on a C# WPF project which uses an SQLite database. I am trying retrieving the data from a table within the sqlite database and add to a data set so I can add the data set to the items source of the data grid. One of the columns is a DateTime column but I am getting the following error:

String was not recognized as a valid DateTime.

Below is how I am retrieving the data

private DataSet getAlarmsForSqlite()
        {
            DataSet ds = new DataSet();
            try
            {
                using (ConnectSQLiteDatabase db = new ConnectSQLiteDatabase(dbPassword))
                {
                    string query = "SELECT * FROM alarms ORDER BY date";
                    SQLiteDataAdapter da = new SQLiteDataAdapter(query, db.conn);
                    da.Fill(ds);
                }

            }
            catch (SQLiteException ex)
            {
                Console.WriteLine("Failed to get alarms. Sqlite Error: {0}", ex.Message);
                return null;
            }
            return ds;
        }

And below is the create statement for the table

CREATE TABLE `alarms` (`id` int(11) NOT NULL, `date` datetime NOT NULL,
`type` varchar(50) NOT NULL,
`message` mediumtext NOT NULL, 
`level` varchar(45) NOT NULL, 
`page` varchar(500) NOT NULL, 
`acknowledged` char(1) DEFAULT '0', PRIMARY KEY (`id`))

Thanks for any help you can provide.

Boardy
  • 35,417
  • 104
  • 256
  • 447
  • I am not sure, but that column named `date` is a bit suspicious. Could you try to encapsulate it with square brackets? – Steve Apr 22 '13 at 20:06
  • not sure what you mean, suspicious in what way and what would square brackets achieve – Boardy Apr 22 '13 at 20:07
  • I suspect that is intepreted as a keyword, and I mean `SELECT * FROM alarms ORDER BY [date]`, well actually SQLite wants double quotes around keywords but square brackets are accepted for compatibility and are easy to write http://www.sqlite.org/lang_keywords.html – Steve Apr 22 '13 at 20:09
  • ah OK, tried that but still no luck. I don't think the order by though is the reason why the datetime error is occurring – Boardy Apr 22 '13 at 20:13
  • Well your code seems absolutely legit, perhaps there is some invalid datetime in that column. You could try to debug using a DataReader and loop through your data until you get the error. – Steve Apr 22 '13 at 20:15

3 Answers3

0

try this way

select *  from alarms order  by date(date) DESC

see the documentation.

Ramesh Rajendran
  • 37,412
  • 45
  • 153
  • 234
0

There's some discussion regarding this issue here; sqlite throwing a "String not recognized as a valid datetime"

Apparently the SQLite datetime to C# DateTime serialization mapping doesn't exactly work. To me, it sounds like the datetime is coming back as a string so you need to do DateTime.Parse() in your code. In one of the answers to that question it suggests the datetime also needs to be cast to an nvarchar in order for it work.

Community
  • 1
  • 1
evanmcdonnal
  • 46,131
  • 16
  • 104
  • 115
  • Thanks but I don't think this helps as that question they are lopping over the result set so they can manipulate each field which I'm not doing as I'm just putting the whole result set into the data set – Boardy Apr 22 '13 at 20:31
  • I haven't dealt with data sets in C# but aren't you still required to create some kind of a contract? Like column 1 is type a and maps to column 1 of my data set? If that is the case I bet you can coerce the value there. Other .NET serialization methods such as the Entity Framework offer those features so I think it's likely datasets also have them. – evanmcdonnal Apr 22 '13 at 21:22
0

It is one of two things:

  • The data in your table is actually in a format which SQLite is not capable of turning into a datetime. For best results use the ISO8601 date format "yyyy-MM-dd HH:mm:ss.FFFFFFF". It requires conversion before writing the data to the database, but is transparently handled when reading the data back to the dataset.

  • The ORDER BY term in your query is for some reason preventing the SQLiteDataAdapter from identifying the correct type for the column. Even if you order by some other column, the result will be the same. To avoid the problem, remove the ordering term (which will then identify the column data type correctly) and sort your DataSet after it has been filled.

EDIT: After looking into the recommendation of Ramesh, when using the "date" function with the Order By, the behavior outlined in my second point no longer happened.

Williams
  • 1
  • 1