VB.Net - 使用 OleDb 将数据插入到 Access 数据库 [英] VB.Net - Inserting data to Access Database using OleDb

问题描述

你能告诉我我使用的代码有什么问题吗?每次执行此操作时，它都会抛出异常(无法连接到数据库)

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

如果有帮助，我正在使用 Access 2013 和 VS 2015 社区.谢谢.

I am using Access 2013 and VS 2015 Community, if that helps. Thank you.

推荐答案

您应该对命令使用参数化方法.参数化查询消除了 Sql 注入的可能性，如果您的字符串值格式不正确，您将不会收到错误消息.

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.

请注意，如果您未在异常块中执行任何操作，则最好将其删除并让异常自行显示或至少显示 Exception.Message 值，以便您了解实际错误.

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.

最后，每个一次性对象都应使用 Using 语句创建，以确保正确关闭和处理此类对象(特别是 OleDbConnection)

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)

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

请记住，在 INSERT INTO 语句中省略字段名称要求您为登录表中存在的每个字段提供值，并按照表预期的确切顺序提供值(因此最好插入字段名称)

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)

例如，如果您的表只有 4 个已知字段:

For example, if your table has only the 4 known fields:

Dim sql As String = "INSERT INTO login (username, userpass, secfield, secfieldsw) " & _ 
                    "VALUES(@name, @pass, @sec, @sw)"

这篇关于VB.Net - 使用 OleDb 将数据插入到 Access 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！