语法错误插入语句vba 2022

Question

我试过很多方法，但我总是犯错误。

这个脚本使用

Module Module1
Public usertype As String
Public userId As String
Public result As String
Public sql As String
Public cmd As New OleDbCommand
Public dt As New DataTable
Public da As New OleDb.OleDbDataAdapter
Public Function myDBmodule() As OleDb.OleDbConnection
Return New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
Application.StartupPath & "db_billing.accdb;Persist Security Info=True")
End Function
Public con As OleDb.OleDbConnection = myDBmodule()
Public strcon As OleDbConnection = myDBmodule()
Public Sub myselectstatements(ByVal sql As String, ByVal dtg As DataGridView)
Try
con.Open()
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, con)
Dim dt As New DataTable
da.Fill(dt)
dtg.DataSource = dt
Catch ex As Exception
MsgBox(ex.Message)
End Try
con.Close()
' Form1.TextBox1.Text = Total().ToString("c")
End Sub
Public Sub mysql(ByVal sql As String)
Try
con.Open()
da = New OleDb.OleDbDataAdapter(sql, con)
dt = New DataTable
da.Fill(dt)
Catch ex As Exception
MsgBox(ex.Message)
Return
End Try
con.Close()
End Sub

Try
If Label82.Text = "Desktop Detail" Then
sql = "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('" & ComboBox6.Text & "','" & TxtFA.Text & "','" & fatxt.Text & "','" &
TextBox3.Text & "','" & TextBox2.Text & "','" & TextBox4.Text _
& "','" & TextBox5.Text & "','" & TextBox7.Text _
& "','" & TextBox6.Text & "','" & ComboBox1.Text _
& "','" & TextBox1.Text & "','" & ComboBox2.Text _
& "','" & TextBox8.Text & "','" & TextBox11.Text _
& "','" & TextBox9.Text & "','" & ComboBox3.Text _
& "','" & ComboBox4.Text & "','" & TextBox10.Text _
& "','" & TextBox12.Text & "','" & IDNUMBER.Text _
& "','" & ComboBox5.Text & "')"
mysql(sql)
MsgBox("Customer Save", , "Save")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try

Answer 1

第一个问题是插入列表中有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)