vb.net OleDb 异常未处理

OleDb Exception was unhandled

提问by James

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

运行此代码时，我似乎遇到了未处理的 OleDb 异常，我真的不明白为什么......

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

回答by Kami

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

客户参数的名称拼写为@CustomerNamee。调整这些或将 if 语句更改为类似

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

回答by Steve

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

FROM 和 CustomerNames 之间没有空格

回答by Steve

In your SQL:

在您的 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:

您引用了不存在的列。Access 认为您要求的是一个参数，而您不是，因此您会收到您提到的错误。试试这个：

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.

使用相同的名称命名字段和表或任何其他对象是一个坏主意。这是有经验的，但你必须相信我。