asked on

Invalid attempt to call Read when reader is closed.

Hi All,

I know where I want to be but just not quite there yet... I have created the following method which creates an OleDbDataReader object which is returned to my calling code.

I am currently getting the error:

Invalid attempt to call Read when reader is closed.

Silly thing is, I can see why I am getting thiis error as I am using the 'Using' pattern to dispose of any objects prior to returning the OleDbDataReader object. But I can't see how to close the database connection otherwise.

Any help would be much appreciated.

Thanks,

Rit

public OleDbDataReader GetUserID()
    {
        string ConnString = GetConnString();
        string SqlString = @"SELECT tblUsers.userID 
                            FROM (tblUsers
                            WHERE username = ? AND [password] = ?";
        
        OleDbDataAdapter adptUserData = new OleDbDataAdapter();
        OleDbDataReader drUserData;

        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("username", Username);
                cmd.Parameters.AddWithValue("password", Password);
                conn.Open();

                drUserData = cmd.ExecuteReader();
            }
        }

        return drUserData;
    }

Open in new window

GlobaLevel

To close:
Conn.close()

FYI you can only access data while it is open ..,store the datareader into a session variable to use once it is closed...

mrjoltcola

Use a finally block to close.

ASKER CERTIFIED SOLUTION

GlobaLevel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Miguel Oz

You can not return OleDbDataReader, you are better off using a List<string> and load all your ids there.
public List<string> GetUserID()

Replace line 20 with:
List<string> returnList = new List<string>();
Replace line 20 with:
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
returnList .Add(reader[0].ToString());
}
reader.Close();

SOLUTION

Miguel Oz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GlobaLevel

Sorry this is from my iPhone:

Also change this:

See this:

GetConnString();
string SqlString = "SELECT tblUsers.userID
FROM tblUsers
WHERE username = @username AND password = @password";

rito1

ASKER

Thanks all,

Going on mas_oz2003, solution, if I wanted to return another column along with tblUsers.userID (e.g. tblUsers.UserTypeID), what control would I use instead of List<>?

Thank you,

Rit

SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rito1

ASKER

Thanks for your support all. I have it sorted.

Rit

Miguel Oz

YOu can use an object say:
public class UserData
{
public string Id;
public string UserTypeID;
public UserData(string id, string userTypeID)
{
Id = id;
UserTypeID= userTypeID;
}
}

Thus my previous post will look like:
public List<UserData> GetUserID()

Replace line 9 with:
List<UserData> returnList = new List<UserData>();
Replace line 20 with:
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
UserData userData= new UserData(reader[0].ToString(), "Your new userTypeID");
returnList .Add(userData);
}
reader.Close();
Replace line 24 with:
return returnList ;

P.S. If your list is very big and your consumer code need to do a lot of searches, use a generic dictionary (Dictionary<string, UserData>) instead of the generic list.