Cannot insert into MS Access database from VB.NET

Question

VB.NET cannot insert data into a MS Access database table.

What can I modify in this code to insert the data into the table?

Error messages:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: ExecuteNonQuery: Connection property has not been initialized.

Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click

        Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=loginUser.mdb;")

        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter

        Try
            connection.Open()
            Dim cmd As New OleDbCommand
            cmd.CommandText = "INSERT INTO Staff (ID, username, password) VALUES (@id, @username, @password)"
            cmd.Parameters.AddWithValue("@id", 101)
            cmd.Parameters.AddWithValue("@username", txtUsername.Text)
            cmd.Parameters.AddWithValue("@password", txtPassword.Text)

            cmd.ExecuteNonQuery()
            MsgBox("Add")
        Catch ex As Exception
        Finally
            connection.Close()
        End Try


    End Sub

Posting the exact error message that occurs when the insertion fails may be helpful to others. — Paul
– Paul, Commented Sep 9, 2015 at 4:30
An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll Additional information: ExecuteNonQuery: Connection property has not been initialized. — user2534252
– user2534252, Commented Sep 9, 2015 at 4:42
Password is a reserved word and needs to be aliased or escaped. Please see stackoverflow.com/tags/ms-access/info — Fionnuala
– Fionnuala, Commented Sep 9, 2015 at 7:12

BizApps · Accepted Answer · 2015-09-09 04:54:17Z

0

Hi you need to initialize the connection to the cmd.Connection property, change your below code to this one:

  cmd.Connection = connection
  con.Open()
  cmd.ExecuteNonQuery()

answered Sep 9, 2015 at 4:54

BizApps

6,1409 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amnesh Goel · Accepted Answer · 2015-09-09 04:55:06Z

You didn't mention your connection while creating/executing the command. Use below code instead...

    Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click

        Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=loginUser.mdb;")

        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter

        Try
            Dim cmd As New OleDbCommand("INSERT INTO Staff (ID, username, password) VALUES (@id, @username, @password)",connection)
            connection.Open()
            cmd.Parameters.AddWithValue("@id", 101)
            cmd.Parameters.AddWithValue("@username", txtUsername.Text)
            cmd.Parameters.AddWithValue("@password", txtPassword.Text)

            cmd.ExecuteNonQuery()
            MsgBox("Add")
        Catch ex As Exception
        Finally
            connection.Close()
        End Try


End Sub

Vivek S. · Accepted Answer · 2015-09-09 07:06:12Z

The reason for the exception is you have not assign the declared connection to the command object

So you could modify existing command declaration to

Dim cmd As New OleDbCommand("", connection )

or

Dim cmd As New OleDbCommand
cmd.connecction = connection

I just revamped your code : see following

Dim dt As New DataTable
Dim da As New OleDbDataAdapter
Using OleDbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=loginUser.mdb;")
            Dim cmd As New OleDbCommand("", OleDbConn)
            With cmd
                OleDbConn.Open()
                .CommandText = "INSERT INTO Staff (ID, username, password) VALUES (@id, @username, @password)"
                .Parameters.AddWithValue("@id", 101)
                .Parameters.AddWithValue("@username", "adfda")
                .Parameters.AddWithValue("@password", "dfsd")
                .ExecuteNonQuery()
                MsgBox("Add")
            End With
        End Using

You can use Using block to handle your connection ,The connection will automatically close at the end of the using block

Collectives™ on Stack Overflow

Cannot insert into MS Access database from VB.NET

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related