Sql OUTPUT Params, SqlDataReaders and “Object reference not set to an instance of an object”

When you call ExecuteReader() on a SqlCommand object that has OUTPUT parameters you won’t be able to access the OUTPUT parameters until the SqlDataReader has been closed.

            Private Shared Sub SomeMethod()
                Using Con As New SqlConnection("DSN...")
                    Con.Open()
                    Using Com As New SqlCommand("MyProc", Con)
                        Com.CommandType = System.Data.CommandType.StoredProcedure
                        Com.Parameters.Add(New SqlParameter("@YourOutputParam", System.Data.SqlDbType.Int) With {.Direction = Data.ParameterDirection.Output})
                        Using RDR = Com.ExecuteReader()
                            ''Using the line below will fail
                            'Dim X = Com.Parameters("@YourOutputParam").Value
                            Do While RDR.Read
                                'Do stuff with your recordset...
                            Loop
                        End Using
                        ''This will work because the Using block closes the SqlDataReader
                        'Dim X = Com.Parameters("@YourOutputParam").Value
                    End Using
                    Con.Close()
                End Using
            End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.