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

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

Answer 1

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

Answer 2

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

Answer 3

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