VB.Net - 使用 OleDb 将数据插入到 Access 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35759150/
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
VB.Net - Inserting data to Access Database using OleDb
提问by rfovaleris
Can you please tell me what's wrong with the code I'm using? Everytime I execute this, it throws the exception (Failed to connect to database)
你能告诉我我使用的代码有什么问题吗？每次执行此操作时，它都会引发异常（无法连接到数据库）
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim conn As New System.Data.OleDb.OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\socnet.accdb"
Dim sql As String = String.Format("INSERT INTO login VALUES('{username}','{password}','{secques}','{secans}')", txt_username.Text, txt_passwd.Text, txt_secquestion.Text, txt_secansw.Text)
Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
'Open Database Connection
sqlCom.Connection = conn
conn.Open()
Dim icount As Integer = sqlCom.ExecuteNonQuery
MessageBox.Show(icount)
MessageBox.Show("Successfully registered..", "Success", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As Exception
MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
cmd_submit2_Click(sender, e)
End If
End Sub
I am using Access 2013 and VS 2015 Community, if that helps. Thank you.
如果有帮助，我正在使用 Access 2013 和 VS 2015 Community。谢谢你。
回答by Steve
You should use a parameterized approach to your commands. A parameterized query removes the possibility of Sql Injection and you will not get errors if your string values are not correctly formatted.
您应该对命令使用参数化方法。参数化查询消除了 Sql 注入的可能性，如果您的字符串值格式不正确，您将不会收到错误消息。
Note that if you don't do anything in the exception block then it is better to remove it and let the exception show itself or at least show the Exception.Message value, so you are informed of the actual error.
请注意，如果您没有在异常块中执行任何操作，那么最好将其删除并让异常自行显示或至少显示 Exception.Message 值，以便您了解实际错误。
Finally every disposable object should be created with the Using statement that ensures a proper close and dispose of such objects (in particular the OleDbConnection)
最后，每个一次性对象都应使用 Using 语句创建，以确保正确关闭和处理此类对象（特别是 OleDbConnection）
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim sql As String = "INSERT INTO login VALUES(@name, @pass, @sec, @sw)"
Using conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\socnet.accdb")
Using sqlCom = New System.Data.OleDb.OleDbCommand(sql, conn)
conn.Open()
sqlCom.Parameters.Add("@name", OleDbType.VarWChar).Value = txt_username.Text
sqlCom.Parameters.Add("@pass", OleDbType.VarWChar).Value = txt_passwd.Text
sqlCom.Parameters.Add("@sec", OleDbType.VarWChar).Value = txt_secquestion.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = txt_secansw.Text
Dim icount As Integer = sqlCom.ExecuteNonQuery
End Using
End Using
End Sub
Keep in mind that omitting the field names in the INSERT INTO statement requires that you provide values for every field present in the login table and in the exact order expected by the table (So it is better to insert the field names)
请记住，在 INSERT INTO 语句中省略字段名称要求您为登录表中存在的每个字段提供值，并按照表预期的确切顺序提供值（因此最好插入字段名称）
For example, if your table has only the 4 known fields:
例如，如果您的表只有 4 个已知字段：
Dim sql As String = "INSERT INTO login (username, userpass, secfield, secfieldsw) " & _
"VALUES(@name, @pass, @sec, @sw)"
