Wiring up Search button form includes Textbox and Combobox

Tayyab Jatoi

I am trying to Wireup Search button my Form includes Textbox and combobox,but getting error when when trying to Search with ItemId.

Error: "The Connection was not closed.The Connections current state is open"

Search button Code

private void searchButton_Click(object sender, EventArgs e)

{

string Id = ItemIdValue.Text;

string command = "Select * from Item where ItemId = " + Id;

cmd = new SqlCommand(command, con);

if (con.State != ConnectionState.Open)

{

con.Open();

}

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

categoryNameDropDown.Text = dr[1].ToString();

categoryIdValue.Text = dr[2].ToString();

itemNameValue.Text = dr[3].ToString();

companyNameValue.Text = dr[4].ToString();

productNameValue.Text = dr[5].ToString();

purchasePriceValue.Text = dr[6].ToString();

salePriceValue.Text = dr[7].ToString();

reorderLevelValue.Text = dr[8].ToString();

openingQuantityValue.Text = dr[9].ToString();

unitValue.Text = dr[10].ToString();

}

else

{

MessageBox.Show("Record is not Exist", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

Reset();

}

dr.Close();

con.Close();

}

CategoryName Combobox Code

private void categoryNameDropDown_SelectedIndexChanged(object sender, EventArgs e)

{

cmd = new SqlCommand("select * from ItemCategory where CategoryName = '"+categoryNameDropDown.Text+"'",con);

con.Open(); -----Error is coming on this line "The Connection was not closed.The Connections current state is open"

cmd.ExecuteNonQuery();

SqlDataReader dr;

dr = cmd.ExecuteReader();

while (dr.Read())

{

string CategoryId = (string)dr["CategoryId"].ToString();

categoryIdValue.Text = CategoryId;

}

con.Close();

}

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta8y ago
Accepted answer
Hey

As you are using one connection object throughout your program, I will suggest you to create a seperate function and call it like below
public void GetConnection()
{
    if(con.State != ConnectionState.Open)
    con.Open();

}
Replace with above code where you are opening an connection
0
Tayyab Jatoi8y ago
Thank you Very Much Amit for your Solution
public void GetConnection()
{
if(con.State != ConnectionState.Open)
con.Open();

}
Your Advised Worked Successfuly Atlast
0
Laxmidhar Sahoo8y ago
Thank you Amit. You are correct
0
Tayyab Jatoi8y ago
I have put the breakpoint
private void categoryNameDropDown_SelectedIndexChanged(object sender, EventArgs e)
{
on this line-- cmd = new SqlCommand("select * from ItemCategory where CategoryName = '"+categoryNameDropDown.Text+"'",con);
it executing idealy like when it reaches Getconnection(); it goes to Constructor
InitializeComponent()
GetConnection(); --it goes to funciton but dont open the new connection
but when it comes back to below line than it gives Error ""THERE IS ALREADY AN OPEN DATAREADER ASSOCIATED WITH THIS COMMAND WHICH MUST BE CLOSE FIRST""
cmd.ExecuteNonQuery();

Please help.
0
Amit Gupta8y ago
Place a breakpoint at the function and check whats the connection state. As already aaid, this error occurs when your connection is opened and you are still opening an connection, please check those
0
Tayyab Jatoi8y ago
@Amit Thanks amit.

I have create a function
public void GetConnection()
{
if(con.State != ConnectionState.Open)
con.Open();

}
and Replace with con.open inside All button events.
But now when i search with ItemId the Category name populates but after that i am getting this error "THERE IS ALREADY AN OPEN DATAREADER ASSOCIATED WITH THIS COMMAND WHICH MUST BE CLOSE FIRST" down i have mentioned on which line i got this Error.

private void categoryNameDropDown_SelectedIndexChanged(object sender, EventArgs e)
{

cmd = new SqlCommand("select * from ItemCategory where CategoryName = '"+categoryNameDropDown.Text+"'",con);
GetConnectoin(); ------"REPLACED HERE AS YOU SUGGESTED"
cmd.ExecuteNonQuery(); ---on this line "THERE IS ALREADY AN OPEN DATAREADER ASSOCIATED WITH THIS COMMAND WHICH MUST BE CLOSE FIRST"

SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
string CategoryId = (string)dr["CategoryId"].ToString();
categoryIdValue.Text = CategoryId;
}
con.Close();
}

Please help.
0
Laxmidhar Sahoo8y ago
Why you do not check connect state in selectedIndexchange event
private void categoryNameDropDown_SelectedIndexChanged(object sender, EventArgs e)
{
cmd = new SqlCommand("select * from ItemCategory where CategoryName = '"+categoryNameDropDown.Text+"'",con);
if (con.State != ConnectionState.Open)
{
con.Open();
}

cmd.ExecuteNonQuery();
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
string CategoryId = (string)dr["CategoryId"].ToString();
categoryIdValue.Text = CategoryId;
}
con.Close();
}
0
Tayyab Jatoi8y ago
Thank you Amit.
But here Using statement will play the Role?
Because Once i click on Search button the Control goes to this line "categoryNameDropDown.Text = dr[1].ToString();" to fetch category name and for that it goes to this instance private void categoryNameDropDown_SelectedIndexChanged(object sender, EventArgs e) so in this way the Connection will still open from the Search Button instance.
I dont know how to deal with this because Combobox takes my control out of the Search button instance.
I will tell you Actualy i am planning to first Engineer Search button than on this base i will wireup update button.Can you reccomend me any link or design for this solution

Many thanks.
0
Amit Gupta8y ago
Connection was not closed somewhere, try uaing connection under using command or better use a separate DataAccess class that can handle all db related queries and its easy to maintain.
+1