ADO OleDB Connection String Not Updating

Question

I have an ASP.NET C# application where I'd like to allow the user to retrieve data from both a Test environment and a Production environment. The app gives the user a dropdown with an option for either environment. The database connection is through a custom ADO provider that I use an OleDbConnection to query. The ADO was provided by a vendor, and I format a Connection String for each environment that looks like this:

string strProd = "Provider={ProviderName};Data Source={Production_Server};Password={Password};User ID={UserId}";
string strTest = "Provider={ProviderName};Data Source={Test_Server};Password={Password};User ID={UserId}";

Everything works fine the very first time that it connects. So, if I compile and deploy the application and the user first requests production data, it pulls data from the Production environment. However, when the user tries to pull data from the Test environment, the data retrieved again comes from the Production environment, even though the Test Environment's Connection String has been set.

Here's an example of how I'm retrieving the data - the applicable Connection String is passed to the function:

protected void Page_Load(object sender, EventArgs e)
{

  string strProd = "Provider={ProviderName};Data Source={Production_Server};Password={Password};User ID={UserId}";
  string strTest = "Provider={ProviderName};Data Source={Test_Server};Password={Password};User ID={UserId}";

  var prod = GetData(strProd); // returns Prod data
  var uat = GetData(strTest); // also returns Prod data ??

}

public DataTable GetData(string connStr) {
  OleDbConnection conn = new OleDbConnection(connStr);
  OleDbCommand cmd = new OleDbCommand("select top 10 field from table", conn);
  OleDbDataAdapter da = new OleDbDataAdapter(cmd);
  DataTable dt = new DataTable();
  da.Fill(dt);
  return dt;
}

I'm positive that I'm passing the applicable Connection String, but it seems like the Data Source (and User Id/Password) are sticking with whatever was set the first time with this specific Provider. If I change the Provider to, for example, a Microsoft.Jet.OLEDB.4.0 provider and read a CSV file, it does read that CSV file and correctly retrieves the CSV data. But then if I switch back to the custom Provider, it's still stuck on the first Connection String values, even if I set the Connection String to the other environment.

When I'm debugging and completely restart the application, if I connect to the Test environment first it will stick with the Test environment and won't update to Production, and if I connect to the Production environment first it will stick with the Production environment. I've tried various ways to use the connection (using conn.Open(), conn.Close(), conn.Dispose(), etc.), but the data retrieved is always from the first attempt's Connection String.

I'm not ruling out that this is a bug with the custom ADO provider, but is there anything else that I can try? Is it possible that the values are being cached, and if so, is there a way to prevent that behavior? I'm stumped.

Edit: I separated the connections into separate classes per a suggestion by @jdweng - this did not solve the problem.

  public partial class Test : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void btn_Click(object sender, EventArgs e) {

      string connStr = string.Format("Provider=ProviderName;Data Source={0};Password=Password;User ID=UserId", ddlEnv.SelectedValue);
      GetEnvData envData = new GetEnvData();
      DataTable dt = envData.GetData(connStr);

    }
  }

  public class GetEnvData {
    public DataTable GetData(string connStr) {
      OleDbConnection conn = new OleDbConnection(connStr);
      OleDbCommand cmd = new OleDbCommand("select top 10 field from table", conn);
      OleDbDataAdapter da = new OleDbDataAdapter(cmd);
      DataTable dt = new DataTable();
      da.Fill(dt);
      return dt;
    }
  }

Edit: Adding Web.Config for inspection:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <system.web>
        <compilation debug="true" targetFramework="4.7.2" />
        <httpRuntime targetFramework="4.7.2" />
        <authentication mode="Windows" />
        <authorization>
            <deny users="?"/>
        </authorization>        
        <identity impersonate="true"/>
    </system.web>
    <system.codedom>
        <compilers>
            <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
            <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" />
        </compilers>
    </system.codedom>
    <system.webServer>
        <validation validateIntegratedModeConfiguration="false" />
    </system.webServer>
</configuration>

Answer 1

Gee, that is a mystery. It is possible that the custom "diver" you have keeps a connection open, perhaps based on the "sql", and if the "sql" is the SAME , then it uses the previous sql data for performance.

I would thus consider trying "different" sql for each try. If you send the exact same sql, it possible some kind of cache is used, and connection is ignored.

I would also wrap the code in a using which will close the conneciton, and ALSO dispose of the object correctly, and hence this:

public DataTable GetData(string connStr)
{
    DataTable dt = new Datatable();
    using (OleDbConnection conn = new OleDbConnection(connStr)) 
    {
        using (OleDbCommand cmd = new OleDbCommand("select top 10 field from table", conn))
        {
           conn.open();
           dt.load(cmd.ExecuteReader());
        }
     }
     return dt;
}