Object reference not set to an instance of an object VB.Net OleDbCommand

Question

Everytime i search on my database it always says "Object reference not set to an instance of an object." Please anyone help me. Thank you.

Here is my code for Search:

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

Here is my code for Populate ListView:

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

.SelectCommand = New OleDb.OleDbCommand() <--- error in here sir — Aouie
– Aouie, Commented Dec 8, 2014 at 8:04
possible duplicate of What is a NullReferenceException and how do I fix it? — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Dec 8, 2014 at 12:43
Did you target x64 or AnyCpu? I think OleDB still only works on 32 bits app. I used to get that error in the past when I tried using OleDB in 64 bits. — Mystra007
– Mystra007, Commented Dec 8, 2014 at 16:42

Jonathan Allen · Accepted Answer · 2014-12-08 08:08:17Z

2

Ok, let's fix some stuff.

Don't use Call. That's just a hold-over from VB 6 (i.e. the late 90's)
Don' use Exit Sub. Use Return instead. (Same reason as #1)

Use parameterized SQL rather that string concatenation. Your code, as is, won't work is someone searches for "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

As for your error, the only possible cause is that OleDa is Nothing.

answered Dec 8, 2014 at 8:08

Jonathan Allen

70.6k77 gold badges276 silver badges459 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AStopher Over a year ago

@user2926827 Please be patient. Please provide the complete error message that you are receiving.

Jonathan Allen Over a year ago

Are you 100% sure OleDa isn't Nothing? The code I showed won't fix that bug.

Collectives™ on Stack Overflow

Object reference not set to an instance of an object VB.Net OleDbCommand

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related