I give up.
I am trying to read a date value from a SQLite database. The column is defined as a DATETIME type. I sucessfully write the data to the database but when trying to read it throws the error: "
String was not recognized as a valid DateTime.
" My code looks like this:
SQLiteConnection conn = new SQLiteConnection(this.ConnString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "SELECT * FROM tblAutomatedSearches";
SQLiteDataReader reader = cmd.ExecuteReader();
this.DBID = Convert.ToInt32(reader["ID"]);
this.NotifyEmail = reader["notifyEmail"].ToString();
this.SearchTitle = reader["jobTitle"].ToString();
Object tempObject = reader["endDate"];
string tempString = Convert.ToString(reader["endDate"]);
The date is in the format of "2012/06/11 12:51:14".
I understand that the SQLite engine actually stores the date value as a string. So it seems like the format that I write it in cant be read. I have tried EVERY format I can think of, e.g. MM/dd/yy, MM/dd/yyyy, M/d/yy, etc.
I am writing the date to the DB using a paramerterized query, ala':
SQLiteConnection conn = new SQLiteConnection(this.ConnString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "INSERT INTO tblAutomatedSearches (jobTitle, endDate, notifyEmail, caseNumber) VALUES(@jobTitle,@endDate,@notifyEmail, @caseNumber)";
cmd.Parameters.AddWithValue("@jobTitle", this.SearchTitle);
cmd.Parameters.AddWithValue("@endDate", String.Format("{0:yyyy/MM/dd HH:mm:ss}", this.EndDate));
cmd.Parameters.AddWithValue("@notifyEmail", this.NotifyEmail);
cmd.Parameters.AddWithValue("@caseNumber", this.CaseNumber);
cmd.ExecuteNonQuery();
These inserts run just fine, so I'm left to think that the database is just entering the date as a string, but trying to read it as a date when retrieving it. I must be missing something. I will need to be able to sort based upon the date of these entries, so I can't just enter them as text.
You help is greatly appreciated.