Problem with DataReader

[VB Programmer]

I have a data class which returns a datareader. (Using an Access db.) I am
using this method to fill a dropdownlist. The problem is that after I've
ran it one time I get this error:
Could not use ''; file already in use.
I know that the datareader probably has it locked. Where should I close the
datareader?

Here's the function:
Public Function GetDataReader(ByVal strSql As String, ByVal strCnnString
As String) As OleDbDataReader
Dim cnn As New OleDbConnection(strCnnString)
cnn.Open()

Dim result As OleDbDataReader
Dim myCommand As New OleDbCommand
myCommand = New OleDbCommand(strSql, cnn)
result = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

' Return the datareader result
Return result
End Function

 

[Marina]

As soon as you are done reading data from it. Perhaps you shouldn't use
data readers if you are worried about not closing it at the right time, etc.

 

[VB Programmer]

If I close the datareader then the data is not accessible anymore, correct?

I need it to be accessible so that I can read the data into my dropdownlist.

 

[Miha Markic [MVP C#]]

Hi,

But you have to close the datareader when you finished reading from it - so,
you read data from reader and then close it.
The other way would be to use DataTable as a data source

 

[Marina]

You can't have it both ways.

If you need to access a second set of data while keeping the datareader
open, then as Miha said, you should use datatables. Otherwise access
complains about the file being open.