database 如何更新 MS Access 数据库 (vb.net)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11833453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update MS Access Database (vb.net)
提问by Richard Paulicelli
I am created a journal program for an internship project and I am using a MS-Access Database. I am programming in VB.net. Now, I am trying to make it so that they can "Update" their journals, meaning that they click on their calendar date and it brings them to that journal if they have one for that date. If they have one for that date then it shows the title and journal text entry for that date. I want to make it so that any changes they have made to the journal (editing the textbox fields) are also changed in the database when they click the update button. Here's what i have so far
我为实习项目创建了一个期刊程序，并且我正在使用 MS-Access 数据库。我在VB.net 中编程。现在，我试图让他们可以“更新”他们的日记，这意味着他们点击日历日期，如果他们有那个日期的日记，它就会把他们带到那个日记。如果他们在那个日期有一个，那么它会显示该日期的标题和日记文本条目。我想让他们在点击更新按钮时对日志所做的任何更改（编辑文本框字段）也会在数据库中更改。这是我到目前为止所拥有的
Private Sub COE_JournalBtn_Click(sender As System.Object, e As System.EventArgs) Handles COE_JournalBtn.Click
If DateTimePicker1.Value <> Nothing Then
If TitleTxt.Text <> "" Then
If JournalTextRtxt.Text <> "" Then
myConnection.Open()
Dim DatePicked As String = DateTimePicker1.Value
Dim cmd As OleDbCommand
Dim str As String
Try
MyJournalTitle = TitleTxt.Text
MyJournalText = JournalTextRtxt.Text
str = "UPDATE Journals SET JournalTitle='" & MyJournalTitle & "', JournalText='" & MyJournalText & "' WHERE JournalDate=" & DatePicked
cmd = New OleDbCommand(str, myConnection)
cmd.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show("There was an error processing your request. Please try again." & vbCrLf & vbCrLf & _
"Original Error:" & vbCrLf & vbCrLf & ex.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
myConnection.Close()
End Try
myConnection.Close()
End If
End If
End If
End Sub
Now my update string by itself is
现在我的更新字符串本身是
"UPDATE Journals SET JournalTitle='" & MyJournalTitle & "', JournalText='" & MyJournalText & "' WHERE JournalDate=" & DatePicked
Now, what happens, is absolutely nothing. No errorboxes come up. No messageboxes appear. The program doesn't freeze. And the database remains unchanged. What am I doing wrong? Is there an error in my code or something missing? Please help me because I really want to figure this out and i've been looking everywhere for a solution in VB.net and cannot find one that applies to me being that I am using MS-Access and NOT SQL.
现在，发生的事情，绝对不是什么。没有错误框出现。没有消息框出现。该程序不会冻结。并且数据库保持不变。我究竟做错了什么？我的代码中是否有错误或遗漏了什么？请帮助我，因为我真的很想弄清楚这一点，我一直在到处寻找 VB.net 中的解决方案，但找不到适用于我的解决方案，因为我使用的是 MS-Access 而不是 SQL。
Thanks in advance, Richard Paulicelli
提前致谢，理查德·保利切利
回答by Steve
Use a parametrized query to avoid Sql Injection Attacks and quoting problems
使用参数化查询避免 Sql 注入攻击和引用问题
str = "Journals SET JournalTitle=?, JournalText=? WHERE JournalDate=?"
cmd = New OleDbCommand(str, myConnection)
cmd.Parameters.AddWithValue("@jounalTitle", MyJournalTitle )
cmd.Parameters.AddWithValue("@journalText", MyJournalText)
cmd.Parameters.AddWithValue("@journalDate", DatePicked)
cmd.ExecuteNonQuery()
Using parameters will free your code from that continue quoting that expose your code to an high chance of typing errors. And you don't have any problem with Sql Injection
使用参数将使您的代码免于继续引用，从而使您的代码有很高的输入错误机会。并且您对Sql 注入没有任何问题
回答by HansUp
You may have a problem with this part of your UPDATEstatement:
你的这部分UPDATE陈述可能有问题：
"' WHERE JournalDate=" & DatePicked
If the Journals field, JournalDate, is Date/Time data type, surround the date string value with Access date delimiter characters (#).
如果日记帐字段JournalDate是日期/时间数据类型，请使用 Access 日期分隔符 ( #)将日期字符串值括起来。
"' WHERE JournalDate=#" & DatePicked & "#"
You can also convert your date string to yyyy-mm-dd format to avoid misinterpreting the date literal based on local.
您还可以将日期字符串转换为 yyyy-mm-dd 格式，以避免基于本地错误解释日期文字。
I agree with the suggestions to use a parameter query for this instead. I'm just trying to help you understand why the original attempt may have failed.
我同意使用参数查询的建议。我只是想帮助您理解为什么最初的尝试可能会失败。
