There is already an openDataReader

Question

0.00/5 (No votes)

See more:

Dear All,

i m using this code.

private void btnSave_Click(object sender, EventArgs e)
{
try
{
string connstr = @"Server=.\SQLEXPRESS ;Initial Catalog=RPSJDB;Integrated Security=True; Max Pool Size=100";
string query = "insert into CustomerTable values('" + txtCustomerName.Text + "','" + txtAddress.Text + "','"
+ txtMobileNo.Text + "','" + lblGoldBalance.Text + "','" + lblSilverBalance.Text + "','" + lblCashBalance.Text + "')";
SqlConnection conn = new SqlConnection(connstr);

if (txtCustomerName.Text != "" & txtAddress.Text != "")
{
MessageBox.Show("Enter some input");
conn.Open();
//Checking User Name Exists in CustomerTable

string query1 = "select txtCustomerName from CustomerTable where txtCustomerName='" + txtCustomerName.Text + "'";
SqlCommand cmd = new SqlCommand(query1,conn);
SqlDataReader reader = cmd.ExecuteReader();

if (reader != null && reader.HasRows)
{
//User exists in db do something
MessageBox.Show("User Already Exists!!");
}

else
{
SqlCommand cmd1 = new SqlCommand(query, conn);
cmd1.ExecuteNonQuery();
txtCustomerName.Clear();
txtAddress.Clear();
txtMobileNo.Clear();
MessageBox.Show("Values Save in DataBase");
}
reader.Close();
conn.Close();
}
else
{
MessageBox.Show("Only Mobile Field Can be Empty");
}
}
catch (Exception ex)
{
MessageBox.Show("At Least 0 value can be save in the Gold Silver Cash Area", ex.Message);
}

it show an error when i am fill only Customer name value then save (here it's show an error "only Mobile field can be empty" but it is not show.
it show a new error is : "There is already an openDataReader associated with this command which must be close first." i am already close the reader.

Posted 2-Sep-13 20:26pm

Manish Arya

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

idenizeni · Accepted Answer · 2013-09-02T20:38:00

Solution 1

You should use try/catch/finally and not just try/catch. If you encounter an error then the connection and reader are never closed. If you use finally then regardless if there is an error or not the reader and connection will be closed. You will also need to declare your reader and connection objects above the try/catch/finally block. Like so...

C#

SqlConnection conn = null; // declare here but set in try block
SqlDataReader reader = null; // declare here but set in try block
try
{
 // ...
}
catch
{
    MessageBox.Show("At Least 0 value can be save in the Gold Silver Cash Area", ex.Message);
}
finally
{
    if(reader != null)
    {
        reader.Close();
    }

    if(conn != null)
    {
        conn.Close();
    }
}

Also, close the reader before you attempt to make another connection.

C#

if (reader != null && reader.HasRows)
{
//User exists in db do something
MessageBox.Show("User Already Exists!!");
}
else
{
    if(reader != null)
    {
        reader.Close(); // close the reader before making a new connection
    }
    SqlCommand cmd1 = new SqlCommand(query, conn);
    cmd1.ExecuteNonQuery();
    txtCustomerName.Clear();
    txtAddress.Clear();
    txtMobileNo.Clear();
    MessageBox.Show("Values Save in DataBase");
}

Posted 2-Sep-13 20:38pm

idenizeni

Updated 2-Sep-13 20:56pm

v3

Comments

Manish Arya 3-Sep-13 2:49am

same problem

private void btnSave_Click(object sender, EventArgs e)
{
string connstr = @"Server=.\SQLEXPRESS ;Initial Catalog=RPSJDB;Integrated Security=True; Max Pool Size=100";
SqlDataReader reader = null;
SqlConnection conn = null;

try
{
string query = "insert into CustomerTable values('" + txtCustomerName.Text + "','" + txtAddress.Text + "','"
+ txtMobileNo.Text + "','" + lblGoldBalance.Text + "','" + lblSilverBalance.Text + "','" + lblCashBalance.Text + "')";
conn = new SqlConnection(connstr);

if (txtCustomerName.Text != "" & txtAddress.Text != "")
{
conn.Open();
//Checking User Name Exists in CustomerTable

string query1 = "select txtCustomerName from CustomerTable where txtCustomerName='" + txtCustomerName.Text + "'";
SqlCommand cmd = new SqlCommand(query1,conn);
reader = cmd.ExecuteReader();

if (reader != null && reader.HasRows)
{
//User exists in db do something
MessageBox.Show("User Already Exists!!");
}

else
{
SqlCommand cmd1 = new SqlCommand(query, conn);
cmd1.ExecuteNonQuery();
txtCustomerName.Clear();
txtAddress.Clear();
txtMobileNo.Clear();
MessageBox.Show("Values Save in DataBase");
}
reader.Close();
conn.Close();
}
else
{
MessageBox.Show("Only Mobile Field Can be Empty");
}
}
catch (Exception ex)
{
MessageBox.Show("At Least 0 value can be save in the Gold Silver Cash Area", ex.Message);
}
finally
{
reader.Close();
conn.Close();
}

idenizeni 3-Sep-13 2:57am

Close the reader before you make another connection, see my updated solution.