在VB.NET中插入sqlite DATETIME时出错 [英] Error on inserting sqlite DATETIME in VB.NET

问题描述

插入SQLite DB的数据样本（.dat文件）

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

TABLE "tblTempData" ( `fldMacId` INTEGER, `fldDateTime` TEXT, `fldRem1` TEXT, `fldRem2` TEXT, `fldRem3` TEXT, `fldRem4` TEXT )

这是我的循环

This is my loop

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

cmd.Parameters.Add("@xDateTime", FormatDateTime(vArray(1), DateFormat.GeneralDate))

推荐答案

您应该使用 DATETIME 而不是 TEXT 。现在你不需要施放日期。



另一件事，在处理日期和时间时，是使用协调世界时（UTC）。这样，如果应用程序在另一个时区使用，日期和时间将是正确的。为此，将; DateTimeKind = Utc 添加到连接字符串。

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.

我发现了错误。

SQLite接受以下格式的数据

I found out the error.
The SQLite accepts data in the following format

41,"2017-09-07 17:01:09",1,1,1,0

所以我对代码进行了更改INSERT INTO

So i made changes in code INSERT INTO

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

这篇关于在VB.NET中插入sqlite DATETIME时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！