语法错误 insert into 语句 VBA 2022

第一个问题是插入列表中有 22 个项目，但只替换了 21 个值。

第二个问题是，该方法至少在三个不同的方面存在根本缺陷。你希望它看起来更像这样：mysql()

Public Function RunSQL(SQL As String, Parameters As IEnumerable(Of OleDbParameter)) As DataTable
    ' Actually return the data, instead of using a global variable
    Dim result As New DataTable()

    ' Thanks to **connection pooling**, it really is better to 
    '  create new connection and command objects for every query.
    Using con As New OleDbConnection("connection string here"), _
          cmd As New OleDbCommand(SQL, con)

        ' This fixes the nasty SQL injection issue, which was a HUGE problem before
        If Parameters IsNot Nothing Then
            For Each p As OleDbParameter In Parameters
               cmd.Parameters.Add(p)
            Next
        End If

        con.Open()
        Using rdr As OleDbDataReader = cmd.ExecuteReader()
           result.Load(rdr)
           rdr.Close()
        End Using
    End Using
    Return result
End Function

那么你可以这样称呼它（注意：我不能完美地演示它，因为我们缺少要包含的值）：

' String literals can cross multiple lines now
Dim sql As String = "
INSERT INTO table1 
 (PHASE, FA_TAGGING, IT_TAGGING, DESKTOP_MODEL, SN, WARRANTY_START, WARRANTY_EXPIRED, RAM, 
  STORAGE, MONITOR_MODEL, MONITOR_SN, UPS_MODEL, UPS_SN, UPS_WARRANTY_STARTS,
  UPS_WARRANTY_EXPIRED, LOCATION, DEPARTMENT, COST_CENTRE, USERNAME, EMPNO, POSITION)
VALUES 
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
  
' Parameter values are mapped to the ? placeholders based on the orders they appear
' in the SQL string/parameters collection.
' So the names don't matter here... it just helpful to ensure we match them up right.
' This is *even better* if we also set type information (on some platforms, there can be a huge performance penalty if we don't).
Dim Parameters As New List(Of OleDbParameter) From {
    New OleDbParameter("Phase", ComboBox6.Text),
    New OleDbParameter("FA_Tagging", TxtFA.Text),
    New OleDbParameter("IT_Tagging", fatxt.Text),
    New OleDbParameter("DESKTOP_MODEL", TextBox3.Text),
    New OleDbParameter("SN", TextBox2.Text),
    New OleDbParameter("WARRANTY_START", DateTime.Parse(TextBox4.Text)),
    New OleDbParameter("WARRANTY_EXPIRED", DateTime.Parse(TextBox5.Text)),
    New OleDbParameter("RAM", TextBox7.Text),
    New OleDbParameter("DEPARTMENT", TextBox6.Text),
    New OleDbParameter("STORAGE", ... ), ' I suspect the missing value is near here or just above
    New OleDbParameter("MONITOR_MODEL", ComboBox1.Text ), 
    New OleDbParameter("MONITOR_SN", TextBox1.Text ), 
    New OleDbParameter("UPS_MODEL", ComboBox2.Text),
    New OleDbParameter("UPS_SN", TextBox8.Text),
    New OleDbParameter("UPS_WARRANTY_STARTS", DateTime.Parse(TextBox11.Text)),
    New OleDbParameter("UPS_WARRANTY_EXPIRED",DateTime.Parse(TextBox9.Text)),
    New OleDbParameter("LOCATION", ComboBox3.Text),
    New OleDbParameter("DEPARTMENT", ComboBox4.Text),
    New OleDbParameter("COST_CENTRE", TextBox10.Text),
    New OleDbParameter("USERNAME", TextBox12.Text),
    New OleDbParameter("EMPNO",Integer.Parse(IDNUMER.Text)),
    New OleDbParameter("POSITION",ComboBox5.Text)
}

dt = RunSQL(SQL, Parameters)