ConnectionString not initiated after inserting!

I'm having this little issue : each time my code executes the InsertOrder() routine below, i get this exception message : "connection string has not been initiated".

Here's the code of InsertOrder() method:

  private void InsertOrder()
{
    string insertSQL = "";

    insertSQL += "INSERT INTO Orders (";
    insertSQL += "UserName, DateCreated, LastUpdate, Description, PaymentType, Delivery, Total) ";
    insertSQL += "VALUES (@UserName, @DateCreated, @LastUpdate, @Description, @PaymentType, @Delivery, @Total)";

    SqlCommand cmd0 = new SqlCommand(insertSQL, connection);

    // Adds the parameters
    cmd0.Parameters.AddWithValue("@UserName", Profile.UserName.ToString());
    cmd0.Parameters.AddWithValue("@DateCreated", Profile.Orders.SCart.DateCreated());
    cmd0.Parameters.AddWithValue("@LastUpdate", Profile.Orders.SCart.LastUpdate());
    cmd0.Parameters.AddWithValue("@Description", Profile.Orders.SCart.GetCartDescription());
    cmd0.Parameters.Add("@PaymentType", SqlDbType.Bit).Value = Profile.Orders.SCart.PaymentType;
    cmd0.Parameters.Add("@Delivery", SqlDbType.Bit).Value = Profile.Orders.SCart.Delivery;
    cmd0.Parameters.AddWithValue("@Total", Profile.Orders.SCart.Total);

    try
    {
        using (connection)
        {
            connection.Open();
            cmd0.ExecuteNonQuery();
        }

        string selectSQL = "SELECT OrderID FROM Orders WHERE UserName=@UserName AND DateCreated=@DateCreated";
        SqlCommand cmd1 = new SqlCommand(selectSQL, connection);

        cmd1.Parameters.AddWithValue("@UserName", Profile.UserName);
        cmd1.Parameters.AddWithValue("@DateCreated", Profile.Orders.SCart.DateCreated());
        SqlDataReader reader;

        using (connection)
        {
            connection.Open();
            reader = cmd1.ExecuteReader();
            reader.Read();
            OrderID = (int)reader["OrderID"];
            reader.Close();
        }

        // Store registered customer information for later usage..
        if (!Profile.IsAnonymous)
        {
            string insertSQL2 = "";

            insertSQL2 += "INSERT INTO CategoriesInAnOrder (";
            insertSQL2 += "OrderID, CategoryID, Quantity) VALUES (@OrderID, @CategoryID, @Quantity)";

            foreach (CartItem item in Profile.Orders.SCart.Items)
            {
                SqlCommand cmd2 = new SqlCommand(insertSQL2, connection);
                cmd2.Parameters.AddWithValue("@OrderID", OrderID);
                cmd2.Parameters.AddWithValue("@CategoryID", item.CategoryID);
                cmd2.Parameters.AddWithValue("@Quantity", item.Quantity);
                using (connection)
                {
           开发者_C百科         connection.Open();
                    cmd2.ExecuteNonQuery();
                }
            }
        }
    }
    catch (Exception err)
    {
        pnlWizard.Visible = false;
        lblError.Text = err.Message;
    }
}  

Probabily it's worth saying i have placed a SqlConnection object inside my SuperClass, so every child class (like the one which contains the above method) inherits this attribute.

The shared SqlConnection object is set as follows:

    protected SqlConnection connection = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString);

Sorry for my english... i'm italian

---

I think the

using(connection)

may be the source of your problem.

The using() is going to cause Dispose() to be called on the connection when the call has completed, but your connection is only being instantiated once.

Next time you come to use it, it's not going to be in a healthy state!

Consider creating a new connection each time:

protected SqlConnection connection 
{
    get
    {
        return new SqlConnection
          (System.Web.Configuration.WebConfigurationManager.ConnectionStrings 
          ["DefaultConnectionString"].ConnectionString); 
    }
}

---

I suspect the problem is that you are using the value from a field. That will work once, but after the Dispose() you have doomed that connection.

For this scenario, I use a custom EnsureOpen() method that returns an IDisposable if it wasn't already open; usage:

using(connection.EnsureOpen())
{
    // use it
}

This way the connection isn't disposed prematurely but is open when needed. Plus it works whether it was already open or not.

I can share EnsureOpen later if you want.