对象引用未设置为对象 VB.Net OleDbCommand 的实例

Question

每次

我在数据库上搜索时，它总是说"对象引用未设置为对象的实例"。请任何人帮助我。谢谢。

这是我的搜索代码：

Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click

    If TBSearch.Text = "" Then
        MsgBox("Please enter keyword to search...", MsgBoxStyle.Information, "Keyword to search...")`
        TBSearch.Focus()
        Exit Sub
    End If
    Call OpenConnection()
    With OleDa
        Call Initialized()
        .SelectCommand = New OleDb.OleDbCommand() <-------- Error appears here.
        .SelectCommand.CommandText = "SELECT * FROM [HighSchool] WHERE [LastName] Like '%%" & TBSearch.Text & "%%' or [FirstName] Like '%%" & TBSearch.Text & "%%' or [MI] Like '%%" & TBSearch.Text & "%%'" & _
        "Or [Gender] Like '%%" & TBSearch.Text & "%%' or [Address] Like '%%" & TBSearch.Text & "%%' or [Birthday] Like '%%" & TBSearch.Text & "%%' or [RNumber] Like '%%" & TBSearch.Text & "%%'" & _
        "Or [ENumber] Like '%%" & TBSearch.Text & "%%'  ORDER By LastName ASC"
        .SelectCommand.Connection = OleCn
        Call PopulateListView()
        If Me.LV.Items.Count >= 1 Then
            MsgBox(Me.LV.Items.Count & " Record(s) found for " & "( " & Me.TBSearch.Text & " )", MsgBoxStyle.OkOnly, "Record(s) found...")
        Else
            MsgBox("No record(s) found for " & "( " & Me.TBSearch.Text & " )" & " , please try again... ", MsgBoxStyle.Critical, "No record found...")
            TBSearch.Focus()
            TBSearch.SelectAll()
        End If
    End With
    Call CloseConnection()
End Sub

这是我用于填充列表视图的代码：

Public Sub PopulateListView() Me.LV.Items.Clear() Dim OleDr As OleDb.OleDbDataReader OleDr = OleDa.SelectCommand.ExecuteReader()

    Do While OleDr.Read()
        Dim Item As New ListViewItem
        Item.Text = IIf(OleDr.IsDBNull(0), "", OleDr.Item(0))
        For shtCntr = 1 To OleDr.FieldCount() - 1
            If Not OleDr.IsDBNull(shtCntr) Then
                Item.SubItems.Add(OleDr.Item("FirstName"))
                Item.SubItems.Add(OleDr.Item("LastName"))
                Item.SubItems.Add(OleDr.Item("MI"))
                Item.SubItems.Add(OleDr.Item("Gender"))
                Item.SubItems.Add(OleDr.Item("Address"))
                Item.SubItems.Add(OleDr.Item("Birthday"))
                Item.SubItems.Add(OleDr.Item("RNumber"))
                Item.SubItems.Add(OleDr.Item("ENumber"))
            Else
                Item.SubItems.Add("")
            End If
        Next shtCntr
        Me.LV.Items.Add(Item)
    Loop
End Sub

Answer 1

好的，让我们修复一些东西。

1. 不要使用Call .这只是 VB 6（即 90 年代后期）的延续
2. 不要使用Exit Sub .请改用Return。（原因与#1相同）

3. 使用参数化的 SQL，而不是字符串连接。你的代码，按原样，是有人搜索"O'Mally"是行不通的。

   Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click
       If TBSearch.Text = "" Then
           MsgBox("Please enter keyword to search...", MsgBoxStyle.Information, "Keyword to search...")`
           TBSearch.Focus()
           Exit Sub
       End If
       Call OpenConnection()
       With OleDa
           Call Initialized()
           .SelectCommand = New OleDb.OleDbCommand() 
           .SelectCommand.CommandText = "SELECT * FROM [HighSchool] WHERE [LastName] Like @TBSearch or [FirstName] Like @TBSearch or [MI] Like @TBSearch Or [Gender] Like @TBSearch or [Address] Like @TBSearch or [Birthday] Like @TBSearch or [RNumber] Like @TBSearch Or [ENumber] Like @TBSearch ORDER By LastName ASC"
           .SelectCommand.Connection = OleCn
           .SelectCommand.Parameters.AddWithValue("@TBSearch", "%" & TBSearch.Text & "%")
           Call PopulateListView()
           If Me.LV.Items.Count >= 1 Then
               MsgBox(Me.LV.Items.Count & " Record(s) found for " & "( " & Me.TBSearch.Text & " )", MsgBoxStyle.OkOnly, "Record(s) found...")
           Else
               MsgBox("No record(s) found for " & "( " & Me.TBSearch.Text & " )" & " , please try again... ", MsgBoxStyle.Critical, "No record found...")
               TBSearch.Focus()
               TBSearch.SelectAll()
           End If
       End With
       Call CloseConnection()
   End Sub
   

至于你的错误，唯一可能的原因是OleDa Nothing .