Hi,
I am using VB 2005. My application has many data bound controls. The
connection is stored in the app.config file.

I want the application to start with a default connection string and
while during the runtime, the user can click on a button and change the
connection string without exiting the application.


I would really appreciate any sort of help.

I have already something working but this requires the application to
close and restart. ( If i dont close and restart the application dosent
change the connection string) . Here is the code


locatedatabase.vb

Imports System.Xml
Public Class LocateDatabase
    Private Const AuthenticationTypeWindows As Integer = 1
    Private Const AuthenticationTypeSQL As Integer = 2
    Dim ProgramTitle As String = "BDM Security"

    Public Function PromptUser() As String
        ' ----- Prompt the user for database connection details. Return
a
        '       valid ADO.NET connection string for SQL Server, or a
blank
        '       string if the user clicks Cancel.

        Dim newConnection As String
        ' ----- Prompt the user.
        Try
            Me.ShowDialog()
        Catch ex As Exception

        End Try


        ' ----- Build the new connection string.
        If (Me.DialogResult = Windows.Forms.DialogResult.OK) Then

            newConnection = "Data Source=" & Trim(server.Text) & _
                ";Initial Catalog=" & Trim(database_name.Text)
            If authentication.SelectedIndex = 0 Then
                ' ----- Use Windows security.
                newConnection &= ";Integrated Security=true"
            Else
                ' ----- Use SQL Server security.
                newConnection &= ";User ID=" & Trim(user_id.Text) & _
                    ";Password=" & Trim(password.Text)
            End If
            Return newConnection
        Else
            Return ""
        End If
    End Function

    Private Sub LocateDatabase_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
        ' ----- Prepare the form.
        Dim counter As Integer
        Dim connectionString As String
        Dim oneKey As String
        Dim oneValue As String
        On Error Resume Next
        ' ----- Load in the existing data.

        connectionString = My.Settings.con & ""
        For counter = 1 To CountSubStr(connectionString, ";") + 1
            ' ----- Each comma-delimited part has the format
