Error when inserting data into a MS Access table

Question

How can I insert data into a MS Access table? When I try, I am getting an error.

Code:

If TextBox1.Text = Nothing And TextBox2.Text = Nothing Then
    MsgBox("No Username and Password inserted")
    TextBox1.Focus()

Else

    If Not con.State = ConnectionState.Open Then
        'open connection if it is not yet open
    End If

    cmd.Connection = con
    'add data to table
    cmd.CommandText = "insert into loginTable(username, password, typeofuser) values ('" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.ComboBox1.Text & "')"

    cmd.ExecuteNonQuery()

    'refresh data in list

    'close connection
    con.Close()
End If

Answer 1

First, you don't open the connection:

con.Open()

Next, password is a reserved word in MS Access. You would need to wrap password in square brackets:

[password]

You are concatenating strings instead of using paramaters:

cmd.Parameters.Add("@username", OleDbType.VarChar).Value = txtUsername.Text
cmd.Parameters.Add("@password", OleDbType.VarChar).Value = txtPassword.Text
cmd.Parameters.Add("@typeofuser", OleDbType.VarChar).Value = cmbTypeOfUser.Text

Look at giving your TextBox and ComboBox controls a proper name instead of using TextBox1, TextBox2 and ComboBox1. This helps to identify correctly each control:

txtUsername
txtPassword
cmbTypeOfUser

Move away from using MsgBox and use MessageBox.Show. MsgBox exists for VB6 and ends up delegating to MessageBox anyway so makes sense to use MessageBox.Show:

MessageBox.Show("No Username and Password inserted")

Lastly I would consider implementing Using which will help to close and dispose of your SQL objects:

Using cmd As New OleDbCommand(command, connection)

End Using

All together your code would look something like this:

If txtUsername.Text = Nothing And txtPassword.Text = Nothing Then

    MessageBox.Show("No Username and Password inserted")
    TextBox1.Focus()

Else

    Using con As New OleDbConnection(connectionString),
          cmd As New OleDbCommand("INSERT INTO [loginTable] ([username], [password], [typeofuser]) VALUES (@username, @password, @typeofuser)", con)

        con.Open()

        cmd.Parameters.Add("@username", OleDbType.VarChar).Value = txtUsername.Text
        cmd.Parameters.Add("@password", OleDbType.VarChar).Value = txtPassword.Text
        cmd.Parameters.Add("@typeofuser", OleDbType.VarChar).Value = cmbTypeOfUser.Text

        cmd.ExecuteNonQuery()

    End Using

End If

^{It's outside the scope of this question but I would also look at encrypting passwords. Storing them as plain text is bad practice. Have a look at the SO question; Best way to store password in database, which may give you some ideas on how best to do this.}

Answer 2

There are (at least) a few ways to do this. So, try this . . .

Imports System.Data.OleDb

Public Class Form1

    Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Excel\Desktop\Coding\Microsoft Access\Northwind.mdb;"
    Private NewIdentifer As Integer = 0
    Private InsertStatement As String = "INSERT INTO Employee (LName) Values(@LName)"
    Private IdentifierStatement As String = "Select @@Identity"

    'Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Using cn As New OleDbConnection(ConnectionString)
            Using cmd As New OleDbCommand("SELECT * FROM Employee", cn)
                Dim dt As New DataTable
                cn.Open()
                Dim Reader As OleDbDataReader = cmd.ExecuteReader()
                dt.Load(Reader)
                Dim dv = dt.DefaultView
                DataGridView1.DataSource = dv
            End Using
        End Using
    End Sub

    'End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        If Not String.IsNullOrEmpty(txtLastName.Text) Then
            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand(InsertStatement, cn)
                    cmd.Parameters.AddWithValue("@LName", txtLastName.Text)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = IdentifierStatement
                    NewIdentifer = CInt(cmd.ExecuteScalar())
                    Dim Row As DataRowView = CType(DataGridView1.DataSource, DataView).AddNew
                    Row("Fname") = NewIdentifer
                    Row("LName") = txtLastName.Text
                    Row.EndEdit()
                    DataGridView1.CurrentCell = DataGridView1(0, DataGridView1.RowCount - 1)
                    txtLastName.Text = ""
                End Using
            End Using
        Else
            MsgBox("Please enter a name")
        End If

    End Sub
End Class

Also, try . . .

Imports System.Data.OleDb

Public Class Form1



    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        ' Requires: Imports System.Data.OleDb

        ' ensures the connection is closed and disposed
        Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=""C:\Users\Ryan\Desktop\Coding\DOT.NET\Samples VB\Insert Into MS Access Table from Textbox\WindowsApplication1\bin\InsertInto.mdb"";" & _
            "Persist Security Info=False")
            ' open connection
            connection.Open()

            ' Create command
            Dim insertCommand As New OleDbCommand( _
                "INSERT INTO Table1([inputOne] , [inputTwo] , [inputThree]) " & _
                "VALUES (@inputOne, @inputTwo, @inputThree);", _
                connection)
            ' Add the parameters with value
            insertCommand.Parameters.AddWithValue("@inputOne", TextBox1.Text)
            insertCommand.Parameters.AddWithValue("@inputTwo", TextBox2.Text)
            insertCommand.Parameters.AddWithValue("@inputThree", TextBox3.Text)
            ' you should always use parameterized queries to avoid SQL Injection
            ' execute the command
            insertCommand.ExecuteNonQuery()

            MessageBox.Show("Insert is done!!")

        End Using

    End Sub
End Class