Newbie woes: accessing two tables at the same time RRS feed

  • Question

  • My code goes over the rows of table 1, looking up values, doing some calculations, and saving them to table 2 in the same database.


    using (SqlConnection conn = new SqlConnection(connString))



    SqlCommand c = new SqlCommand("..", conn);

    SqlDataReader r = c.ExecuteReader();

    if (r.HasRows)


    while (r.Read())


    <do something>

    SqlCommand c2 = new SqlCommand("..",conn);






    .. Oh, rather, this is what I want it to do; in practice, Visual Studio flags the 'ExecuteNonQuery' line as error, saying 


    There is already an open DataReader associated with this Command which must be closed first. 


    Yes, I can 'pre-read' table 1, saving values to an array, and iterate over it, calling table 2. Is that my only option?



    Tuesday, August 5, 2008 2:33 PM


  • You get this error because by default SqlDataReader cannot share same database connection with any other command. In a case if you use SQL Server 2005 you could enable MARS feature in your connection string setting MultipleActiveResultSets=True inside of connection string and connection could be shared. Otherwise you need to open brand new connection for your second command that calls ExecuteNonQuery.


    Wednesday, August 6, 2008 9:48 AM