Results 1 to 15 of 15

Thread: Select connection string at runtime

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Select connection string at runtime

    Hi,

    I am not sure if this is possible. But I am giving it a try. Also I hope to give all relevant information - If I miss something then I shall gladly give anything that is required.

    I have 3 databases that in design is exactly the same. What is difference is that they have separate names, reside on separate servers, and the data inside the tables is also different.

    This my connection string in the app.config:

    Code:
    <connectionStrings>
            <add name="MichelleApp.My.MySettings.RiskAConnectionString"
                connectionString="Server=Risk1Alias;Database=RiskA;User Id=RiskAUser;Password=RiskA#1985;"
                providerName="System.Data.SqlClient" />
        </connectionStrings>
    and...


    Code:
    <connectionStrings>
            <add name="MichelleApp.My.MySettings.RiskBConnectionString"
                connectionString="Server=Risk2Alias;Database=RiskB;User Id=RiskBUser;Password=RiskB#1985;"
                providerName="System.Data.SqlClient" />
        </connectionStrings>
    and...


    Code:
    <connectionStrings>
            <add name="MichelleApp.My.MySettings.RiskCConnectionString"
                connectionString="Server=Risk3Alias;Database=RiskC;User Id=RiskCUser;Password=RiskC#1985;"
                providerName="System.Data.SqlClient" />
        </connectionStrings>

    Currently I use 3 separate builds of my app, each with own connection string. This is much effort.

    So I was thinking. What if I have a form on startup where I enter the server name and database name or whatever is needed into textboxes and then press a button and then my app connect to that? It will somehow need to write that to the app.config...? or under settings write that to the app settings? I don't know. "application-scope settings are read-only and cannot be changed programmatically"

    Or is there another (easy) way to achieve this?

    Thanks
    Last edited by schoemr; Feb 21st, 2019 at 08:01 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Select connection string at runtime

    Why do you need three builds of your app? The whole point of the config file is that it is hand-editable. You can deploy your app and change the config file at any time and the new values will be used by the app the next time it runs. You can either have one config file and edit it or three config files and simply copy in the one you want at the time.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Select connection string at runtime

    Hi John,

    You can deploy your app and change the config file at any time and the new values will be used by the app the next time it runs.
    Thanks for that!!

    So say I want to connect to the first database, I can change the app.config before starting up the app... This will have to be manual?

    Can I somehow.... and please bear with me.... let the user select that setting connectionString="Server=Risk2Alias;Database=RiskB;User Id=RiskBUser;Password=RiskB#1985;" from a dropdown and then start up the app? Like one app opens another app?

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Select connection string at runtime

    I just realize now that my app.config.deploy file is on a common folder on a intranet... If I change that, it will change for all.. It will not be possible for one person to work on databaseA and another on databaseB

    Also it is not possible to change a connection string to a user scope...

  5. #5
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Select connection string at runtime

    I am not sure I follow 100% Why even have connections stored in the config file?

    Would something like this work for you? (this assumes all connections are SqlClient)
    Code:
    Public Class DBConn
        Private Sub DBConn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ConnDT As New DataTable
    
            With ConnDT
                .Columns.Add("DispMem", GetType(String))
                .Columns.Add("ValMem", GetType(String))
    
                'add your connection strings
                .Rows.Add("Connection 1", "Data Source=A;Initial Catalog=X;Integrated Security=True")
                .Rows.Add("Connection 2", "Data Source=B;Initial Catalog=Y;Integrated Security=True")
    
                'I suppose you could add things from settings too
                .Rows.Add("Connection 3", My.Settings.SQLConn1)
            End With
    
            CmbBoxConn.DataSource = ConnDT
            CmbBoxConn.ValueMember = "ValMem"
            CmbBoxConn.DisplayMember = "DispMem"
    
            AddHandler CmbBoxConn.SelectedIndexChanged, Sub()
    
                                                            Using SQLConn As New SqlClient.SqlConnection(CmbBoxConn.SelectedValue.ToString)
    
                                                            End Using
    
                                                        End Sub
        End Sub
    End Class
    You could get clever and modify it to use different connection types such as oledb dependent on what connection was selected

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Select connection string at runtime

    For allowing a user to select a named connection in a ComboBox or Listbox, that is possible if interested.

    Name:  F1.jpg
