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屋！