[RESOLVED] Issue with SQLite DB

[DataMiser]

I have the following block of code

try
                                    {
                                        sql2 = "insert into RecordDB(time,operation,object,information) values(?,?,?,?)";
                                        sqlRejects.Parameters.Clear();
                                        sqlRejects.Parameters.Add(new SQLiteParameter("time", SqlDbType.DateTime));
                                        sqlRejects.Parameters.Add(new SQLiteParameter("op", SqlDbType.VarChar));
                                        sqlRejects.Parameters.Add(new SQLiteParameter("ob", SqlDbType.NVarChar));
                                        sqlRejects.Parameters.Add(new SQLiteParameter("inf", SqlDbType.NVarChar));
                                        sqlRejects.Parameters["time"].Value = DateTime.Now;
                                        sqlRejects.Parameters["op"].Value = Globals.pOperation;
                                        sqlRejects.Parameters["ob"].Value = Globals.pObject;
                                        sqlRejects.Parameters["inf"].Value = Globals.pInformation;
                                        sqlRejects.CommandText = sql2;
                                        sqlRejects.ExecuteNonQuery();
                                    }
                                    catch (Exception sql1e)
                                    {
                                        LogError("Error updating rejects db " + sql1e.Message);
                                    }

The table is as follows

CREATE TABLE RecordDB(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,time DATETIME NOT NULL,operation VARCHAR (50) NOT NULL,object NVARCHAR (50) NOT NULL,information NVARCHAR (100) )

When it runs I get the following error message
12:34:25 PM Friday, December 2, 2022 Error updating rejects db Invalid cast from 'DateTime' to 'Int32'.

I am just a bit confused here. Seems like that should be correct but apparently not.

Any clues?


Those Globals are all strings btw

[techgnome]

SQLite doesn't have a DateTime type ... you'll need to convert the value to an integer value before passing it in. That means you also need to convert it FROM the integer TO a DateTime.

How you go about doing all that, is up to you. But dateTime does have a method for getting the value you need. And can take that same value in its constructor (or there might be a static From method, I don't recall).

-tg

[Zvoni]

agree with tg: there is no DateTime type in SQLite (as well those other „Xxx char“ - SQLite ignores those sizes and assigns TEXT), but there is an affinity. Meaning: SQLite will still recognize it, but not assign a „hard“ type to the column.
SQLite offers 3 storage classes for DateTime: TEXT, REAL and INTEGER
Text = ‚2022-12-02 16:34:56‘ (ISO-Format)
Real = 245367.897654 (Julian day)
Integer = 1234444456777885 (Unixepoch)

i‘d guess you might have some Unix-Timestamps already saved, and „anything“ with „now“ usually returns a double
i‘d try casting that to something SQLite might recognize

In any way: i‘d go for Text/ISO-Format for the Column type, and then look for something like „DateTime.Now.AsISODATE“ or something.
i have no clue about .NET

[DataMiser]

So what I thought would be simple in the beginning turned out to be quite a PITA.
I have tried many different formats and none of them seem to want to work.

I had initially thought that since the field showed as a datetime field that datetime was the way to go but now I have no idea what should be used for that parameter. There are several different options SqlDbType.DateTime SqlDbType.DateTime2 SqlDbType.TimeStamp and others, none of which have worked with any format I have tried.

I think at this point I am just going to drop the field and be done with it.

[DataMiser]

So I dropped that time field, wasn't needed anyway and was only there to keep it the same as another source db.

Of course the code still did not work, complaining of formatting issue with the data.

So I changed the code to

sql2 = "insert into RecordDB(operation,object,information) values($op,$ob,$inf)";
                                        sqlRejects.Parameters.Clear();                                       
                                        sqlRejects.Parameters.AddWithValue("$op", Globals.pOperation);
                                        sqlRejects.Parameters.AddWithValue("$ob", Globals.pObject);
                                        sqlRejects.Parameters.AddWithValue("$inf", Globals.pInformation);
                                        sqlRejects.CommandText = sql2;
                                        sqlRejects.ExecuteNonQuery();

And it correctly inserted the data.

[Zvoni]

5587907[/URL]]So I dropped that time field, wasn't needed anyway and was only there to keep it the same as another source db.

Of course the code still did not work, complaining of formatting issue with the data.

So I changed the code to

sql2 = "insert into RecordDB(operation,object,information) values($op,$ob,$inf)";
                                        sqlRejects.Parameters.Clear();                                       
                                        sqlRejects.Parameters.AddWithValue("$op", Globals.pOperation);
                                        sqlRejects.Parameters.AddWithValue("$ob", Globals.pObject);
                                        sqlRejects.Parameters.AddWithValue("$inf", Globals.pInformation);
                                        sqlRejects.CommandText = sql2;
                                        sqlRejects.ExecuteNonQuery();

And it correctly inserted the data.

And did you try that on your time-field????
EDIT: and if you wanted to insert current system-time, you could do it from within the SQL. No need for parameter

[DataMiser]

No I actually thought about it at one point but didn't until I had removed the field.

Maybe when things settle down I'll re-visit it and see what happens for now I spent to much time on it already, The original db is populated by a printer I have made a copy and emptied it out. When I read the printer db there are some records in it that can't be processed so my intent was to write these rejects into this other db and then reprocess them later. The time field is not used by my program I simply use the current system date/time when the record gets processed so I only had that field there because it was in the original and I thought it more simple to keep that same format

Good to know about the time stamp in sql not sure if I knew that and forgot or just didn't know.

Thanks