Passing Connection From TableAdapter Produces connectionstring property has not been initialized

Question

I ran into a problem where passing a connection from a TableAdapter to some methods throws an exception stating the connectionstring isn't initialized. There are quite a few questions on SO with this exception but none were passing the connection and most were because the ConnectionString was null. Weird thing is I used MessageBox.Show(connection.ConnectionString); through out the chain of methods and I receive a valid connection string at every step. This is a somewhat complicated program that is in production but I will try to simplify the code for this question...

This is the postInventoryData method, which takes a DataGridView with inventory items and iterates through it posting them to the inventory. I use a TransactionScope to ensure the changes are safely rolled back in the event of an error. If an item is a kit(an item comprised of other items) I must iterate through those items and remove them from the inventory. The problem occurs when I check whether or not the item is a kit.

    public bool postInventoryData(DataGridView dgv)
    {
        bool successful = true;

        TestDataSetTableAdapters.inentoryTrxTableAdapter inventoryTrxAdapter = 
                        new TestDataSetTableAdapters.inentoryTrxTableAdapter();

        try
        {
            using (TransactionScope trxScope = new TransactionScope
                         (TransactionScopeOption.Required, new System.TimeSpan(0, 15, 0)))
            {
                MessageBox.Show(inventoryTrxAdapter.Connection.ConnectionString); // <-- Valid ConnectionString

                inventoryTrxAdapter.OpenConnection();

                for (int i = 0; i < dgv.Rows.Count; i++)
                {
                    //parameter values
                    string departmentCode = dgv.Rows[i].Cells["Department_Code"].Value.ToString();
                    string machineCode = dgv.Rows[i].Cells["Machine_Code"].Value.ToString();
                    string operatorCode = dgv.Rows[i].Cells["Operator_Code"].Value.ToString();
                    string itemNumber = dgv.Rows[i].Cells["Item_Number"].Value.ToString();
                    double? qtyProduced = Convert.ToDouble(dgv.Rows[i].Cells["Quantity"].Value.ToString());
                    bool isKit = 
                       businessLayer.isItemNumberKit
                       (inventoryTrxAdapter.Connection, itemNumber); // <-- CULPRIT!

                    // Inserts the item
                    dailyProductionInsertQty(
                        departmentCode,
                        machineCode,
                        operatorCode,
                        itemNumber,
                        isKit,
                        qtyProduced,
                        inventoryTrxAdapter,
                        trxScope);
                }

                inventoryTrxAdapter.CloseConnection();
                trxScope.Complete();
            }
        }
        catch (System.Exception ex)
        {
            successful = false;

            MessageBox.Show(ex.ToString());
        }

        return successful;
    }

The isItemNumberKit method

    public bool isItemNumberKit(SqlConnection connection, string itemNumber)
    {
        bool contains;

        MessageBox.Show(connection.ConnectionString); // <-- Valid ConnectionString

        DataTable dt = getKit(connection, itemNumber); // <-- CULPRIT!
        if (dt.Rows.Count > 0)
        {
            contains = true;
        }
        else
        {
            contains = false;
        }

        return contains;
    }

The getKit method

    public DataTable getKit(SqlConnection connection, string itemNumber)
    {
        DataTable dt = new DataTable();

        SqlConnection myConnection = connection;

        MessageBox.Show(myConnection.ConnectionString); // <-- Valid ConnectionString

        SqlParameter paramItemNumber = new SqlParameter();
        paramItemNumber.ParameterName = "@ItemNumber";
        paramItemNumber.Value = itemNumber;
        paramItemNumber.SqlDbType = System.Data.SqlDbType.VarChar;

        try
        {
            using (myConnection)
            {
                string sql =
                        @"SELECT kits.Row_Id, 
                          kits.Kit_Item_Number, 
                          kits.Location_Code        
                          FROM Inventory.dbo.Z_PV_Kits kits 
                          WHERE kits.Kit_Item_Number=@ItemNumber";
                //myConnection.Open();

                using (SqlCommand myCommand = new SqlCommand(sql, myConnection))
                {
                    myCommand.Parameters.Add(paramItemNumber);
                    SqlDataReader reader = myCommand.ExecuteReader();
                    dt.Load(reader);
                }
            }
        }
        catch (Exception ex)
        {
            dt = null;
            MessageBox.Show(ex.ToString());
        }

        return dt;
    }

When I execute postInventoryData the program throws an exception with the message, "The connectionstring property has not been initialized." with the line numbers pointing to isItemNumberKit and getKit. As you can see in the code above, I used a MessageBox.Show(connection.ConnectionString) throughout the process and each time I received a valid Connection string. I have created a workaround which stores a cached DataTable containing all the kit items I can run linq statements on. I am not in emergency mode or anything but I thought this to be weird and an opportunity for me to learn. Thanks in advance for any help!

Answer 1

OK, I figured it out and now when I think about it the answer was somewhat obvious. I always use using(){} blocks to ensure connections and similar objects are properly disposed and taken care of after they are used. The solution was to simply remove the using(myConnection){} block from the getKit method like this:

    public DataTable getKit(SqlConnection connection, string itemNumber)
    {
        DataTable dt = new DataTable();

        SqlConnection myConnection = connection;

        MessageBox.Show(myConnection.ConnectionString);

        SqlParameter paramItemNumber = new SqlParameter();
        paramItemNumber.ParameterName = "@ItemNumber";
        paramItemNumber.Value = itemNumber;
        paramItemNumber.SqlDbType = System.Data.SqlDbType.VarChar;

        try
        {
            string sql =
@"SELECT    kits.Row_Id, 
        kits.Kit_Item_Number, 
        kits.Location_Code      
FROM    Inventory.dbo.Z_PV_Kits kits 
WHERE kits.Kit_Item_Number=@ItemNumber 
";
            //myConnection.Open();

            using (SqlCommand myCommand = new SqlCommand(sql, myConnection))
            {
                myCommand.Parameters.Add(paramItemNumber);
                SqlDataReader reader = myCommand.ExecuteReader();
                dt.Load(reader);
            }
        }
        catch (Exception ex)
        {
            dt = null;
            MessageBox.Show(ex.ToString());
        }

        return dt;
    }

This will leave the connection intact but properly dispose of the command. Sorry for the long winded question with a short simple answer. Hope this might help someone someday.

Answer 2

It might be possible that you have 2 app.config files in your solution with 2 different connection strings.