CODEWITHSUNDEEP
c#sqlasp.netsql-serversqldatareader
amadeus
amadeus

Reputation: 39

C# ASP.NET error: There is already an open datareader associated with this command which must be closed first

I keep getting this error

There is already an open datareader associated with this command which must be closed first.

at this line of code:

using (SqlDataReader rd = command.ExecuteReader())

I have tried to close all other SqlDataReader's in class but it didn't work.

public int SifreGetir(string pEmail) {
    SqlCommand command = con.CreateCommand();

    command.CommandText = @"SELECT Sifre FROM Kullanici WITH (NOLOCK) WHERE email=@email";

    command.Parameters.Add("@email", SqlDbType.VarChar);
    command.Parameters["@email"].Value = pEmail;

    using (SqlDataReader rd = command.ExecuteReader())
    {
        rd.Read();
        string pass = rd["Sifre"].ToString();
        int p = Convert.ToInt32(pass);

        return p;
    }
}

Upvotes: 0

Views: 3958

Answers (3)

JC Borlagdan
JC Borlagdan

Reputation: 3618

try this:

public int SifreGetir(string pEmail) {

            SqlConnection con = new SqlConnection("Your connection string here");
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SqlCommand command = new SqlCommand(@"SELECT Sifre FROM Kullanici WITH (NOLOCK) WHERE email=@email",con);
            command.CommandType = CommandType.Text;
            command.Parameters.Add("@email", SqlDbType.VarChar).Value = pEmail;

            da.Fill(ds);
            foreach(DataRow dr in ds.Tables[0].Rows)
           {
                string pass = dr["Sifre"].ToString();
                int p = Convert.ToInt32(pass);
                return p;
           }
           con.Close();
        }

Upvotes: 0

Suraj Singh
Suraj Singh

Reputation: 4059

You have used Using Keyword for SQL Reader but There is nothing to take care of your command and connection object to dispose them properly. I would suggest to try disposing your Connection and command both objects by Using keyword.

string connString = "Data Source=localhost;Integrated " +   "Security=SSPI;Initial Catalog=Northwind;";

using (SqlConnection conn = new SqlConnection(connString))
{
  SqlCommand cmd = new;
  cmd.CommandText = "SELECT ID, Name FROM Customers";

  conn.Open();

  using (SqlDataReader rd = command.ExecuteReader())
    {
        rd.Read();
        string pass = rd["Sifre"].ToString();
        int p = Convert.ToInt32(pass);

        return p;
    }
}

Upvotes: 0

Trupti
Trupti

Reputation: 128

Try implementing your code in the below format

using(SqlConnection connection = new SqlConnection("connection string"))
{

    connection.Open();

    using(SqlCommand cmd = new SqlCommand("your sql command", connection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader != null)
            {
                while (reader.Read())
                {
                    //do something
                }
            }
        } 

    } 

}

The using statement will ensure disposal of the objects at the end of the using block

Upvotes: 1

Related Questions