提问人：Meme 提问时间：3/2/2023 最后编辑：Joel CoehoornMeme 更新时间：3/3/2023 访问量：31
我不断收到错误号或查询值，并且目标字段不一样
i keep getting the error number or query values and destination fields are not the same
问：
我真的需要帮助才能看到问题所在
Private Sub btnSaveData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveData.Click
Try
conn.Open()
cmd = conn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "insert into vbsavedata([Student ID, First Name, Surname, Address, Postcode, Telephone, Grade, Section, Specialization])VALUES('" + tbstudentid.Text + "','" + tbfirstname.Text + "','" + tbsurname.Text + "','" + tbaddress.Text + "','" + tbpostcode.Text + "','" + tbtelephone.Text + "','" + tbgrade.Text + "','" + tbsection.Text + "','" + tbspecialization.Text + "')"
cmd.ExecuteNonQuery()
MessageBox.Show("Record Saved MS Access", "VB Save Database", MessageBoxButtons.OK, MessageBoxIcon.Information
)
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "VB Save Database", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
我真的看不出有什么问题，请帮忙
答：
0赞
dbasnett
3/2/2023
#1
FWIW - 如果，这是一个很大的 IF，我使用文本命令而不是 SPROC，我发现使用 XElement 很有帮助，主要是因为嵌入了表达式。
Dim mycommand As New SqlCommand
mycommand.CommandType = CommandType.Text
Dim cmd As XElement
' the <v> nodes use embedded expressions
cmd = <cmd>
<ins>insert into tbl_cus(name,class,phone) </ins>
<vals>values (<v>'<%= TextBox1.Text %>',</v>
<v>'<%= TextBox2.Text %>',</v>
<v>'<%= TextBox3.Text %>'</v>);</vals>
</cmd>
mycommand.CommandText = cmd.Value
' mycommand.CommandText
' insert into tbl_cus(name,class,phone) values ('TB val one','TB val two','TB val three');
0赞
Joel Coehoorn
3/2/2023
#2
只看SQL的这一部分，我们就可以看到问题所在：
into vbsavedata([Student ID, First Name,
左方括号 （） 从不闭合，甚至从未尝试过其他多字列名称。[
更糟糕的是，这段代码非常容易受到 SQL 注入问题的影响。使用字符串连接将用户值构建到 SQL 语句中是绝对不行的！您应该花几分钟时间阅读如何对 MS Access 进行参数化查询。
最后，Connection 的使用还有一个问题。尝试在整个应用程序中重复使用相同的连接对象通常是一个坏主意。这实际上使事情变慢了，因为它干扰了 ADO.Net 中现有的连接池功能。更糟糕的是，如果运行查询时出现异常，则将跳过调用。相反，通常的做法是将几乎每个查询都创建为块的一部分创建一个新连接，这将确保即使在异常的情况下也能正确关闭连接。conn.Close()Using
换句话说，你需要更多这样的东西：
Private Sub btnSaveData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveData.Click
Dim SQL As String = "INSERT INTO vbsavedata([Student ID], [First Name], Surname, Address, Postcode, Telephone, Grade, Section, Specialization]) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
Try
Dim conn As New OleDbConnection("connection string here")
Dim cmd As New OleDbCommand(SQL, conn)
' Match up with the actual column types and lengths from the database
cmd.Parameters.Add("Student ID", OleDbType.VarChar, 8).Value = tbstudentid.Text
cmd.Parameters.Add("First Name", OleDbType.VarWChar, 15).Value = tbfirstname.Text
cmd.Parameters.Add("Surname", OleDbType.VarWChar, 20).Value = tbsurname.Text
cmd.Parameters.Add("Address", OleDbType.VarWChar, 60).Value = tbaddress.Text
cmd.Parameters.Add("PostCode", OleDbType.VarChar, 10).Value = tbpostcode.Text
cmd.Parameters.Add("Telephone", OleDbtype.VarChar, 14).Value = tbtelephone.Text
cmd.Parameters.Add("Grade", OleDbType.VarChar, 3).Value = tbgrade.Text
cmd.Parameters.Add("Section", OleDbType.VarChar, 12).Value = tbsection.Text
cmd.Parameters.Add("Specialization", OleDbType.VarWChar, 40).Value = tbspecialization.Text
conn.Open()
cmd.ExecuteNonQuery()
MessageBox.Show("Record Saved MS Access", "VB Save Database", MessageBoxButtons.OK, MessageBoxIcon.Information
Catch ex As Exception
MessageBox.Show(ex.Message, "VB Save Database", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally ' Using blocks are more common, but in the case where you already have a Try/Catch closing the in Finally block is also okay
conn.Dispose()
End Try
End Sub
评论