ReadFromDatabase Function

[kevinv_]

Hi,

 

I wrote a nice function to return an OleDbDataReader object containing my query's results.

 

Here it is: (feel free to use it)

 

   Public Function ReadFromDatabase(ByRef varQuery As String, ByVal Caller As String) As OleDbDataReader
       'Variables
       Dim varReadOleDBCommand As New OleDbCommand

       'Error Handler
       ReadFromDatabaseError = False

       Try
           'DBCommand Properties 
           With varReadOleDBCommand
               .Connection = New OleDbConnection(varConnectionString)
               .Connection.Open()
               .CommandText = varQuery

               'Return Query Output
               Return .ExecuteReader
           End With
       Catch
           'Log Error
           ReadFromDatabaseError = True

           'Show Error (Debug Window)
           Debug.WriteLine("*** Error ***" & vbCr & "Error: (" & Err.Number & ") " & Err.Description & vbCr & "Caller: " & Caller & vbCr & "Source: DB.Functions.Queries.ReadFromDatabase")
           Debug.WriteLine("")
           Debug.WriteLine("Query: " & vbCr & varQuery)
       Finally
           'Close Database
           varReadOleDBCommand.Connection.Close() 'gives trouble
       End Try
   End Function

 

It has, however, a problem when I try to close the connection in the "Final" part of the try...catch statement.

I receive an error on my calling method that the reader attemt failed because the connection has been closed.

 

My code:

 

       QueryResult = QueriesClass.ReadFromDatabase(Query, "Form1_Button1.Click")


       While QueryResult.Read() 'ERROR: Invalid attempt to Read when reader is closed.
           MsgBox(QueryResult.Item("'Title'"))
       End While

 

Anyway, I want to close the connection when I'm done with it.

It is, however, not possible via this way...

 

So my question is:

How can I return the whole recordset from my function, to my own local variable,

so I can use it from there as if I were using it from within the above method.

 

Perhaps some code-blocking that stops processing the function until I'm done with the reader,

and then advances to the next line, and closes the connection.

 

:confused: Help! :confused:

 

Thanks for your help!

 

Best Regards,

Kevin