Views: 121
Size:  26.1 KB

  7. #7
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Select connection string at runtime

    For allowing a user to select a named connection in a ComboBox or Listbox, that is possible if interested.

    Name:  F1.jpg
Views: 121
Size:  26.1 KB

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Select connection string at runtime

    If your dataadapter/tableadapters are using a connectionstring that is stored in Settings and has an Application Scope, you can still change it's value at runtime but you can not save the change back to the settings file. So you will loose the new value when the app ends.

    Here is an example, http://www.vbforums.com/showthread.p...lication+scope

    Look at my post 4 and 5.

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Select connection string at runtime

    Be aware that, if you're using a typed DataSet, every table adapter is going to get its connection string from the config file so you would have to change the connection string for every table adapter after creating it.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Select connection string at runtime

    Quote Originally Posted by jmcilhinney View Post
    Be aware that, if you're using a typed DataSet, every table adapter is going to get its connection string from the config file so you would have to change the connection string for every table adapter after creating it.
    Either I don't understand what your saying or that statement is incorrect.

    Once you reset the value of the connection string that the TableAdapters are using stored in setting then all subsequently created TableAdapters will use the new value. So you would only need to change the value in the StartUp form before creating any TableAdapters. Maybe this will show what I mean.

    StartUp form
    Code:
    Public Class Form12
    
        Private Sub Form12_Load(sender As Object, e As EventArgs) Handles Me.Load
            My.Settings.Item("waterconnectionstring") = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\AJunkProject\water.accdb"
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim frm As New Form11
            frm.Show()
        End Sub
    
    End Class
    And the TableAdapter in "frm" will uuse the new value.
    Code:
    Public Class Form11
        Private Sub Tagsdetail1BindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles Tagsdetail1BindingNavigatorSaveItem.Click
            Me.Validate()
            Me.Tagsdetail1BindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.WaterDataSet)
    
        End Sub
    
        Private Sub Form11_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            'TODO: This line of code loads data into the 'WaterDataSet.tagsdetail1' table. You can move, or remove it, as needed.
            Me.Tagsdetail1TableAdapter.Fill(Me.WaterDataSet.tagsdetail1)
    
        End Sub
    
    End Class

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Select connection string at runtime

    Quote Originally Posted by wes4dbt View Post
    Either I don't understand what your saying or that statement is incorrect.
    Now that I think about it, you're probably right that it's wrong. If I had ever tested that sort of thing, it would have been by hand-editing the config file after starting the app. In that case, the change would not affect new table adapters because the config file would be read once at startup and the value stored. Your suggestion is actually changing the value that was stored when the config file was read rather than just changing the config file.

  12. #12
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Select connection string at runtime

    Quote Originally Posted by jmcilhinney View Post
    Now that I think about it, you're probably right that it's wrong. If I had ever tested that sort of thing, it would have been by hand-editing the config file after starting the app. In that case, the change would not affect new table adapters because the config file would be read once at startup and the value stored. Your suggestion is actually changing the value that was stored when the config file was read rather than just changing the config file.
    Please don't make a habit of saying I was right and you were wrong. I would become insufferable ( or more insufferable ).

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Select connection string at runtime

    Quote Originally Posted by wes4dbt View Post
    more insufferable
    I wouldn't have thought that that was possible.

  14. #14
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Select connection string at runtime

    Quote Originally Posted by jmcilhinney View Post
    I wouldn't have thought that that was possible.
    Touche

  15. #15

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Select connection string at runtime

    Hi All, thank you very much for this

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