DataList binding : "The connection was not closed. The connection's current state is open."

I'm trying to bound a DataList control with selected data from a SQL Table:

     private void ShowPossiblePurchases(string CategoryName)
{

    string selectSQL = "SELECT TOP 2 * FROM Menu WHERE CategoryName=@CategoryName ORDER BY NEWID()";
    SqlCommand cmd = new SqlCommand(selectSQL, connection);
    cmd.Parameters.AddWithValue("@CategoryName", CategoryName);
    SqlDataReader reader;

    DataList DataList1 = (DataList)lgnView.FindControl("DataList1");

    try
    {

        connection.Open();
        reader = cmd.ExecuteReader();
        DataList1.DataSource = reader;
        DataList1.DataBind();

        reader.Close();

    }
    catch (Exception ex)
    {
        Label lblError = (Label)lgnView.FindControl("lblError");
        lblError.Text = ex.Message;
    }
    finally
    {
        connection.Close();
    }

When i run this code, i get "The connection was not closed. The connection's current state is open."

My previous version of the method was this:

    private void ShowPossiblePurchases(string CategoryName)

{

string selectSQL = "SELECT TOP 2 * FROM Menu WHERE CategoryName=@CategoryName ORDER BY NEWID()";
SqlCommand cmd = new SqlCommand(selectSQL, connection);
cmd.Parameters.AddWithValue("@CategoryName", CategoryName);
SqlDataReader reader;

DataSet myDataSet = new DataSet();
myDataSet.Tables.Add("Products");


myDataSet.Tables["Products"].Columns.Add("ProductID");
myDataSet.Tables["Products"].Columns.Add("CategoryID");
myDataSet.Tables["Products"].Columns.Add("ProductName");
myDataSet.Tables["Products"].Columns.Add("Price");

DataList DataList1 = (DataList)lgnView.FindControl("DataList1");

try
{
    connection.Open();
    reader = cmd.ExecuteReader();

    while (reader.Read())
    {

        DataRow rowNew = myDataSet.Tables["Products"].NewRow();
     开发者_如何学C   rowNew["ProductID"] = reader["ProductID"];
        rowNew["CategoryID"] = reader["CategoryID"];
        rowNew["ProductName"] = reader["ProductName"];
        rowNew["Price"] = reader["Price"];
        myDataSet.Tables["Products"].Rows.Add(rowNew);
    }

    DataList1.DataSource = myDataSet.Tables["Products"];
    DataList1.DataBind();
}
catch(Exception ex)
{
    Label lblError = (Label)lgnView.FindControl("lblError");
    lblError.Text = ex.Message;
}
finally
{
    connection.Close();
}

}

Try to First Close the connection than reader

You could check if the connection was closed first:

If ((cmd.Connection.State And System.Data.ConnectionState.Open) _
         <> System.Data.ConnectionState.Open) Then
    cmd.Connection.Open()
End If

C#:

if (((cmd.Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) {
    cmd.Connection.Open();
}

Where are you declaring connection?

It seems like you are declaring it outside of the code provided and opening the connection somewhere else before hand.

To avoid trying to open it twice, you can put in a simple check to see if the connection is open...

if (conn == null || conn.State == ConnectionState.Closed)
                OpenDBConnection();