"key=value".
            oneKey = GetSubStr(connectionString, ";", counter)
            oneValue = Trim(GetSubStr(oneKey, "=", 2))
            oneKey = Replace(UCase(Trim(GetSubStr(oneKey, "=", 1))), "
", "")

            ' ----- Process each part.
            Select Case oneKey
                Case "DATASOURCE"
                    ' ----- Show the server host.
                    server.Text = oneValue
                Case "INITIALCATALOG"
                    ' ----- Show the default database name.
                    database_name.Text = oneValue
                Case "INTEGRATEDSECURITY"
                    ' ----- Only check for "true". False is assumed.
                    If (UCase(oneValue) = "TRUE") Then _
                       authentication.SelectedIndex = 0
                Case "USERID"
                    ' ----- A user ID forces SQL authentication.
                    authentication.SelectedIndex = 1
                    user_id.Text = oneValue
                Case "PASSWORD"
                    ' ----- A password forces SQL authentication.
                    authentication.SelectedIndex = 1
                    password.Text = oneValue
            End Select
        Next counter
    End Sub

    Private Sub RecordDatabase_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles database_name.Enter
        ' ----- Highlight the entire text.
        database_name.SelectAll()
    End Sub

    Private Sub RecordPassword_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles password.Enter
        ' ----- Highlight the entire text.
        password.SelectAll()
    End Sub

    Private Sub RecordServer_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles server.Enter
        ' ----- Highlight the entire text.
        server.SelectAll()
    End Sub

    Private Sub RecordUser_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles user_id.Enter
        ' ----- Highlight the entire text.
        user_id.SelectAll()
    End Sub

    Private Sub RecordAuthentication_SelectedIndexChanged(ByVal sender
As Object, ByVal e As System.EventArgs) Handles
authentication.SelectedIndexChanged
        ' ----- Enable dependent fields as needed.
        If authentication.SelectedIndex = 0 Then
            ' ----- Windows authentication requires no user/password.
            Label4.Enabled = False
            user_id.Enabled = False
            Label5.Enabled = False
            password.Enabled = False
        Else
            ' ----- Requires specific SQL Server user and password.
            Label4.Enabled = True
            user_id.Enabled = True
            Label5.Enabled = True
            password.Enabled = True
        End If
    End Sub
    Private Sub ActOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles ActOK.Click
        'Clear the previous connection string

        Dim con_Z As String
        con_Z = My.Settings.con
        My.Settings.con = ""
        If (ValidateFormData() = False) Then Return
        Me.DialogResult = Windows.Forms.DialogResult.OK
        If Form1.ConnectDatabase() = False Then
            My.Settings.con = con_Z
            Me.Close()
            Exit Sub
        End If
        Form1.Close()

        Me.Close()
    End Sub
    Public Function CountSubStr(ByVal mainText As String, ByVal subText
As String) As Integer
        ' ----- Return a count of the number of times that a subText
occurs in
        '       a string (mainText).
        Dim totalTimes As Integer
        Dim startPos As Integer
        Dim foundPos As Integer

        totalTimes = 0
        startPos = 1

        ' ----- Keep searching until we don't find it no more!
        Do
            ' ----- Search for the subText.
            foundPos = InStr(startPos, mainText, subText)
            If (foundPos = 0) Then Exit Do
            totalTimes = totalTimes + 1

            ' ----- Move to just after the occurrence.
            startPos = foundPos + Len(subText)
        Loop

        ' ----- Return the count.
        Return totalTimes
    End Function

    Public Function GetSubStr(ByVal origString As String, ByVal delim
As String, _
           ByVal whichField As Integer) As String
        ' ----- Extracts a delimited string from another larger string.
        Dim stringParts() As String

        ' ----- Handle some errors.
        If (whichField < 0) Then Return ""
        If (Len(origString) < 1) Then Return ""
        If (Len(delim) = 0) Then Return ""

        ' ----- Break the string up into delimited parts.
        stringParts = Split(origString, delim)

        ' ----- See if the part we want exists and return it.
        If (whichField > UBound(stringParts) + 1) Then Return "" _
            Else Return stringParts(whichField - 1)
    End Function

    Private Function ValidateFormData() As Boolean
        ' ----- Check the supplied data, and return True if it is all
valid.
        On Error Resume Next

        ' ----- Server name is required.
        If (Trim(server.Text) = "") Then
            MsgBox("The Server or Host name is required.", _
                MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
            server.Focus()
            Return False
        End If
        ' ----- Database name is required.
        If (Trim(database_name.Text) = "") Then
            MsgBox("The Database Name is required.", _
                MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
            database_name.Focus()
            Return False
        End If
        ' ----- For SQL Server authentication, the User ID is required.
        If authentication.SelectedIndex = 1 Then
            If (Trim(user_id.Text) = "") Then
                MsgBox("The User Name is required for SQL Server
authentication.", _
                    MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
                user_id.Focus()
                Return False
            End If
        End If
        ' ----- Success.
        Return True
    End Function
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
        Me.Close()
    End Sub


    Private Sub unit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles unit.Click
        server.Text = "SQLDEV2\SQL_DEV2"
        database_name.Text = "fgb01q_unit"
        authentication.SelectedIndex = 0
        user_id.Text = ""
        password.Text = ""

    End Sub

    Private Sub demo_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles demo.Click
        server.Text = "BUF53460"
        database_name.Text = "fgb01q_demo"
        authentication.SelectedIndex = 1
        user_id.Text = "sa"
        password.Text = "blues"
    End Sub

    Private Sub demo1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles demo1.Click
        server.Text = "BUF53460"
        database_name.Text = "fgb01q_demo1"
        authentication.SelectedIndex = 1
        user_id.Text = "sa"
        password.Text = "blues"
    End Sub
End Class
Main Form (Form1)

Private Sub ChangeDatabase_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles ChangeDatabase.Click
        LocateDatabase.Show()

    End Sub
    Public Function ConnectDatabase() As Boolean
        ' ----- Connect to the database. Return True on success.
        Dim connectionString As String
        Dim configChanged As Boolean
        Dim ProgramTitle As String = "BDM Security "
        ' ----- Initialize.
        HoldTransaction = Nothing
        configChanged = False
        ' ----- Obtain the connection string.
        If (Trim(My.Settings.con & "") = "") Then
            ' ----- Inform the user about the need to configure the
database.
            If (MsgBox("The application will exit for the new
connection. Please restart the application. Would you like to
proceed?", _
                MsgBoxStyle.YesNo Or MsgBoxStyle.Question,
ProgramTitle) _
                <> MsgBoxResult.Yes) Then Return False
            ' ----- Prompt for the new connection details.
            connectionString = LocateDatabase.PromptUser()
            If (connectionString = "") Then Return False
            configChanged = True
        Else
            connectionString = My.Settings.con

        End If

TryConnectingAgain:
        ' ----- Attempt to open the database.
        Try
            DB = New SqlClient.SqlConnection(connectionString)
            DB.Open()
        Catch ex As Exception
            ' ----- Some database failure.
            MsgBox("Database Connection Error ")

            ' ----- Perhaps it is just a configuration issue.
            If (MsgBox("The connection to the database may have failed
due to " & _
                "invalid configuration settings. Would you like to
change the " & _
                "database configuration at this time?", _
                MsgBoxStyle.YesNo Or MsgBoxStyle.Question,
ProgramTitle) _
                <> MsgBoxResult.Yes) Then Return False



            ' ----- Prompt for new details.
            connectionString = LocateDatabase.PromptUser()
            If (connectionString = "") Then Return False
            configChanged = True
            GoTo TryConnectingAgain
        End Try

        ' ----- Save the udpated configuration if needed.
        If (configChanged = True) Then _
            My.Settings.con = connectionString
        ' ----- Success.
        Return True


    End Function

End Class

@h40g2000cwb.googlegroups.com:

Load the connection string into a static (shared) variable. Initialize the
variable with the connection string in the app.config.

If the connection string changes during runtime, you'll need to
reinitialize all your database stuff/

When to use AndAlso vs And ?
Binary Search Tree - CompareTo Error
Waiting for a process to halt before continuing
Trying to get started with tables...
How do I handle a NULL XmlElement?
Get spawned process
how to check if the dataset is EOF or if a record exists frist .
GotFocus versus PreviewKeyDown or Other
Finding interrelation between primary keys in tables
How do I close a project without saving