Thanks Karl and Nic. I didn't think about the date format being off. When I have used sqlite in the past, I've used the internal command for dates. I was grappling for a quick sample. Due to the heat, I thought I would try something related to temperature over the course of a day. I just changed my sample to get around this. Thanks again! Wally
Date: Mon, 18 Jul 2011 17:16:38 -0300 Subject: Re: [MonoTouch] datetime parsing with datasets in sqlite From: [email protected] To: [email protected]; [email protected] Hi Wally, I'd recommend to change: String.Format("INSERT INTO TESTTABLE (DT, DEGREES) VALUES ('{0}', 87.8)", DateTime.Now) Per: String.Format("INSERT INTO TESTTABLE (DT, DEGREES) VALUES (datetime('now', 'localtime'), 87.8)") This comment isn't because I know some hidden thing, but I all life used in SQL Server queries GETDATE() to get local date in ASP.NET and not as a parameter. So when ported to Sqlite, I'm doing the same way, replacing all GETDATE() per datetime('now', 'localtime'). BTW, if this wasn't a loop with any kind of queries and was a single query, could has parameters instead String.Format, that maybe convert dates better, using just that I don't have problems: sqlCommand.Parameters.AddWithValue("@Date", DateTime.Now); And, as Sqlite is a bit sentimental with strings dates, I was looking know inside my Rda.Pull() mimic of SQL Mobile 2005, I don't remember why I'm doing equivalent to that but I found this: DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") To force the string format that Sqlite understand. Also it could could be write with "usings": string dir = Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); string dbFile = "Test.db3"; string db = Path.Combine(dir, dbFile); string dbConn = String.Format("Data Source={0}", db); using (SqliteConnection conn = new SqliteConnection()) { using (SqliteCommand cmd = new SqliteCommand()) { if ( !File.Exists(db) ) { SqliteConnection.CreateFile(db); } conn.ConnectionString = dbConn; cmd.Connection = conn; conn.Open(); string[] sql = new string[]{ "CREATE TABLE IF NOT EXISTS TESTTABLE(TID INTEGER PRIMARY KEY, DT DATETIME, DEGREES INTEGER )", "INSERT INTO TESTTABLE (DT, DEGREES) VALUES (datetime('now', 'localtime'), 87.8)" }; foreach(string s in sql) { cmd.CommandText = s; cmd.ExecuteNonQuery(); } } // I thing that don't need to create another command, can use the same, I don't know if changes something inside // But I usually use just one command if is in the same database during a method call that do something to database // Just need to clear parameters if using it with cmd.Parameters.Clear(); using (SqliteCommand sqlCm = new SqliteCommand(conn)) { string sSql = "select DT, DEGREES from TESTTABLE"; sqlCm.CommandText = sSql; sqlCm.CommandType = CommandType.Text; using (SqliteDataAdapter sda = new SqliteDataAdapter(sqlCm)) { DataSet ds = new DataSet(); sda.Fill(ds, "TESTTABLE"); lblOutput.Text = String.Format("Records returned: {0}", ds.Tables["TESTTABLE"].Rows.Count); if ( conn.State != ConnectionState.Closed ) { conn.Close(); } } } } Karl
_______________________________________________ MonoTouch mailing list [email protected] http://lists.ximian.com/mailman/listinfo/monotouch
