locked
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))

    {

    conn.Open();

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

    SqlDataReader r = c.ExecuteReader();

    if (r.HasRows)

    {

    while (r.Read())

    {

    <do something>

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

    c2.ExecuteNonQuery();

    }

    }

    }

     

    .. 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?

     

    Thanks!  

    Tuesday, August 5, 2008 2:33 PM

Answers

  • 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