这是我的代码我试图通过VB表格更新表，我不知道它有什么问题请帮助我。
这是表：
Dim con As New OleDbConnection("provider=microsoft.ace.oledb.12.0; data source = |datadirectory|\Studen.accdb;") con.Open() Dim sql As String = "Update tend set StudentName='" & TextBox9.Text & "', LessonDate='" & TextBox13.Text & "', LessonTime=" & TextBox10.Text & ", Payment=" & TextBox11.Text & ", Note='" & TextBox12.Text & "' where ID=" & TextBox8.Text Dim cmd As New OleDbCommand(sql, con) cmd.ExecuteNonQuery() con.Close()This is my code i'm trying to update table via VB forms , I don't know what the wrong with it please help me.
This is the table:
Dim con As New OleDbConnection("provider=microsoft.ace.oledb.12.0; data source = |datadirectory|\Studen.accdb;") con.Open() Dim sql As String = "Update tend set StudentName='" & TextBox9.Text & "', LessonDate='" & TextBox13.Text & "', LessonTime=" & TextBox10.Text & ", Payment=" & TextBox11.Text & ", Note='" & TextBox12.Text & "' where ID=" & TextBox8.Text Dim cmd As New OleDbCommand(sql, con) cmd.ExecuteNonQuery() con.Close()最满意答案
第一个问题是名为NOTE的字段。 这是MS-Access中的保留关键字，如果要使用它，则需要用方括号封装该单词
Dim sql As String = "Update tend set StudentName=...., [Note]=..."但这不是唯一的问题。 更大的一个是用于构建sql命令的字符串连接。 这种方法导致可能的sql注入和属性引用用于准备语句的值的问题。 需要检查字符串以复制单引号，小数需要以适当的小数点传递，日期需要封装在＃符号中等等.... 更好的方法是使用参数化查询
Dim sql As String = "Update tend set StudentName=?, LessonDate=?, LessonTime=?, " & _ "Payment=?, [Note]=? where ID=?" Using con = New OleDbConnection(...........) Using cmd = New OleDbCommand(sql, con) con.Open() cmd.Parameters.AddWithValue("@p1", TextBox9.Text) cmd.Parameters.AddWithValue("@p2", Convert.ToDate(TextBox13.Text)) cmd.Parameters.AddWithValue("@p3", Convert.ToInt32(TextBox10.Text)) cmd.Parameters.AddWithValue("@p4", Convert.ToDecimal(TextBox11.Text)) cmd.Parameters.AddWithValue("@p5", TextBox12.Text) cmd.Parameters.AddWithValue("@p6", Convert.ToInt32(TextBox8.Text)) cmd.ExecuteNonQuery() End Using End Using在参数化查询中，如上所述，您将占位符（？）放在查询文本中，并使用命令的Parameters集合提供值。 通过这种方式，正确引用每个值的工作将传递给框架和数据库引擎。 他们比你和我更了解如何正确引用参数。
请注意AddWithValue方法如何推断出正确的数据类型，以用于查看传递的值的数据类型的参数。 如果LessonDate字段是具有DateTime类型的字段，则需要将文本框文本（字符串）转换为日期。 如果您在尝试转换之前未进行检查，则可能会导致异常。 （这里我假设你有一些确保有效输入的东西）。 相同的推理应该应用于其他非文本字段。 （身份证，课程时间，付款）
The first problem is the field named NOTE. This is a reserved keyword in MS-Access and, if you want to use it, you need to encapsulate the word with square brackets
Dim sql As String = "Update tend set StudentName=...., [Note]=..."but this is not the only problem here. A much bigger one is the string concatenation used to build the sql command. This approach leads to possible sql injections and problems in propertly quoting the values used to prepare the statement. Strings need to be examined to duplicate single quotes, decimals need to be passed with the proper decimal point, dates need to be encapsulated in the # symbol and so on.... A better way is using a parameterized query
Dim sql As String = "Update tend set StudentName=?, LessonDate=?, LessonTime=?, " & _ "Payment=?, [Note]=? where ID=?" Using con = New OleDbConnection(...........) Using cmd = New OleDbCommand(sql, con) con.Open() cmd.Parameters.AddWithValue("@p1", TextBox9.Text) cmd.Parameters.AddWithValue("@p2", Convert.ToDate(TextBox13.Text)) cmd.Parameters.AddWithValue("@p3", Convert.ToInt32(TextBox10.Text)) cmd.Parameters.AddWithValue("@p4", Convert.ToDecimal(TextBox11.Text)) cmd.Parameters.AddWithValue("@p5", TextBox12.Text) cmd.Parameters.AddWithValue("@p6", Convert.ToInt32(TextBox8.Text)) cmd.ExecuteNonQuery() End Using End UsingIn a parameterized query, like the one above, you put placeholders (?) in the query text and supply the values with the Parameters collection of the command. In this way, the work to properly quote every single value is passed to the framework and db engine. They know better than you and me how to properly quote the parameters.
Note how the AddWithValue method infers the correct datatype to use for the parameter looking at the datatype of value passed. If your LessonDate field is a field with DateTime type then you need to convert the textbox text (a string) to a date. This could cause an exception if you don't check before trying the conversion. (Here I assume that you have something in place to ensure valid inputs). The same reasoning should be applied to the other NON text fields. (ID, LessonTime, Payment)
更多推荐
发布评论