Connection String not close

Emmmanuel FIADUFE

2y816C#

Hello Team

I want to add product to list in a textbox by earsching for the product or scanning the barcode before selling but when I tried to search for product it says connection not close.

private void txt_Search_Product_Barcode_KeyDown(object sender, KeyEventArgs e)
{

try
{
if (txt_Search_Product_Barcode.Text == String.Empty)
{
return;
}
else
{
String _pcode;
String _productname;
Decimal _price;
int qty;
cn.Open();
cm = new SqlCommand("select * from tblProduct where Barcode like '" + txt_Search_Product_Barcode.Text + "'", cn);
dr = cm.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
// validate product quantity to show avoid selling when product is out of stock

qty = int.Parse(dr["StockIn"].ToString());
_pcode = dr["PCode"].ToString();
_productname = dr["ProductName"].ToString();
_price = decimal.Parse(dr["SalesPrice"].ToString());
dr.Close();
cn.Close();

//ADDToLIST(_pcode, _price, _qty);

}
else
{
dr.Close();
cn.Close();
}

}

}
catch (Exception ex)
{
dr.Close();
cn.Close();
MessageBox.Show(ex.Message, stitle, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
}

3 Replies

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

Amit Mohanty2y ago

Accepted answer

Try the below code:

private void txt_Search_Product_Barcode_KeyDown(object sender, KeyEventArgs e)
{
    if (txt_Search_Product_Barcode.Text == String.Empty)
    {
        return;
    }

    try
    {
        using (SqlConnection cn = new SqlConnection("your_connection_string_here"))
        {
            cn.Open();
            using (SqlCommand cm = new SqlCommand("select * from tblProduct where Barcode like @barcode", cn))
            {
                cm.Parameters.AddWithValue("@barcode", txt_Search_Product_Barcode.Text);
                
                using (SqlDataReader dr = cm.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        // Validate product quantity to avoid selling when the product is out of stock
                        int qty = int.Parse(dr["StockIn"].ToString());
                        string _pcode = dr["PCode"].ToString();
                        string _productname = dr["ProductName"].ToString();
                        decimal _price = decimal.Parse(dr["SalesPrice"].ToString());

                        // Close the reader before processing
                        dr.Close();

                        // ADDToLIST(_pcode, _price, qty);
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
    }
}

You should use parameterized queries instead of directly embedding values into your SQL statements.

Tuhin Paul2y ago

private void txt_Search_Product_Barcode_KeyDown(object sender, KeyEventArgs e)
{
    if (string.IsNullOrEmpty(txt_Search_Product_Barcode.Text))
    {
        return;
    }

    try
    {
        string connectionString = "your_connection_string_here";
        
        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            cn.Open();
            
            using (SqlCommand cm = new SqlCommand("SELECT * FROM tblProduct WHERE Barcode LIKE @barcode + '%'", cn))
            {
                cm.Parameters.AddWithValue("@barcode", txt_Search_Product_Barcode.Text);

                using (SqlDataReader dr = cm.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        // Validate product quantity to avoid selling when the product is out of stock
                        int qty = int.Parse(dr["StockIn"].ToString());
                        // string _pcode = dr["PCode"].ToString();
                        // string _productname = dr["ProductName"].ToString();
                        // decimal _price = decimal.Parse(dr["SalesPrice"].ToString());

                        // Close the reader before processing
                        dr.Close();

                        // ADDToLIST(_pcode, _price, qty);
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        // Log the exception details for further investigation
    }
}

code improvement.

Emmmanuel FIADUFE2y ago
It is working now, thank you Mr. Amit