• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

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

0
rito1
Asked:
rito1
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
GlobaLevelCommented:
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...
0
 
mrjoltcolaCommented:
Use a finally block to close.
0
 
GlobaLevelCommented:
See this:

GetConnString();
        string SqlString = @"SELECT tblUsers.userID
                            FROM (tblUsers
                            WHERE username = @username AND  password = @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();
Conn.close();

                drUserData = cmd.ExecuteReader();
            }
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Miguel OzSoftware EngineerCommented:
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();
0
 
Miguel OzSoftware EngineerCommented:
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 9 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();
Replace line 24 with:
 return returnList ;
0
 
GlobaLevelCommented:
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";
       
0
 
rito1Author Commented:
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
0
 
mrjoltcolaCommented:
Don't use a control, just return an object. If you don't have one, create an anonymous one. Besides that a List<> is not needed if you only return a single object. Personally I'd make my method return an entity type.

public User GetUser(...) {
   User u = new User();
   // execute SQL
   ...
   // fetch into user properties  
   u.userId = reader[0].ToString();
   u.userName = reader[1].ToString();
   return u;
}
0
 
rito1Author Commented:
Thanks for your support all. I have it sorted.

Rit
0
 
Miguel OzSoftware EngineerCommented:
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now