Changing DataDirectory does not load different data

James Buss 136 Reputation points
2026-06-17T04:33:17.3833333+00:00

When the user clicks File | Open, the user gets a FolderBrowserDialog to select a folder containing the MyData.mdf data file. After clicking OK, the app calls

AppDomain.CurrentDomain.SetData("DataDirectory", FolderBrowserDialog.SelectedPath)
OrganizationTableAdapter.Fill(MyDataSet.Organization)

On first run of the app, it works as expected. The data displayed is the data from the MyData.mdf file in the FolderBrowserDialog.SelectedPath folder.

But when the user subsequently clicks File | Open and selects a different folder, while same code executes to change the DataDirectory and Fill the OrganizationTableAdapter, the data displayed remains the same data as the first folder that was opened. The only way to open a different dataset is to exit the app and launch the app again.

OrganizationTableAdapter.ClearBeforeFill is set to True, so I expect the old data to be cleared out and the data from the new directory to be loaded in. That's not what is happening.

I don't understand what is happening or how to fix it.

Developer technologies | VB
0 comments

4 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-06-17T04:33:31.1433333+00:00

    AppDomain.CurrentDomain.SetData("DataDirectory", ...) only affects how connection strings that use |DataDirectory| are built when the connection is first created. Once the TableAdapter’s underlying SqlConnection has been created and opened, changing DataDirectory does not change the connection’s ConnectionString, nor does it force the adapter to rebuild or reopen it.

    Result: after the first Fill, the TableAdapter keeps using the original connection (pointing to the first MyData.mdf), so subsequent Fill calls still read from the first database even though DataDirectory has been changed.

    To load data from a different folder without restarting the app, the connection must be recreated (or its connection string updated) after changing DataDirectory. Typical patterns:

    1. Ensure the connection string in the TableAdapter uses |DataDirectory|.
    2. After calling SetData("DataDirectory", ...), force the TableAdapter to use a new SqlConnection built from that connection string, or dispose and recreate the DataSet/TableAdapter instance so that a new connection is created using the updated DataDirectory.
    3. Then call OrganizationTableAdapter.Fill(MyDataSet.Organization).

    ClearBeforeFill = True only clears the target DataTable before filling; it does not affect which database the adapter connects to.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. James Buss 136 Reputation points
    2026-06-17T04:36:50.3866667+00:00

    This makes sense. So, after calling SetData("DataDirectory", ...), how do I force the TableAdapter to use a new SqlConnection built from that connection string? What code do I need to do that? The TableAdapter is created in the Designer not through code.

    Was this answer helpful?

    0 comments
  3. Senthil kumar 2,410 Reputation points
    2026-06-17T05:05:15.27+00:00

    Hi @James Buss

    try this below code will helpful to you. 

    Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
    Dim ofd As New OpenFileDialog
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim tableadapter As SqlDataAdapter

    Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
        ofd.Filter = "MDF Files (*.mdf)|*.mdf"
        If ofd.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim fileName As String = ofd.FileName
            Dim Connstr As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={" & fileName & "};Integrated Security=True;Connect Timeout=30;"
            Using conn = New SqlConnection(Connstr)
                conn.Open()
                Using tableadapter = New SqlDataAdapter("yourquery or storedprocedure", conn)

					// here do your stuff.. 

                End Using
                conn.Close()
            End Using
        End If
    End Sub

End Class

    Was this answer helpful?

    0 comments
  4. Nancy Vo (WICLOUD CORPORATION) 8,155 Reputation points Microsoft External Staff Moderator
    2026-06-17T09:03:39.4333333+00:00

    Hello @James Buss ,

    Thanks for your question.

    I recommend investigating the connection string resolution and connection pooling behavior. The DataDirectory token in the connection string appears to be resolved during the initial connection establishment. ADO.NET's connection pooling likely caches this connection with the originally resolved path. When you update the DataDirectory value, I suspect the pooled connection continues referencing the initial database file instead of re-evaluating the token with the new path.

    You can refer to my following example code:

    Imports System.Data.SqlClient

Private Sub LoadDatabaseFromFolder()
    If FolderBrowserDialog1.ShowDialog() <> DialogResult.OK Then Return

    If OrganizationTableAdapter.Connection IsNot Nothing Then
        OrganizationTableAdapter.Connection.Close()
    End If
    OrganizationTableAdapter.Dispose()

    SqlConnection.ClearAllPools()

    AppDomain.CurrentDomain.SetData("DataDirectory", FolderBrowserDialog1.SelectedPath)

    OrganizationTableAdapter = New OrganizationTableAdapter()

    MyDataSet.Organization.Clear()
    OrganizationTableAdapter.Fill(MyDataSet.Organization)
End Sub

    I hope this addresses your question. If this response was helpful, please consider following the guidance to provide feedback.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.