Vb.Net Change the connectionstring at runtime using an openfiledialog

[dday9]

I've once read that computers are the dumbest things ever, they do however, take great directions.

In this code, I will explain how to set up a connectionstring based on where the user decides the database is. A little note, I use Microsoft Access 2003, to find the correct connection string, go to here, and find the one you use.

Prerequisits:1. Two(2) settings set up like:
A.Name - Database & DatabaseCon
B.Type - String(for both)
C.Scope - User(for both)
D.Value - Leave blank(for both)

2. Have one(1) openfile dialog

Getting started:
Add

Dim strDatabase As String = My.Settings.Database
    Dim strDatabaseCon As String = My.Settings.DatabaseCon

under your public class. What we're doing is declaring these two(2) variables as strings that will be equal to what the value of database and databasecon are. At first they will be nothing, don't fret, we will set them later down the road. Also, you will want to Imports System.IO to check for a file.

Form Load
When we have the form load we want to check if strDatabase's file exist, right now it is blank so the first time we debug the program the file obviously will not exist. When file.exists = false we will use our open file dialog to allow the user to choose the datastring. In this code I have ' so that away you will know what's going on.

'Finds the db
        'Here we declare the provider and the securit
        'Check the link in my notes above Prerequisits:
        Dim provider As String = "Microsoft.Jet.OLEDB.4.0"
        Dim security As String = "False"

        'Here if the the filename we have stored in
        'My.Settings.Database doesn't exist the we use
        'The openfile dialog
        If File.Exists(strDatabase) = False Then
            MessageBox.Show("Do you want to change the location of the customers database?", "System Message", MessageBoxButtons.OK)
            'I would suggest that you use a filter
            'That away the user will only be allowed
            'To choose what ever you say he/she can.
            OpenFileDialog1.Filter = "Access Documents|*.mdb"
            OpenFileDialog1.FileName = ""
            OpenFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyComputer)

            'When the user chooses the database in
            'The open filedialog then this is where
            'The real magic happens
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

                'Here we set the value of My.Settings.Database
                'Which will be the filename in OpenFile Dialog
                My.Settings.Database = OpenFileDialog1.FileName

                'Here we redeclare the strDatabase = My.Settings.Database
                strDatabase = My.Settings.Database

                'Here is the connection string that we use
                'When we try to open a connection
                My.Settings.DatabaseCon = "Provider=" & provider & ";Data Source =" & strDatabase & ";Persist Security Info=" & security

                'Here we redeclare the strDatabaseCon = My.Settings.DatabaseCon
                strDatabaseCon = My.Settings.DatabaseCon

                'When you make changes to the settings,
                'Save, save, save, save!
                My.Settings.Save()
            Else
                MessageBox.Show("No Database Path selected.", "Fatal Error", MessageBoxButtons.OK)
            End If
        End If

Setting Up The Connection
Now that the user chose what database he/she wanted you can use My.Settings.DatabaseCon as your connection string.

Dim con As New OleDbConnection(strDatabaseCon)
        con.Open()

        'Now you can do your SQL commands and what not.

        con.Close()

Summary
In conclusion, we let the user choose a database by using an openfile dialog and we made the connection string based off of the file they chose. We then saved the settings, so that next time the user uses the program they don't have to repeate the process.

Dday!