Changing DataDirectory does not load different data

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.

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

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.