OleDb Exception was unhandled

Question

I seem to be getting a OleDb Exception was unhandled when running this code and i really cant see why...

Code attached Thanks! James

Module DataAccess
    Private Builder As New OleDb.OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    ''' <summary>
    ''' Used to remove the current item selected in the txtFirstName text box.
    ''' </summary>
    ''' <param name="Name"></param>
    ''' <remarks></remarks>
    Public Sub RemmoveFemale(ByVal Name As String)
        Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        DELETE FROMCustomerNames WHERE CustomerName = @CustomerName
                    </SQL>.Value

                cmd.Parameters.Add(New OleDb.OleDbParameter With {.DbType = DbType.String, .ParameterName = "@CustomerName", .Value = Name})

                cn.Open()
                Dim Affected As Int32 = cmd.ExecuteNonQuery

            End Using
        End Using
    End Sub
    ''' <summary>
    ''' Called in Form1 on FormClosing event to update the database table if
    ''' needed.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <remarks></remarks>
    Public Sub UpdateFemaleNames(ByVal sender As AutoCompleteStringCollection)
        Dim NewNames As New List(Of String)

        Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT CustomerName
                        FROM CustomerNames
                        WHERE CustomerName = @CustomerName
                    </SQL>.Value

                cmd.Parameters.Add(New OleDb.OleDbParameter With {.DbType = DbType.String, .ParameterName = "@CustomerName"})

                cn.Open()

                For x As Int32 = 0 To sender.Count - 1
                    cmd.Parameters("@CustomerName").Value = sender.Item(x)
                    Dim Result As String = CStr(cmd.ExecuteScalar)
                    If String.IsNullOrWhiteSpace(Result) Then
                        NewNames.Add(sender.Item(x))
                    End If
                Next

                If NewNames.Count > 0 Then
                    cmd.CommandText =
                        <SQL>
                                INSERT INTO CustomerNames (CustomerName,Gender) VALUES (@CustomerNamee,@Gender)
                            </SQL>.Value

                    cmd.Parameters.Add(New OleDb.OleDbParameter With {.DbType = DbType.String, .ParameterName = "@Gender", .Value = "Female"})

                    For Each Item In NewNames
                        cmd.Parameters("@CustomerName").Value = Item
                        cmd.ExecuteReader()
                    Next

                End If

            End Using
        End Using
    End Sub
    ''' <summary>
    ''' Used in Form1 DataGridView1 for learning purposes only
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AllFemaleNames() As DataTable
        Dim dt As New DataTable
        Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT Identifier, CustomerName
                        FROM CustomerNames
                        WHERE Gender = 'Female'
                        ORDER BY CustomerName;
                    </SQL>.Value

                cn.Open()

                dt.Load(cmd.ExecuteReader)

            End Using
        End Using

        Return dt
    End Function
    ''' <summary>
    ''' Load only female first names into the auto complete source
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function LoadFemaleNames() As AutoCompleteStringCollection
        Dim TheNameList As New AutoCompleteStringCollection


        Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT CustomerName
                        FROM CustomerNames
                        WHERE Gender = 'Female'
                        ORDER BY CustomerName;
                    </SQL>.Value

                cn.Open()
                Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader

                If Reader.HasRows Then
                    While Reader.Read
                        TheNameList.Add(Reader.GetString(0))
                    End While

                    Reader.Close()

                End If

            End Using
        End Using

        Return TheNameList

    End Function

End Module








Module StringExtensions
    <Runtime.CompilerServices.Extension()> _
    Public Function ProperCase(ByVal sender As String) As String
        Dim TI As System.Globalization.TextInfo = New System.Globalization.CultureInfo("en-US", False).TextInfo
        Return TI.ToTitleCase(sender.ToLower)
    End Function
End Module







''' <summary>
''' Simple demo for auto complete adding items that are not in the list when pressing ENTER in TextBox1.
''' </summary>
''' <remarks></remarks>
Public Class Form1
    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        UpdateFemaleNames(txtFirstName.AutoCompleteCustomSource)
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        txtFirstName.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        txtFirstName.AutoCompleteSource = AutoCompleteSource.CustomSource
        txtFirstName.AutoCompleteCustomSource = LoadFemaleNames()
        ' The next two lines are for demo purposes only to see what is in the list for the TextBox with First names
        DataGridView1.DataSource = AllFemaleNames()
        DataGridView1.Columns("CustomerName").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
    End Sub
    Private Sub TextBox1_KeyDown(sender As Object, e As KeyEventArgs) Handles txtFirstName.KeyDown
        If e.KeyCode = Keys.Enter Then
            If Not String.IsNullOrWhiteSpace(txtFirstName.Text) Then
                If Not txtFirstName.AutoCompleteCustomSource.Contains(txtFirstName.Text.ToLower) Then
                    txtFirstName.AutoCompleteCustomSource.Add(txtFirstName.Text.ProperCase)
                End If

                txtFirstName.Text = txtFirstName.Text.ProperCase

                e.SuppressKeyPress = True

            End If
        End If
    End Sub
    Private Sub cmdRemoveName_Click(sender As Object, e As EventArgs) Handles cmdRemoveName.Click
        If Not String.IsNullOrWhiteSpace(txtFirstName.Text) Then
            Dim CurrentName As String = txtFirstName.Text.Trim.ProperCase
            If My.Dialogs.Question(String.Format("Remove '{0}'", CurrentName)) Then
                RemmoveFemale(CurrentName)
                txtFirstName.AutoCompleteCustomSource.Remove(txtFirstName.Text)
                txtFirstName.Text = ""
            End If
        End If
    End Sub
End Class

Answer 1

Without exact location of the error it is difficult to identify where the problem is originating from.

However, given the error message it seems like one or more of your statements contain parameters that are not provided. The following code

If NewNames.Count > 0 Then
    cmd.CommandText =
        <SQL>
             INSERT INTO CustomerNames (CustomerName,Gender) VALUES (@CustomerNamee,@Gender)
        </SQL>.Value

    cmd.Parameters.Add(New OleDb.OleDbParameter With {.DbType = DbType.String, .ParameterName = "@Gender", .Value = "Female"})

    For Each Item In NewNames
        cmd.Parameters("@CustomerName").Value = Item
        cmd.ExecuteReader()
    Next

End If

the name of the customer parameters is spelt @CustomerNamee. Adjust these or change the if statement to something like

If NewNames.Count > 0 Then
    cmd.CommandText =
        <SQL>
             INSERT INTO CustomerNames (CustomerName,Gender) VALUES (@CustomerName,@Gender)
        </SQL>.Value

    For Each Item In NewNames
        cmd.Parameters("@CustomerName").Value = Item
        cmd.Parameters("@Gender").Value = "Female";
        cmd.ExecuteReader()
    Next
End If

Answer 2

Right from the start, I see this line in your code, which would cause that error:

DELETE FROMCustomerNames WHERE CustomerName = @CustomerName

And it should be this:

DELETE FROM CustomerNames WHERE CustomerName = @CustomerName

There was no space between FROM and CustomerNames

Answer 3

In your SQL:

SELECT CustomerName
FROM CustomerNames
WHERE Gender = 'Female'
ORDER BY CustomerName;

You refer to a column that does not exist. Access thinks you are asking for a parameter, which you are not, so you get the error you mentioned. Try this instead:

SELECT CustomerNames
FROM CustomerNames
WHERE Gender = 'Female'
ORDER BY CustomerNames;

It is a bad idea to name fields and tables, or any other objects for that matter, with the same name. This comes with experience but you must trust me on this.