Error on inserting sqlite DATETIME in VB.NET

Question

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 13-Jan-18 14:16pm

IamLance1578

Updated 13-Jan-18 16:42pm

Add a Solution

2 solutions

Solution 1

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.

Posted 13-Jan-18 16:06pm

Graeme_Grant

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

IamLance1578 · Accepted Answer · 2018-01-13T16:42:00

Solution 2

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

Posted 13-Jan-18 16:42pm

IamLance1578

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.

Richard Deeming 16-Jan-18 13:20pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]