Application hangs when used with different database

[Webskater]

I have a development box running Win7 and VS2010. A User Acceptance Testing server running Windows Server 2008 / Sql Server 2008 and a Live server running Windows Server 2008 / Sql 2008.

When doing development I connect to a development database on the UAT server.

I have a problem that only occurs when I change the connection string to point to the database on the Live Server.

So, before I start describing the problem - the app works fine in development and when published to the UAT server - but there is a problem when I either publish it to the live server or, whilst on my dev box, point the connection string to the live database.

Here's an example.

I have a page with a gridview on it. In each row of the gridview there is a drop down list. All calls to the database are done through a Data Access class.

Say I have a function on an .aspx.cs page called GetDetails().

In this - to populate the Gridview I'll have something like ...

DataAccess da = new DataAccess(); //new instance of Data Access class
gvDetails.DataSource = da.GetDetails(ProductID); //returns a SqlDataReader
gvDetails.DataKeyNames = new string[] { "DetailID", "Reference", "ReferenceID" };
gvDetails.DataBind();

And, in the RowDataBound event I'll have something like this:

protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        /*
        [0]DetailID
        [1]Reference
        [2]ReferenceID
        */
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataKey key = gvDetails.DataKeys[e.Row.RowIndex];
            DropDownList ddlReference = (DropDownList)e.Row.FindControl("ddlReference");
GF.PopulateDDL(ddlReference, key[2].ToString());
}
}

GF - is a Generic Function class. GFPopulateDDL takes a DropDownList and an ID and fetches a SqlDataReader with some data to populate the Dropdownlist.
So, as each row in the GridView is DataBound, a call to my generic function PopulateDDL - populates the DropDownList in each row.

I do this on dozens of pages in different apps and it works fine.

But, when I connect to the database on the Live Server - the application hangs when I call PopulateDDL. Within PopulateDDL it will have code like

public void PopulateDDL(DropDownList ddl, int ItemID)
{
DataAccess da = new DataAccess();
ddl.DataSource = da.GetddlItems(ItemID);
ddl.DataBind();
}

The problem occurs in the DataAccess class function that returns the datareader to populate the DropDownLists in each row. This looks like:

public SqlDataReader GetddlItems(string ItemGroup)
        {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(ConnectionString);
            SqlCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "GetddlItems";
            myCommand.CommandTimeout = 120;

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterItemGroup = new SqlParameter("@ItemGroup", SqlDbType.Int, 4);
            parameterItemGroup.Value = ItemGroup;
            myCommand.Parameters.Add(parameterItemGroup);

            // Execute the stored procedure and Return the datareader result
            myConnection.Open();
            return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }

(I didn't write that - but it works)

As soon as you hit myConnection.Open() - the application hangs. No error messages - nothing. As you step through the code you get returned to the browser and it just sits there forever with the progress bar going nowhere. BUT, it only happens in the specific situation of populating the dropdownlists in each row of the Gridview. On the page I am having problems with - PopulateDDL has already been called a couple of times, successfully, to populate DropDownLists outside of the Gridview.

Now, you might think this is a Connection issue - that a Connection is opened to populate the SqlDataReader that populates the Gridview and that, as you loop through the gridview, more connections are opened and closed to populate the DropDownLists. But, all functions that return SqlDataReaders are closed with
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
so Connections are definitely closed.

So ... sit there on my dev box and point at the database on UAT server - and it works fine. Point at the database on the Live server and, at that specific point (described above), it falls over. (Earlier calls to live database all work fine).

Same database (SQL Server 2008) - same operating system - Windows Server 2008 R2.

So, why does it hang on the live database on that particular myConnection.Open but not on the UAT or development database? And why does it only do it in that specific situation. Most database calls work fine. And why no error messages? You just get moved back to the browser - which has hung. On my development box I am having to restart IIS sometimes - one error message that does appear sometimes is to do with something (damn, can't remember the terminology) like - a debugger is already attached - something like that.

Any help much appreciated. At the end of my second day on this now.

By the way - it isn't anything to do with errors in the code above - I just typed that from memory - the actual code works fine on dozens and dozens of pages - it's just this issue with the different databases that is behind this.