OleDbException 未处理....... UPDATE 语句中的语法错误 [英] OleDbException was unhandled.......Syntax error in UPDATE statement
问题描述
我在调试时遇到该错误，请有人帮忙吗...代码如下:
I get that Error when i debug please can someone help please... Below is the code:
Private Sub UpdateToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateToolStripMenuItem.Click
If MsgBox("Save Changes?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "WARNING") = MsgBoxResult.Yes Then
Dim oleDC As New OleDbCommand
With oleDC
.Connection = conn
.CommandText = "UPDATE tblPatientsRecord SET Names='" & txtNames.Text & _
"',Licensenumber='" & txtLicensenumber.Text & _
"',Address='" & txtAddress.Text & _
"',Fullname='" & txtFullname.Text & _
"',Birthday='" & txtBase.Text &
"',Age='" & txtAge.Text & _
"',Country='" & cmbCountry.Text & "' WHERE PatientID='" & txtPatientID.Text & "'"
.ExecuteNonQuery()
MsgBox("Record Updated!", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "SUCCESS")
Disable()
Clear()
SaveToolStripMenuItem.Enabled = False
NewToolStripMenuItem.Enabled = True
LoadPatientsRecord()
getLastPatientID()
End With
End If
End Sub
请帮忙
推荐答案
您应该使用 SQL 参数.这些不仅会简化您的代码，还会使某些类型的语法和数据类型错误极发生并防止 SQL 注入攻击:
You should use SQL parameters. These will not only simplify your code, they will make certain kinds of errors regarding syntax and data types extremely unlikely and protect against SQL injection attacks:
Dim sql = <sql>
UPDATE tblPatientsRecord SET [Names] = @p1,
Licensenumber = @p2,
Address = @p3,
Fullname = @p4,
Birthday = @p5,
[Age] = @p6,
Country = @p7
WHERE PatientID = @p8
</sql>.Value
Using conn = New OleDbConnection(myConnStr),
cmd As New OleDbCommand(sql, conn)
conn.Open()
cmd.Parameters.Add("@p1", OleDbType.VarChar).Value = txtNames.Text
cmd.Parameters.Add("@p2", OleDbType.VarChar).Value = txtLicensenumber.Text
' ...etc
cmd.Parameters.Add("@p6", OleDbType.Integer).Value = intVar
cmd.Parameters.Add("@p7", OleDbType.VarChar).Value = strVar
'the last one is the WHERE
cmd.Parameters.Add("@p8", OleDbType.VarChar).Value = Convert.ToInt32(lblPatientID.Text)
cmd.ExecuteNonQuery()
'... etc
End Using
还有一些其他常见的问题需要注意.
There are several other commonly seen issues which should be tended to.
DBConnection 对象旨在创建、使用和处理，而不是反复使用的同一个对象.但是，您可以使用全局连接字符串，这样您就不会到处都有相同的连接字符串.
DBConnection objects are intended to be created, used and disposed of rather than the same one used over and over. However, you can use a global connection string so you don't have the same connection string all over the place.
许多DBObjects 应该被处理.Using 块将关闭并处理连接和命令对象.通常，如果某些东西具有 Dispose 方法，请将它们包装在 Using 块中.上面显示了如何将 2 个对象(OleDbConnection 和 OleDbCommand)堆叠"到一个 Using 语句中，以减少缩进.
Many of the DBObjects should be disposed of. Using blocks will close and dispose of the connection and command objects. Generally, if something has Dispose method, wrap them in a Using block. The above shows how to "stack" 2 objects (OleDbConnection and OleDbCommand) into one Using statement which reduces indentation.
使用 Add 方法而不是 AddWithValue.这允许您为每个参数指定数据类型.如果没有它，数据库提供者必须猜测哪些会导致 Datatype mismatch 甚至在某些情况下损坏数据库.
Use the Add method rather than AddWithValue. This allows you to specify the datataype for each parameter. Without it, the DB Provider must guess which can result in Datatype mismatch or even corrupt the database in some instances.
WHERE 子句只是另一个参数.通常人们会在 SQL 的第一部分使用参数，但不必要地为 WHERE 子句连接.
The WHERE clause is just another parameter. Often people will use Parameters for the first part of the SQL but concatenate for the WHERE clause needlessly.
上面还使用了 XML 文字来构造 SQL.这对于长而复杂的 SQL 很方便，因为您可以随意格式化和缩进它.您也可以使用多行使其可读:
The above also uses an XML literal to construct the SQL. This is handy for long, complex SQL because you can format and indent it as you like. You can also just use multiple lines to make it readable :
Dim sql = "UPDATE tblPatientsRecord SET [Names] = @p1," _&"许可证编号 = @p2," _&"地址 = @p3, "
如果您使用 SQL 保留字或表名或列名中的空格，您必须使用 [方括号] 对名称进行转义，如图所示.最好不要在名称中使用任何一个.User、Password Names 和 Values 是常见的词，用作列名或表名，导致 SQL 语法错误.
If you use SQL reserved words or spaces in table or column names, you must escape the names using [Square Brackets] as shown. It is best not to use either in the names. User, Password Names and Values are commonly seen words used as column or table names which result in SQL syntax errors.
刻度不是通用的 SQL 字段分隔符，它们实际上表示传递的值是字符串/文本:&"年龄 ='" &txtAge.Text &'".如果 DB 设置为将 Age 存储为数字，则 SQL 将其作为文本/字符串传递，这可能会导致 数据类型不匹配.如果 PatientID 和 Birthday 是日期字段，则同样如此.
Ticks are not all-purpose SQL field delimiters, they actually indicate that the value being passed is string/text: & "Age ='" & txtAge.Text & "'". If the DB is set to store Age as a number, your SQL is passing it as text/string which can result in a data type mismatch. The same is true of PatientID and Birthday if it is a date field.
连接 SQL 语句的字符串的一个常见问题是结果中的刻度 (') 过多或过少.SQL 参数不会发生这种情况.
A common problem concatenating strings for a SQL statements is too many or too few ticks (') in the result. This cant happen with SQL Parameters.
SQL 参数的主要目的是防止名称为O'Brian"或O'Reilly"或 "Bobby';DROP TABLE tblPatientsRecord"
The main purpose for SQL Parameters, though is to prevent an error if the name is "O'Brian" or "O'Reilly" or "Bobby';DROP TABLE tblPatientsRecord"
这些原则适用于其他数据库提供程序，例如MySql、SQLite 和SQLServer.但是，确切的转义字符等细节会有所不同.
These principles apply for other DB providers such asMySql, SQLite and SQLServer. The details such as the exact escape character however will vary.
请注意，Access/OleDB 实际上并不使用命名参数(@FirstName 甚至 @p2)，因此通常会以?"的形式查看参数.这意味着您必须添加参数值 (Add/AddWithValue)以与这些列在 SQL 中出现的完全相同的顺序.
Note that Access/OleDB doesn't actually use named parameters as such (@FirstName or even @p2), so will often see params in the form of "?". This means that you must add the parameter values (Add/AddWithValue) in the same exact order as those columns appear in the SQL.
有关更多信息，请参阅:
For more information see:
这篇关于OleDbException 未处理....... UPDATE 语句中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
