Can't INSERT INTO access database

问题:

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.

Code:

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()

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.

回答1:

Firstly I would check the database settings. If your app copies a new copy of the database each time you run it that would explain why you can select existing data and why your new data is not being saved (Well it is being saved, but the database keeps getting replaced with the old one). Rather set it up to COPY IF NEWER.

Further, you should ALWAYS use parameterized queries to protect your data. It is also is less error prone than string concatenated commands ans is much easier to debug.

Also, I recommend using a USING block to handle database connections so that your code automatically disposes of resources no longer needed, just in case you forget to dispose of your connection when you are done. Here is an example:

Using con As New OleDbConnection

   con.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; " & _
                       "Data Source = " 
   Dim sql_insert As String = "INSERT INTO Tbl (Code) " & _
                              "VALUES " & _ 
                              "(@code);"

   Dim sql_insert_entry As New OleDbCommand

   con.Open()

   With sql_insert_entry
      .Parameters.AddWithValue("@code", txtCode.Text)
      .CommandText = sql_insert
      .Connection = con
      .ExecuteNonQuery()
   End With

   con.Close()

End Using

回答2:

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

Calling from a form

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

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