sql - Can't INSERT INTO access database

Question

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.

Answer 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