Alin00
Alin00

Reputation: 23

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

I am creating a register form and I have to check if the email is valid and already used in my database. I figured out one method but I have errors in my code. If it's showing "something wrong" and after I try to use a new email address I have an error at DataReader and if I use directly a new email address it's showing an error at ExecuteNonQuery. Any thoughts?

My methods of validating the email address work ok separately. Thanks.

    public bool IsValidEmail(string emailadress)
    {
        try
        {
            MailAddress m = new MailAddress(emailadress);
            return true;
        }
        catch
        {
            return false;
        }
    }

    public bool good(string emailadress)
    {
        SqlCommand cmd = new SqlCommand("SELECT Email FROM Clients", con);
        SqlDataReader dr = cmd.ExecuteReader();//error is here

        while (dr.Read())
        {
            if (dr[0].ToString() == textBox6.Text)
            {
                return false;
            }
        }

        return true;
    }

    private void button1_Click(object sender, EventArgs e)
    {
        if (IsValidEmail(textBox6.Text) && good(textBox6.Text))
        {
            SqlCommand cmd1 = new SqlCommand("insert into Clienti(Name , Prename , Adress , Pass , Email ) values (@name , @prename ,@adress , @pass , @email)", con);
            cmd1.Parameters.AddWithValue("name", textBox1.Text);
            cmd1.Parameters.AddWithValue("prename", textBox2.Text);
            cmd1.Parameters.AddWithValue("adress", textBox3.Text);
            cmd1.Parameters.AddWithValue("pass", textBox4.Text);
            cmd1.Parameters.AddWithValue("email", textBox6.Text);

            cmd1.ExecuteNonQuery();    // error happens here

            cmd1.Dispose();
            MessageBox.Show("success");
        }
        else
            MessageBox.Show("something went wrong");
    }

Upvotes: 2

Views: 120

Answers (2)

Steve
Steve

Reputation: 216333

An SqlDataReader should be closed after its use, otherwise it keeps the connection locked and you cannot use that connection until you close it.
However you don't need an SqlDataReader to discover if you have already registered an email address

public bool good(string emailadress)
{
    string query = @"IF EXISTS(SELECT 1 FROM Clients WHERE Email = @email) 
                     SELECT 1 ELSE SELECT 0";
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = emailadress;
    int result = (int)cmd.ExecuteScalar();
    return (result == 1);
}

Keep in mind that disposable objects like the SqlCommand, the SqlDataReader but of uttermost importance, the SqlConnection should be created at the moment you need them and disposed immediately to free valuable resouces.
The code above with a connection created on the spot would be:

public bool good(string emailadress)
{

    string query = @"IF EXISTS(SELECT 1 FROM Clients WHERE Email = @email) 
                     SELECT 1 ELSE SELECT 0";
    using(SqlConnection con = new SqlConnection(....here put your connection string...))
    using(SqlCommand cmd = new SqlCommand(query, con))
    {
        con.Open();
        cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = emailadress;
        int result = (int)cmd.ExecuteScalar();
        return (result == 1);
   }
}

Upvotes: 1

Cem YILMAZ
Cem YILMAZ

Reputation: 134

Try closing your reader before you return a value from your good function. Like below:

        public bool good(string emailadress)
        {
            SqlCommand cmd = new SqlCommand("SELECT Email FROM Clients", con);
            SqlDataReader dr = cmd.ExecuteReader();//error is here
            while (dr.Read())
            {
                if (dr[0].ToString() == textBox6.Text)
                {
                    dr.Close();
                    return false;
                }
            }
            dr.Close();
            return true;
        }

Upvotes: 0

Related Questions