ExecuteReader() Object reference not set to an instance of an object error

Question

I am trying to figure out why I am getting an error of 'Object reference not set to an instance of an object.' when my winforms code runs. I have set a breakpoint on the sql statement and stepped into the code and it shows as the line: Using dr = oledbCmd.ExecuteReader()

I am still learning vb.Net so would appreciate some help as to how to overcome this error. Many thanks

    DBConnection.connect()

    sql = "SELECT * from Boxes WHERE Customer = ? AND Status = 'I'"

    Dim cmd As New OleDb.OleDbCommand

    cmd.Parameters.AddWithValue("@p1", cmbCustomer.Text)

    cmd.CommandText = sql
    cmd.Connection = oledbCnn
    dr = cmd.ExecuteReader

    Using dr = oledbCmd.ExecuteReader()

        While dr.Read()

            Dim LV As New ListViewItem

            With LV

                .UseItemStyleForSubItems = False
                .Text = dr(1).ToString()
                .SubItems.Add(dr(2).ToString())

            End With
            lvSelectRequestItems.Items.Add(LV)
        End While

    End Using

    cmd.Dispose()
    dr.Close()
    oledbCnn.Close()

DBConnect module

Imports System.Data.OleDb

    Module DBConnection

        Public connetionString As String = My.Settings.storageConnectionString
        Public oledbCnn As New OleDbConnection
        Public oledbCmd As OleDbCommand
        Public dr As OleDbDataReader
        Public sql As String

        Sub connect()

            'connetionString = My.Settings.storageConnectionString
            oledbCnn.ConnectionString = connetionString
            oledbCnn.Open()

        End Sub

    End Module

Answer 1

I saw several mistakes. Look to the comments for reasons for the changes

Dim sql As String = "SELECT * from Boxes WHERE Customer = ? AND Status = 'I'"
'.Net uses connection pooling, such that you're better using a new connection object for each query
'Also, a Using block will ensure the connection is closed, **even if an exception is thrown**
' The original code would leak connections when exceptions occured, eventually locking you out of the db
Using cn As New OleDb.OleDbConnection("Connection string here"), _
      cmd As New OleDb.OleDbCommand(sql, cn) 'set CommandText BEFORE adding parameters

    'Use explicit parameter types
    cmd.Parameters.Add("?", SqlDbType.NVarChar, 50).Value = cmbCustomer.Text

    cn.Open()
    Using dr As OleDb.OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()

            Dim LV As New ListViewItem
            With LV
                .UseItemStyleForSubItems = False
                .Text = dr(1).ToString()
               .SubItems.Add(dr(2).ToString())
            End With
            lvSelectRequestItems.Items.Add(LV)

        End While
        dr.Close()

    End Using
End Using

Answer 2

You have failed to properly bind the SqlConnection to the SqlCommand object.

Using connection As New SqlConnection(connectionString)
    connection.Open()

    Dim command As New SqlCommand(queryString, connection)
    Dim reader As SqlDataReader = command.ExecuteReader()
    While reader.Read()
        Console.WriteLine("{0}", reader(0))
    End While 
End Using 

See: MSDN

Edit: Requested adjustment to aid in clarity:

    Using connection As New Data.SqlClient.SqlConnection
        Dim sql As String = "SELECT * from Boxes WHERE Customer = ? AND Status = 'I'"
        connection.Open()
        Using command As New Data.SqlClient.SqlCommand(Sql, connection)
            command.Parameters.AddWithValue("@p1", cmbCustomer.Text)
            dr = command.ExecuteReader()

            While dr.Read()
                Dim LV As New ListViewItem
                With LV
                    .UseItemStyleForSubItems = False
                    MediaTypeNames.Text = dr(1).ToString()
                    .SubItems.Add(dr(2).ToString())

                End With
                lvSelectRequestItems.Items.Add(LV)
            End While
        End Using
    End Using

Your code should look something like that.

Answer 3

you have a fair amount of potential issues going on here.

1-you are using your dr variable twice. once before the using, which is probably causing your error. then again in for using, which does not look right because it is not the cmd variable used to execute the reader. so change this part of your code:

cmd.CommandText = sql
cmd.Connection = oledbCnn
dr = cmd.ExecuteReader

Using dr = oledbCmd.ExecuteReader()

to this:

cmd.CommandText = sql
cmd.Connection = oledbCnn

Using dr = cmd.ExecuteReader()

2-you are not showing if oledbCnn has been opened yet. I'm assuming your DBConnection.connect() function is doing this and olebCnn is a variable that function sets and opens

3-I'm not sure if the question mark in your query string will work. Even if it does you should replace it with the parameter name. so your query string should be:

sql = "SELECT * from Boxes WHERE Customer = @p1 AND Status = 'I'"

lastly you should probably show all the code for the sub(or function) this is for so we can get a full picture. example dr must be declared before the using else you would get a build error.