System.Data.SQLite

Hi everyone i have a problem with the sqlite data adapter when i try to execute fill() method for filling the datatable; when querying the DateTime column in table where that column is NULL. I get the following exception: String was not recognized as a valid DateTime. i found somewhere that this can be prevented if i put in my connection string: DateTimeFormat=TICKS; but this only raises the another exception: Input string was not in a correct format. SQLite versio is 3.4.2 Please help

oh, and one more thing, I can bypass this problem if I use the SUBSTR function on datetime column, but when i try to execute : select * from tableName, the null value of the datetime column gives me problems... is there any way that i can execute select * from tableName query to fill new datatable This is my code: SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); System.Data.SQLite.SQLiteConnection connection = new SQLiteConnection(Data Source=datatable.sqlite"); connection.Open(); cmd.Connection = connection; cmd.CommandText = "select * from tableName"; cmd.CommandType = CommandType.Text; Datatable tbl = new Datatable(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(tbl);

 Leo,

 I get the same message when using dataAdapter.fill(). While I couldn't solve it properly, i used CREATE TABLE (myDatetimeField TEXT) instead. But it translates time to my timezone (dd/mm/yyyy hh:mm:ss) so i cannot sort on this field.

Thus far, i just managed to avoid a program crash

What about raising a bug report ?   Fact is dataAdapter.Fill(table); doesn't reconize datetime properly

In the meantime :

I think about using a dataReader and creating a dataTable by code somehow...   i ll check how reader bypasses datetime error ... and post results here         

 

How are your datetimes formatted in your database?  If they're improperly formatted, then Fill() will generate an error.  NULL should be handled transparently as NULL, so shouldn't cause a program crash.

The only scenario I can envision where Fill() would crash on a datetime is if the underlying datetime was formatted incorrectly in the database.

 

 Robert,

You were right. DATETIME is to be formatted properly in writing operation or set to null if necessary. After many tries, i used this method witch seems to work:

        public static object fmtDate(object mydate)
        {
            if (mydate is System.DateTime)
                return ((System.DateTime)mydate).ToString("yyyy-MM-dd HH:mm:ss.fffffff");
            else
                return null;
        }
 

Then :

       cmd1.Parameters.Add("", DbType.DateTime).Value = fmtDate(t.Rows[i]["dtModif"]);
 

Thank you

 Regards