CODEWITHSUNDEEP
c#sqlasp.netsql-server
Christopher Bruce
Christopher Bruce

Reputation: 711

Already an open DataReader. MARS is set to true

I always have this problem when trying to switch back and forth between different commands and result sets when using SQL in ASP.NET. Code:

foreach (string user in usernames)
{
    command.CommandText = "select case system_role when 'Z' then 'none' else 'none' end as 'system_role', case institution_roles_pk1 when 1 then 'student' when 2 then 'faculty' end as 'institution_role',batch_uid,user_id,student_id as 'passwd',firstname,middlename,lastname,email,student_id,case row_status when 0 then 'ENABLED' when 2 then 'DISABLED' end as 'row_status' from users where user_id = '" + user + "'";

    SqlDataReader reader = command.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            file.WriteLine(user + "|" + status);

            if (overrideFramework == 0)
            {
               command.CommandText = "use sis_comparison_data insert into person_override values ('" + reader["system_role"].ToString() + "','" + reader["institution_role"].ToString() + "','" + reader["batch_uid"].ToString() + "','" + reader["user_id"].ToString() + "','" + reader["passwd"].ToString() + "','" + reader["firstname"].ToString() + "','" + reader["middlename"].ToString() + "','" + reader["lastname"].ToString() + "','" + reader["email"].ToString() + "','" + reader["student_id"].ToString() + "','" + overrideIndicator + "')";

               command.ExecuteNonQuery();
            }

            validPersonCounter++;

            using (StreamWriter historyFile = new StreamWriter(@"c:\bb_updates\history.txt", true))
            {
                historyFile.WriteLine(this.firstName + status.ToLower() + " user ID for " + user + ", SIS Integration Override: " + didOverride + ", Date: " + mn + "/" + dy + "/" + yy + " Time: " + DateTime.Now.ToString("h:mm:ss tt"));
            }
        }
    }
    else
    {
        invalidPersons.Add(user);
    }                                  
}

My connection string has MultipleActiveResultSets=true; enabled, I've tried adding a close before command.ExecuteNonQuery() which then I get the error

Invalid attempt to read while reader is closed

If I put the reader.Close() anywhere else I get the already open data reader error. Please advise.

Upvotes: 0

Views: 274

Answers (2)

vcsjones
vcsjones

Reputation: 141678

You shouldn't be reusing your command object. When you do that, you are interfering with the result from the ExecuteReader call. Create a new SqlCommand inside your while loop instead.

Upvotes: 2

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Please try the following. It properly disposes of reader and does not reuse command.

 foreach (string user in usernames)
 {
     command.CommandText = "select case system_role when 'Z' then 'none' else 'none' end as 'system_role', case institution_roles_pk1 when 1 then 'student' when 2 then 'faculty' end as 'institution_role',batch_uid,user_id,student_id as 'passwd',firstname,middlename,lastname,email,student_id,case row_status when 0 then 'ENABLED' when 2 then 'DISABLED' end as 'row_status' from users where user_id = '" + user + "'";
     using (var reader = command.ExecuteReader())
     {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                file.WriteLine(user + "|" + status);
                if (overrideFramework == 0)
                {
                    var cmd = new SqlCommand();

                    cmd.CommandText = "use sis_comparison_data insert into person_override values ('" + reader["system_role"].ToString() + "','" + reader["institution_role"].ToString() + "','" + reader["batch_uid"].ToString() + "','" + reader["user_id"].ToString() + "','" + reader["passwd"].ToString() + "','" + reader["firstname"].ToString() + "','" + reader["middlename"].ToString() + "','" + reader["lastname"].ToString() + "','" + reader["email"].ToString() + "','" + reader["student_id"].ToString() + "','" + overrideIndicator + "')";

                    cmd.ExecuteNonQuery();
                }

                validPersonCounter++;

                using (StreamWriter historyFile = new StreamWriter(@"c:\bb_updates\history.txt", true))
                {
                    historyFile.WriteLine(this.firstName + status.ToLower() + " user ID for " + user + ", SIS Integration Override: " + didOverride + ", Date: " + mn + "/" + dy + "/" + yy + " Time: " + DateTime.Now.ToString("h:mm:ss tt"));
                }
            }

        }
        else
        {
            invalidPersons.Add(user);
        }  
     }
 }

Upvotes: 1

Related Questions