SQLite DateTime

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
The SQLite documentation says datetimes must be in the format YYYY/MM/DD HH:mm:ss.xxx
My system format for dates is DD/MM/YYYY.

I painstakingly build up my date to store to be:
"2014/10/25 00:00:00.000"
It stores successfully with an INSERT INTO. No complaints.
With SQLiteManager I can see it looks exactly as saved.

Problem: When filling a dataset from the table I get "String was not recognised as a valid DateTime."
Does that mean that SQLite didn't recognise "2014/10/25 00:00:00.000" as valid?
If so, why not?
(I even tried removing the .xxx part to try and solve my problem.)

It is YYYY-MM-DD, not YYYY/MM/DD. Problem solved.
 
Last edited:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,141
Location
Sydney, Australia
Programming Experience
10+
When the documentation says that date/time values must be in that format it's talking about literals. You shouldn't really be using date/time literals at all so that format is irrelevant. In your .NET code you use DateTime values. You pass DateTime values to the database and you get DateTime values from the database. You NEVER use text so format is NEVER an issue.

Follow the Blog link in my signature and check out my post on Parameters In ADO.NET. Note that at the beginning, where I demonstrate what NOT to do, I'm using literals in SQL code and therefore format is important. When it's done properly using parameters, the DateTime is never converted to a String so format is never an issue. The value remains in binary form at all times. It's the same for all databases.
 

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
Makes sense. Because I write to a number of tables, all completely different, I have been doing this:
C#:
if (!DataAccess.InsertData(TableName, column1name, column1data, column2name, column2data, ....))
And then in DataAccess class:
C#:
      [COLOR=#0000ff][B]public[/B][/COLOR] [COLOR=#a52a2a]static[/COLOR] [COLOR=#ff0000][B]bool[/B][/COLOR] [COLOR=#191970][B]InsertData[/B][/COLOR]([COLOR=#ff0000]string[/COLOR] whichTable, [COLOR=#ff1493][B]params[/B][/COLOR] [COLOR=#ff0000]object[/COLOR][] args)
      {
         [COLOR=#ff0000][B]int[/B][/COLOR] numColumns = [COLOR=#00008b]0[/COLOR];
         [COLOR=#0000ff][B]foreach[/B][/COLOR] (var tt [COLOR=#0000ff][B]in[/B][/COLOR] args)
         {
            numColumns += [COLOR=#00008b]1[/COLOR];
         }
         numColumns = numColumns / [COLOR=#00008b]2[/COLOR];
         
         [COLOR=#ff0000]string[/COLOR] insertString = [COLOR=#0000ff]"INSERT INTO "[/COLOR] + whichTable + [COLOR=#0000ff]" ("[/COLOR];
         [COLOR=#0000ff][B]for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]0[/COLOR]; i < (numColumns - [COLOR=#00008b]1[/COLOR]) * [COLOR=#00008b]2[/COLOR]; i += [COLOR=#00008b]2[/COLOR])
         {
            insertString += args[i] + [COLOR=#0000ff]", "[/COLOR];
         }
         insertString += args[(numColumns - [COLOR=#00008b]1[/COLOR]) * [COLOR=#00008b]2[/COLOR]] + [COLOR=#0000ff]") VALUES ("[/COLOR];
         [COLOR=#0000ff][B]for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]1[/COLOR]; i < ((numColumns * [COLOR=#00008b]2[/COLOR]) - [COLOR=#00008b]1[/COLOR]); i += [COLOR=#00008b]2[/COLOR])
         {
            insertString += [COLOR=#0000ff]"'"[/COLOR] + args[i] + [COLOR=#0000ff]"', "[/COLOR];
         }
         insertString += [COLOR=#0000ff]"'"[/COLOR] + args[(numColumns * [COLOR=#00008b]2[/COLOR]) - [COLOR=#00008b]1[/COLOR]] + [COLOR=#0000ff]"');"[/COLOR];
I can change it to use parameters. But I will have to add a 3rd param, the SqlDbType.
I'll convert it to parameters and without strings.
Then I'll redo it with 3 values per entry. Using parameters it will be easier to rewrite, without all those single and double quotes.
It is not somehting to tackle late at night when you are tired. :friendly_wink:
 

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
I have written a function for generalised use with any number of columns, using parameters.
This is the function'
C#:
      [COLOR=#0000ff][B]public[/B][/COLOR] [COLOR=#a52a2a]static[/COLOR] [COLOR=#ff0000][B]bool[/B][/COLOR] [COLOR=#191970][B]SaveData[/B][/COLOR]([COLOR=#ff0000]string[/COLOR] whichTable, [COLOR=#ff1493][B]params[/B][/COLOR] [COLOR=#ff0000]object[/COLOR][] args)
      {
         [COLOR=#ff0000][B]int[/B][/COLOR] numColumns = [COLOR=#00008b]0[/COLOR];
         [COLOR=#0000ff][B]foreach[/B][/COLOR] (var tt [COLOR=#0000ff][B]in[/B][/COLOR] args)
         {
            numColumns += [COLOR=#00008b]1[/COLOR];
         }
         numColumns = numColumns / [COLOR=#00008b]3[/COLOR];

         [COLOR=#ff0000]string[/COLOR] insertString = [COLOR=#0000ff]"INSERT INTO "[/COLOR] + whichTable + [COLOR=#0000ff]" ("[/COLOR];
         [COLOR=#008000]//  First and middle columns get a ,[/COLOR]
         [COLOR=#008000]//  The last column gets a )[/COLOR]
         [COLOR=#0000ff][B]for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]0[/COLOR]; i < (numColumns * [COLOR=#00008b]3[/COLOR]); i += [COLOR=#00008b]3[/COLOR])
         {
            [COLOR=#0000ff][B]if[/B][/COLOR] (i < (numColumns - [COLOR=#00008b]1[/COLOR]) * [COLOR=#00008b]3[/COLOR])
            {
               insertString += args[i] + [COLOR=#0000ff]", "[/COLOR];
            }
            [COLOR=#0000ff][B]else[/B][/COLOR]
            {
               insertString += args[i] + [COLOR=#0000ff]")"[/COLOR]; 
            }
         }

         [COLOR=#008000]//  We now have column names done[/COLOR]
         [COLOR=#008000]//  Insert the parameter names (values)[/COLOR]
         [COLOR=#008000]//  First and middle columns get a ,[/COLOR]
         [COLOR=#008000]//  The last column gets a )[/COLOR]
         insertString += [COLOR=#0000ff]" VALUES ("[/COLOR];
         [COLOR=#0000ff][B]for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]0[/COLOR]; i < (numColumns * [COLOR=#00008b]3[/COLOR]); i += [COLOR=#00008b]3[/COLOR])
         {
            [COLOR=#0000ff][B]if[/B][/COLOR] (i < (numColumns - [COLOR=#00008b]1[/COLOR]) * [COLOR=#00008b]3[/COLOR])
            {
               insertString += [COLOR=#0000ff]"@"[/COLOR] + args[i] + [COLOR=#0000ff]", "[/COLOR];
            }
            [COLOR=#0000ff][B]else[/B][/COLOR]
            {
             insertString += [COLOR=#0000ff]"@"[/COLOR] + args[i] + [COLOR=#0000ff]");"[/COLOR];
            }
         }

         [COLOR=#008000]//  Insert string is now complete[/COLOR]
         [COLOR=#004085]SQLiteCommand[/COLOR] insertSQL = [COLOR=#008b8b][B]new[/B][/COLOR] [COLOR=#004085]SQLiteCommand[/COLOR] (insertString, [COLOR=#004085]Variables[/COLOR].[I]conn[/I]);
         
         [COLOR=#008000]//  Add the data types[/COLOR]
         [COLOR=#0000ff][B]for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]0[/COLOR]; i < (numColumns * [COLOR=#00008b]3[/COLOR]); i += [COLOR=#00008b]3[/COLOR])
         {
            insertSQL.Parameters.[COLOR=#191970][B]Add[/B][/COLOR]([COLOR=#0000ff]"'@"[/COLOR] + args[i] + [COLOR=#0000ff]"'"[/COLOR], ([COLOR=#004085][B]DbType[/B][/COLOR]) args[i + [COLOR=#00008b]2[/COLOR]]);
         }
         
         [COLOR=#008000]//  Add the values[/COLOR]
         [COLOR=#0000ff][B]for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]0[/COLOR]; i < (numColumns * [COLOR=#00008b]3[/COLOR]); i += [COLOR=#00008b]3[/COLOR])
         {
            insertSQL.Parameters.[COLOR=#191970][B]AddWithValue[/B][/COLOR]([COLOR=#0000ff]"'@"[/COLOR] + args[i] + [COLOR=#0000ff]"'"[/COLOR], args[i + [COLOR=#00008b]1[/COLOR]]);
         }

         [COLOR=#008080][B]try[/B][/COLOR]
         {
            [COLOR=#004085]Variables[/COLOR].[I]conn[/I].[COLOR=#191970][B]Open[/B][/COLOR]() ;
            insertSQL.[COLOR=#191970][B]ExecuteNonQuery[/B][/COLOR]();
         }
         [COLOR=#008080][B]catch[/B][/COLOR] ([COLOR=#004085]Exception[/COLOR] ex) 
         {
            [I]errorString[/I] = [COLOR=#0000ff]"Saving data to "[/COLOR] + whichTable + [COLOR=#0000ff]" failed with this error message:"[/COLOR] + [COLOR=#004085]Environment[/COLOR].NewLine;
            [I]errorString[/I] += ex.Message + [COLOR=#004085]Environment[/COLOR].NewLine;
            [I]errorString[/I] += [COLOR=#0000ff]"Make notes and get the help of an SQLite expert."[/COLOR];
            [COLOR=#004085]MessageBox[/COLOR].[COLOR=#191970][B]Show[/B][/COLOR]([I]errorString[/I], 
                            [COLOR=#0000ff]"Problem"[/COLOR], 
                            [COLOR=#004085][B]MessageBoxButtons[/B][/COLOR].[I]OK[/I], 
                            [COLOR=#004085][B]MessageBoxIcon[/B][/COLOR].[I]Error[/I]);
               [COLOR=#004085]Variables[/COLOR].[I]conn[/I].[COLOR=#191970][B]Close[/B][/COLOR]() ;
            [COLOR=#000080]return[/COLOR] [COLOR=#008b8b][B]false[/B][/COLOR];
         }

         [COLOR=#000080]return[/COLOR] [COLOR=#008b8b][B]true[/B][/COLOR];         
      }
To test it I use
C#:
         [COLOR=#0000ff][B]if[/B][/COLOR] (![COLOR=#004085]Common[/COLOR].[COLOR=#191970][B]SaveData[/B][/COLOR]([COLOR=#0000ff]"Weights"[/COLOR],
                      [COLOR=#0000ff]"Date"[/COLOR], [I]dateDate[/I].Value.Date, [COLOR=#004085][B]DbType[/B][/COLOR].[I]Date[/I],
                      [COLOR=#0000ff]"Cat"[/COLOR], [COLOR=#004085]Convert[/COLOR].[COLOR=#191970][B]ToInt32[/B][/COLOR]([I]cboCat[/I].SelectedValue), [COLOR=#004085][B]DbType[/B][/COLOR].[I]Int32[/I],
                      [COLOR=#0000ff]"Weight"[/COLOR], catWeight, [COLOR=#004085][B]DbType[/B][/COLOR].[I]Decimal[/I])) [COLOR=#000080]return[/COLOR];
When I run it, it returns the error "unknown error Insufficient parameters supplied to the command."

Checking, I get insertString is INSERT INTO Weights (Date, Cat, Weight) VALUES (@Date, @Cat, @Weight);

insertSQL.Parameters has 3 rows.
insertSQL.Parameters.Value has the correct values.
insertSQL.Parameters.DbType has the correct data types.

Yet still the error.

Just before the try block, the value of insertSQL.CommandText is still the same as insertString (ie, with the @values). Is this correct, or should they have been replaced with the actual values by then?
I can't see any other value of insertSQL that I can inspect.

I might add that if I use a dedicate routine:
C#:
[COLOR=#0000ff][B]public[/B][/COLOR] [COLOR=#a52a2a]static[/COLOR] [COLOR=#ff0000][B]bool[/B][/COLOR] [COLOR=#191970][B]SaveWeights[/B][/COLOR]([COLOR=#004085][B]DateTime[/B][/COLOR] theDate, [COLOR=#ff0000][B]int[/B][/COLOR] theCat, [COLOR=#ff0000][B]decimal[/B][/COLOR] theWeight)
      {

         [COLOR=#ff0000]string[/COLOR] insertString = [COLOR=#0000ff]"INSERT INTO Weights (Date, Cat, Weight) VALUES (@theDate, @theCat, @theWeight)"[/COLOR];   
         
         [COLOR=#004085]SQLiteCommand[/COLOR] insertSQL = [COLOR=#008b8b][B]new[/B][/COLOR] [COLOR=#004085]SQLiteCommand[/COLOR] (insertString, [COLOR=#004085]Variables[/COLOR].[I]conn[/I]);
         
         insertSQL.Parameters.[COLOR=#191970][B]Add[/B][/COLOR]([COLOR=#0000ff]"@theDate"[/COLOR], [COLOR=#004085][B]DbType[/B][/COLOR].[I]DateTime[/I]);
         insertSQL.Parameters.[COLOR=#191970][B]Add[/B][/COLOR]([COLOR=#0000ff]"@theCat"[/COLOR], [COLOR=#004085][B]DbType[/B][/COLOR].[I]Int32[/I]);
         insertSQL.Parameters.[COLOR=#191970][B]Add[/B][/COLOR]([COLOR=#0000ff]"@theWeight"[/COLOR], [COLOR=#004085][B]DbType[/B][/COLOR].[I]Decimal[/I]);

         insertSQL.Parameters.[COLOR=#191970][B]AddWithValue[/B][/COLOR]([COLOR=#0000ff]"@theDate"[/COLOR], theDate);
         insertSQL.Parameters.[COLOR=#191970][B]AddWithValue[/B][/COLOR]([COLOR=#0000ff]"@theCat"[/COLOR], theCat);
         insertSQL.Parameters.[COLOR=#191970][B]AddWithValue[/B][/COLOR]([COLOR=#0000ff]"@theWeight"[/COLOR], theWeight);
it works


EDIT: Got confused with building a string and providing variables.
This bit:
C#:
[COLOR=#0000ff][COLOR=#008000]//  Add the data types[/COLOR][B]
for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]0[/COLOR]; i < (numColumns * [COLOR=#00008b]3[/COLOR]); i += [COLOR=#00008b]3[/COLOR])
{
    insertSQL.Parameters.[COLOR=#191970][B]Add[/B][/COLOR]([COLOR=#0000ff]"'@"[/COLOR] + args[i] + [COLOR=#0000ff]"'"[/COLOR], ([COLOR=#004085][B]DbType[/B][/COLOR]) args[i + [COLOR=#00008b]2[/COLOR]]);
}
         
         [COLOR=#008000]//  Add the values[/COLOR]
         [COLOR=#0000ff][B]for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]0[/COLOR]; i < (numColumns * [COLOR=#00008b]3[/COLOR]); i += [COLOR=#00008b]3[/COLOR])
 {
                 insertSQL.Parameters.[COLOR=#191970][B]AddWithValue[/B][/COLOR]([COLOR=#0000ff]"'@"[/COLOR] + args[i] + [COLOR=#0000ff]"'"[/COLOR], args[i + [COLOR=#00008b]1[/COLOR]]);
 }
Should be (simplified to only one loop)
C#:
[COLOR=#0000ff][B]for[/B][/COLOR] ([COLOR=#ff0000][B]int[/B][/COLOR] i = [COLOR=#00008b]0[/COLOR]; i < (numColumns * [COLOR=#00008b]3[/COLOR]); i += [COLOR=#00008b]3[/COLOR])
 {
     insertSQL.Parameters.[COLOR=#191970][B]Add[/B][/COLOR]([COLOR=#0000ff]"@"[/COLOR] + args[i], ([COLOR=#004085][B]DbType[/B][/COLOR]) args[i + [COLOR=#00008b]2[/COLOR]]);
     insertSQL.Parameters.[COLOR=#191970][B]AddWithValue[/B][/COLOR]([COLOR=#0000ff]"@"[/COLOR] + args[i], args[i + [COLOR=#00008b]1[/COLOR]]);
}
The quotes around the parameter names was wrong.
Now works perfectly. Only need one insert function (per database) for an entire project.
 
Last edited:

JohnH

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
815
Location
Norway
Programming Experience
10+
insertSQL.Parameters.Add("@" + args, (DbType) args[i + 2]);
insertSQL.Parameters.AddWithValue("@" + args, args[i + 1]);

SqlLite library will here help you more than other db provider libraries, that would add two parameters with same name.
The first line is redundant, AddWithValue adds the parameter and sets DbType according to type of value, overriding any type you have set when adding the parameter first time.
If DbType can't be detected properly with AddWithValue then you have to use first approach (Add) and set Value property for that parameter, for example: .Add(name, type).Value = theValue
 

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
So you are saying replace those 2 lines with just
insertSQL.Parameters.Add("@" + args, (DbType) args[i+2]).Value = args[i+1];
Then I am guaranteed the DbType will be correct?
 

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
Thanks.
So much easier to have only 1 INSERT INTO function that handles all needs.
 
Top Bottom