Unable to insert into a MS Access database

Question

I am trying to insert data into a database using a sign in form, but the code does not seem to work. No error occurs, it just looks like nothing is happening when I run the code.

Code:

Imports System.Data.OleDb

Public Class Form2
Dim provider As String
Dim datafile As String
Dim connstring As String
Dim myconnection As OleDbConnection = New OleDbConnection
Dim Mode As String

Private Sub btnSignin_Click(sender As Object, e As EventArgs) Handles btnSignin.Click


    ' Enable the Insert / Update / Delete buttons.
    btnSignin.Enabled = True

    ' Test for the Mode of the form and perform the necessary actions accordingly.
    Select Case Mode
        Case "INSERT" ' If the mode is INSERT, perform a new record insertion.
            Try
                ' Declare the connection and command objects.
                Dim connection As New OleDb.OleDbConnection
                Dim command As New OleDb.OleDbCommand

                ' Set the ConnectionString property and open the database connection.
                connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Dentist Pat 2\Dentist Pat 2\Users.accdb;_Name=;_Surname=;_Username=;_Password=;"
                connection.Open()

                ' Set the Connection and CommandText properties of the command.
                command.Connection = connection
                command.CommandText = "INSERT INTO Users (_Name,_Surname,_Password,_Username) VALUES (@Name,@Surname,@Password,@Username)"

                ' Declare and initialize the parameters required by the command object.
                Dim parName = New OleDb.OleDbParameter("@Name", txtName.Text)
                Dim parSurname = New OleDb.OleDbParameter("@Surname", txtSurname.Text)
                Dim parUsername = New OleDb.OleDbParameter("@Username", txtUsername.Text)
                Dim parPassword = New OleDb.OleDbParameter("@Password", txtPassword.Text)

                ' Add the parameters to the command object.
                command.Parameters.Add(parName)
                command.Parameters.Add(parSurname)
                command.Parameters.Add(parUsername)
                command.Parameters.Add(parPassword)

                ' Execute the command.
                command.ExecuteNonQuery()

                ' Close the database connection.
                connection.Close()

            Catch ex As Exception
                ' Prompt the user with the exception.
                MessageBox.Show("Sign In Complete")
            End Try
    End Select
    Me.Hide()
    Form3.ShowDialog()
End Sub
End Class

Answer 1

I'm not sure what you are attempting with your connection string but you don't need this part:

;_Name=;_Surname=;_Username=;_Password=;

Instead remove the above:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Dentist Pat 2\Dentist Pat 2\Users.accdb;

For more information on connection strings have a look at www.connectionstrings.com/access.

As said in the comments, consider checking the value returned by ExecuteNonQuery() to see if the row has been inserted.

With MS Access, the order of the parameters is important not the names. I use the ? placeholder within my SQL command when using parameters. I also specify the data type so consider using the OleDbParameter Constructor (String, OleDbType) constructor. In my example I have used VarChar which you may want to change accordingly.

I would also consider implementing Using:

Managed resources are disposed of by the .NET Framework garbage collector (GC) without any extra coding on your part. You do not need a Using block for managed resources. However, you can still use a Using block to force the disposal of a managed resource instead of waiting for the garbage collector.

Your code would look something similar to this:

Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Dentist Pat 2\Dentist Pat 2\Users.accdb;"),
      cmd As New OleDbCommand("INSERT INTO Users (_Name, _Surname, _Password, _Username) VALUES (?, ?, ?, ?)", con)

    con.Open()

    cmd.Parameters.Add(New OleDbParameter("@Name", OleDbType.VarChar)).Value = txtName.Text
    cmd.Parameters.Add(New OleDbParameter("@Surname", OleDbType.VarChar)).Value = txtSurname.Text
    cmd.Parameters.Add(New OleDbParameter("@Username", OleDbType.VarChar)).Value = txtUsername.Text
    cmd.Parameters.Add(New OleDbParameter("@Password", OleDbType.VarChar)).Value = txtPassword.Text

    Dim rowsAffected As Integer = cmd.ExecuteNonQuery()

    If rowsAffected = 1 Then
        MessageBox.Show("Sign In Complete")
    End If
End Using

As you can see, overall the code is a lot neater and a lot easier to read. I hope this helps with your problem.

^{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.}