Results 1 to 7 of 7

Thread: [RESOLVED] Issue with SQLite DB

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,151

    Resolved [RESOLVED] Issue with SQLite DB

    I have the following block of code
    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
    Code:
    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

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,066

    Re: Issue with SQLite DB

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,569

    Re: Issue with SQLite DB

    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
    Last edited by Zvoni; Dec 2nd, 2022 at 03:25 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,151

    Re: Issue with SQLite DB

    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.

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,151

    Re: Issue with SQLite DB

    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

    Code:
    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.

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,569

    Re: Issue with SQLite DB

    Quote Originally Posted by DataMiser;[URL="tel:5587907"
    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

    Code:
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,151

    Re: Issue with SQLite DB

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width