Click here to Skip to main content
15,917,005 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Sample of data being inserted to SQLite DB (.dat file)
41 2017-09-07 17:01:09 1   1   1   0
      27   2017-09-07 17:01:13 1   1   1   0
      42   2017-09-07 17:01:31 1   1   1   0
      46   2017-09-07 17:04:05 1   1   1   0
      44   2017-09-07 17:04:12 1   1   1   0
      45   2017-09-07 17:04:16 1   1   1   0
      47   2017-09-07 17:05:09 1   1   1   0
      17   2017-09-07 17:05:19 1   1   1   0
      29   2017-09-07 17:05:47 1   1   1   0
      35   2017-09-07 17:09:14 1   1   1   0
       8   2017-09-07 17:09:22 1   1   1   0

This is my Table
SQL
TABLE "tblTempData" ( `fldMacId` INTEGER, `fldDateTime` TEXT, `fldRem1` TEXT, `fldRem2` TEXT, `fldRem3` TEXT, `fldRem4` TEXT )


This is my loop
VB
Dim InsertData As String = "INSERT INTO tblTempData (fldMacId,fldDateTime,fldRem1,fldRem2,fldRem3,fldRem4) Values (@MacId,@xDateTime,@Rem1,@Rem2,@Rem3,@Rem4)"
Do
                sLine = Filereader.ReadLine
                If sLine Is Nothing Then Exit Do
                Dim vArray() As String = sLine.Split(CType(vbTab, Char()))
                Dim cmd As New SQLiteCommand(InsertData, MyConn)
                cmd.Parameters.Add("@MacId", CInt(vArray(0)))
                cmd.Parameters.Add("@xDateTime", CStr(vArray(1))) <- This cause an error
                cmd.Parameters.Add("@Rem1", CStr(vArray(2)))
                cmd.Parameters.Add("@Rem2", CStr(vArray(3)))
                cmd.Parameters.Add("@Rem3", CStr(vArray(4)))
                cmd.Parameters.Add("@Rem4", CStr(vArray(5)))
                cmd.ExecuteNonQuery()
                MsgBox(InsertData)
            Loop

This is the error
Conversion from string "9/7/2017 5:01:09 PM" to type 'Integer' is not valid.


What I have tried:

I try this code but no luck
VB
cmd.Parameters.Add("@xDateTime", FormatDateTime(vArray(1), DateFormat.GeneralDate))
Posted
Updated 13-Jan-18 16:42pm

You should be using DATETIME instead of TEXT. Now you don't need to cast the date.

The other thing, when working with dates and times, is to use Universal Time Coordinated (UTC). That way, if the app is used in another timezone, the date and time will be correct. To do this add ;DateTimeKind=Utc to the connection string.
 
Share this answer
 
I found out the error.
The SQLite accepts data in the following format
41,"2017-09-07 17:01:09",1,1,1,0

So i made changes in code INSERT INTO
VB
<pre>strSQL = "INSERT INTO tblTempData (fldMacId,fldDateTime,fldRem1,fldRem2,fldRem3,fldRem4) Values " &
                    "(" & CInt(vArray(0)) &
                    ",'" & CStr(vArray(1)) &
                    "','" & CStr(vArray(2)) &
                    "','" & CStr(vArray(3)) &
                    "','" & CStr(vArray(4)) &
                    "','" & CStr(vArray(5)) & "')"



and it works...
 
Share this answer
 
Comments
Dave Kreskowiak 14-Jan-18 0:17am    
Yeah, again, another really bad habit. You're saving date/time values as strings in your database. That's wrong. It should be stored in a date/time column in the database, not as a string, EVER.
IamLance1578 14-Jan-18 1:56am    
@Dave Kreskowiak

Yes, you're right if I'm using ms access or MySQL I will be using date type as date, however, I'm using SQLite. The way I know SQLite doesn't have Date types. It only has NULL. INTEGER, REAL, TEXT, and BLOB. SQLite can store dates and time as TEXT, REAL or Integer as stated in https://www.sqlite.org/datatype3.html unless I was wrong :-)

Thanks for your time. I'm looking forward to your constructive comments in the future. You did a great help to me in solving my previous problem and I really appreciate it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900