DataReader issues

This is a discussion on DataReader issues within the C# Programming forums, part of the General Programming Boards category; I know that for every data reader you need a new connection. So I've created 4 connections in the init() ...

  1. #1
    Registered User
    Join Date
    Nov 2006
    Posts
    6

    DataReader issues

    I know that for every data reader you need a new connection. So I've created 4 connections in the init() function for each of the datareaders. Its still giving me the error "There is already an Open DataReader Associated with this connection which must be closed first." Below is shortened version of my code. The Red Line is the one that keeps throwing the error. I could be going about this the wrong way.

    Code:
    public void init()
    {
      SQLConn = new SqlConnection(GetConnStr(DBN,"SQL1"));
      SQLConn1 = new SqlConnection(GetConnStr(DBN,"SQL1"));
      SQLConn2 = new SqlConnection(GetConnStr(DBN,"SQL1"));
      SQLConn3 = new SqlConnection(GetConnStr(DBN,"SQL1"));
      sqlComm = SQLConn.CreateCommand();
      sqlComm1 = SQLConn1.CreateCommand();
      sqlComm2 = SQLConn2.CreateCommand();
      sqlComm3 = SQLConn3.CreateCommand();
    }
    public void dosomeFunc()
    {
      init();
      SQLConn.Open();
      SQLConn1.Open();
      SQLConn2.Open();
      SQLConn3.Open();
      sqlComm.CommandText = "SELECT * FROM STUFF";
      SqlDataReader drStuff = sqlComm.ExecuteReader();
      while(drStuff.Read())
      {
         str = "SELECT * FROM OTHERSTUFF WHERE ID="+drStuff["ID"]
         sqlComm1.CommandText = str;
         SqlDataReader drOther = sqlComm1.ExecuteReader();
         while(drOther.Read())
         {
            //dosomething;
         }
         drOther.Close();
       }
       drStuff.Close();
    }
    Thanks,
    Josh

  2. #2
    Registered User
    Join Date
    Mar 2005
    Location
    Mountaintop, Pa
    Posts
    1,059
    I know that for every data reader you need a new connection.
    Yes, but you have only created new instances to the same connection based on the connection string you're using. When the SqlDataReader is in use, the assigned SqlConnection is only serving that particular SqlDataReader, no other operations including another SqlDataReader can be performed on that particular connection.

    You may want to check out connection pooling using unique connection strings.

  3. #3
    Registered User
    Join Date
    Nov 2006
    Posts
    6
    I guess i'm lacking the knowledge in creating a new connection every time. How would I go about doing that?

    Thanks,
    Josh

  4. #4
    Registered User
    Join Date
    Mar 2005
    Location
    Mountaintop, Pa
    Posts
    1,059
    You may want to try something like this in your connection string:

    Code:
    String connString = "server=(local)\\SQLExpress;Integrated Security=SSPI;database=MyDatabase;" +
                 "connection reset=false;" +
                 "connection lifetime=5;" +
                 "min pool size=1;" +
                 "max pool size=4";

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Bitmap scroll issues
    By Gerread in forum Windows Programming
    Replies: 4
    Last Post: 05-14-2007, 05:18 AM
  2. Solution to culling issues?
    By VirtualAce in forum Game Programming
    Replies: 4
    Last Post: 03-14-2006, 05:59 PM
  3. SQL Connection issues
    By jverkoey in forum Tech Board
    Replies: 4
    Last Post: 02-17-2005, 06:52 AM
  4. Visual Age C++ to MS VC++ conversion issues?
    By Ruchikar in forum Windows Programming
    Replies: 3
    Last Post: 08-10-2003, 09:54 PM
  5. hexdump issues
    By daluu in forum C Programming
    Replies: 2
    Last Post: 03-04-2003, 08:01 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21