无法INSERT INTO访问数据库 [英] Can't INSERT INTO access database

问题描述

我可以从Access数据库中选择数据，但是我尝试了许多方法来将数据库插入INTO.没有错误消息，但没有插入数据.

I can select the data from an Access database, but I tried many ways to INSERT INTO database. There is no error message, but it didn't insert the data.

代码:

Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurDir() & "\fsDB1.accdb")
Dim cmd As OleDbCommand 
Dim dr As OleDbDataReader

conn.Open()
Dim CommandString As String = "INSERT INTO tblfile(stdUname,filePw,filePath,status) VALUES('" & userName & "','" & filePw & "','" & filePath & "','A')"
Dim command As New OleDbCommand(CommandString, conn)
Command.Connection = conn
Command.ExecuteNonQuery()

我只想要一种简单易行的方法来插入Access数据库.是否可能由于Access数据库的问题?我可以通过直接在Access中运行查询来插入此查询.

I just want a simple easy way to INSERT INTO an Access database. Is it possible because of the problem of Access database? I can insert this query by running query directly in Access.

推荐答案

下面是一个示例，其中数据操作位于与表单代码不同的类中.

Here is an example where data operations are in a separate class from form code.

通过表单调用

Dim ops As New Operations1
Dim newIdentifier As Integer = 0
If ops.AddNewRow("O'brien and company", "Jim O'brien", newIdentifier) Then
    MessageBox.Show($"New Id for Jim {newIdentifier}")
End If

后端类，其中为AddNewRow的最后一个参数设置了新的主键，如果AddNewRow返回true，则可以使用该主键.

Back-end class where the new primary key is set for the last argument to AddNewRow which can be used if AddNewRow returns true.

Public Class Operations1
    Private Builder As New OleDbConnectionStringBuilder With
    {
        .Provider = "Microsoft.ACE.OLEDB.12.0",
        .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
    }
    Public Function AddNewRow(
        ByVal CompanyName As String,
        ByVal ContactName As String,
        ByRef Identfier As Integer) As Boolean

        Dim Success As Boolean = True
        Dim Affected As Integer = 0

        Try
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) VALUES (@CompanyName, @ContactName)"
                    cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                    cmd.Parameters.AddWithValue("@ContactName", ContactName)

                    cn.Open()

                    Affected = cmd.ExecuteNonQuery()
                    If Affected = 1 Then
                        cmd.CommandText = "Select @@Identity"
                        Identfier = CInt(cmd.ExecuteScalar)
                        Success = True
                    End If
                End Using
            End Using
        Catch ex As Exception
            Success = False
        End Try

        Return Success

    End Function
End Class

这篇关于无法INSERT INTO访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！