Results 1 to 13 of 13

Thread: Vb.Net Change the connectionstring at runtime using an openfiledialog

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Vb.Net Change the connectionstring at runtime using an openfiledialog

    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
    Code:
    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.
    Code:
    '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.

    Code:
    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!
    Last edited by dday9; Jun 21st, 2012 at 02:32 PM.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2
    Lively Member kyrsoronas's Avatar
    Join Date
    Jun 2012
    Location
    Mordor
    Posts
    89

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    thats a very helpful post indeed !!! but can an admin user through the UI add another connection string to the app.config file and select which one he/she wants to use on the next login to the application ?

  3. #3

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    I'm not to sure. The whole reason I use the openfiledialog to choose the database is to avoid messing with the app.config all together.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4
    Lively Member kyrsoronas's Avatar
    Join Date
    Jun 2012
    Location
    Mordor
    Posts
    89

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    yes, but is there a way to tamper, from code, the app.config ?

  5. #5

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    It's hardly ever a good idea to write to your app.config file. But all it is, is an xml file. I would do a google search on "read and write xml vb.net" and there are plenty of examples. If you stumble into any problems while attempting to read or write from/to your app.config file, then I would suggest starting a new thread in the VB.net section of this forum.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  6. #6
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    Quote Originally Posted by kyrsoronas View Post
    yes, but is there a way to tamper, from code, the app.config ?
    Although in C#, this article explains how it can be done:

    http://www.codeproject.com/Tips/4110...the-App-Config

    Gary

  7. #7
    Lively Member kyrsoronas's Avatar
    Join Date
    Jun 2012
    Location
    Mordor
    Posts
    89

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    Thank you for your reply

  8. #8
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    Quote Originally Posted by kyrsoronas View Post
    Thank you for your reply
    Did you get what you needed from here, or are you still facing problems?

    Gary

  9. #9
    New Member
    Join Date
    Jun 2016
    Posts
    1

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    hello iam new to this forum and vb aswell . I want to have a user decide where the MDB file is at run time, I have done all the above but where and how do I use this

    Code:

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

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

    con.Close()


    Mu connections were done using the wizard so I have the APP.CONFIG FILE AS XML...pliz help dear.

  10. #10

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    You will want to change your App.Config fille at runtime. A quick Google search yielded this result: http://stackoverflow.com/questions/1...ing-at-runtime
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  11. #11
    Registered User
    Join Date
    Nov 2017
    Posts
    1

    Question Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    hi what if i have a textbox for sever,user id, password and data base name in a single form and may sql connection is on app.config using the code below is it possible to change it during runtime? that it will base on the info in the textbox?

    Code:
    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <startup>
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
        </startup>
    <connectionStrings>
      <add name="mater" connectionString="server = localhost; User ID = root; password = ; database = vblord   ; provider name = System.Data.SqlClient"/>
    </connectionStrings>
    </configuration>

  12. #12
    New Member
    Join Date
    Jun 2018
    Posts
    1

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    Hi dday9,

    I realise you posted this sometime back, but i'm just utilising the code now. Thanks it all works a treat. I'm able to change databases etc.
    Issue i'm having is I cannot get the DGV on the form to refresh with the new DB data. If I open another form which has the same datatable, the data is there.

    Any ideas?

    Thanks

  13. #13

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Vb.Net Change the connectionstring at runtime using an openfiledialog

    Are you re-declaring the DbConnection with the new connection string?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width