VB.NET Insert Data into MS Access DB

Question

This is my first attempt at working with .NET and a database.

I am trying to add records to a table but nothing is being added. I have stripped it down to just the basic code below.

No errors are generated but nothing is added to the table.

Imports System.Data
'Imports System.Data.OleDb
Class Form1
    Dim dbProvider As String
    Dim dbSource As String

    Dim dbPathAndFilename As String
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" ' MDB
        'dbProvider = "PROVIDER=Microsoft.Ace.OLEDB.12.0;" 'ACCDB
        dbSource = "Data Source="

        LoadData()
    End Sub

    Sub LoadData()
        'Connect to db
        'You could store the db path in the Settings of the App.
        'dbPathAndFilename = My.Settings.dbPath
        dbPathAndFilename = "C:\temp\VB\DBTest\Test.mdb"
        con.ConnectionString = dbProvider & dbSource & dbPathAndFilename

        con.Open()
        sql = "INSERT INTO Table1(Field1) VALUES('Field1');"
        da = New OleDb.OleDbDataAdapter(sql, con)
        con.Close()
    End Sub
End Class

your missing the actual execution of your query: cmd.ExecuteNonQuery() https://social.msdn.microsoft.com/Forums/vstudio/en-US/d11ed406-d9bf-4363-84b6-99f4e9ee4631/how-to-insert-data-into-sql-table-using-vbnet?forum=vbgeneral — Brad, Mar 14 '19 at 17:42
Also, IMHO look into wrapping the disposable objects in `using` statements; this ensures they are getting properly disposed of when done. — Trevor, Mar 14 '19 at 17:59
I went back to where I copied the initial code from and found what I had cut out. Now getting "Object variable or With block variable not set" — Rockb, Mar 14 '19 at 18:34
Updated based on the https://www.599cd.com/tips/vbdotnet/140826_ConnectToAccess/ and it is now working. — Rockb, Mar 14 '19 at 18:56
Your linked code queries data from the database, but your query text attempts to insert. You would need a different object to do that. See https://stackoverflow.com/questions/10941284/how-to-insert-a-record-into-a-access-table-using-oledb — djv, Mar 14 '19 at 19:00

score 0 · Answer 1 · answered Mar 14 '19 at 19:11

Try this

Class Form1
    Private dbProvider As String
    Private dbPathAndFilename As String

    Sub LoadData()
        dbProvider = "Microsoft.Jet.OLEDB.4.0;"
        dbPathAndFilename = "C:\temp\VB\DBTest\Test.mdb"
        Using con As New OleDb.OleDbConnection($"PROVIDER={dbProvider};DATA SOURCE={dbPathAndFilename}")
            con.Open()
            Using cmd = con.CreateCommand()
                cmd.CommandText = "INSERT INTO Table1([Field1]) VALUES(@field1);"
                cmd.Parameters.AddWithValue("@field1", field1Value) ' replace field1Value with your value
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

End Class

It has a few advantages over your version.

It limits scope of disposable objects with Using blocks
It uses a parameter which is preferred to avoid injection
I felt the connection string was a little clunky and this neatens it up a bit
Most importantly, the Command is introduced so ExecuteNonQuery can be called, in contrast to your linked example which returns data in a query.

score 0 · Answer 2 · answered Mar 29 '19 at 00:04

Imports System.Data
Imports System.Data.OleDb

Class Form1
    Dim dbProvider As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" ' MDB
    Dim dbSource As String = "Data Source="
    Dim dbPathAndFilename As String

    Public Property ConnectionString as String
       Get
          return $"{dbProvider}{dbSource}{dbPathAndFilename}"
       End Get
    End Property

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dbPathAndFilename = "C:\temp\VB\DBTest\Test.mdb"    
        LoadData()
    End Sub

    Sub LoadData()
        Dim sql As String = "INSERT INTO Table1(Field1) VALUES(?);"

        Using con As New OleDbConnection(ConnectionString), _
              cmd As New OleDbCommand(sql)

            cmd.Parameters.Add("Field1", OleDbType.VarWString, 50).Value = "Field1"

            con.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub
End Class

VB.NET Insert Data into MS Access DB

2 Answers2