One of the challenging parts in VB.NET is to change the connection string of your database location when you run your application.

This tutorial will help you solve this problem. This will apply only to SQL Server but it can be also modified easily to work with MS Access and other DBMS.

In my case I initialize this on the Form Load event on my Login form.

1. Private
   
   Sub
   
   frmLogin_Load(
   
   ByVal
   
   sender As
   
   System.
   
   Object
   
   , ByVal
   
   e As
   
   System.
   
   EventArgs
   
   )
   
   Handles
   
   MyBase
   
   .
   
   Load
   
2. ReadfromXML(
   
   )
   
3. MySettingsChanger.
   
   SetConnectionString
   
   (
   
   cnString)
   
4. 
   
5. End
   
   Sub
   

ReadfromXML is a procedure that reads the value from data.xml and assigns the value to cnString variable.

1. Public
   
   Sub
   
   ReadfromXML(
   
   )
   
2. Dim
   
   I As
   
   Integer
   
3. 
   
4. For
   
   I =
   
   0
   
   To
   
   2
   
5. Dim
   
   doc As
   
   XmlDocument =
   
   New
   
   XmlDocument(
   
   )
   
6. doc.
   
   Load
   
   (
   
   "data.xml"
   
   )
   
7. 
   
8. Dim
   
   root As
   
   XmlElement =
   
   doc.
   
   DocumentElement
   
9. 
   
10. ServerName =
    
    root.
    
    Attributes
    
    .
    
    Item
    
    (
    
    0
    
    )
    
    .
    
    Value
    
11. DatabaseName =
    
    root.
    
    Attributes
    
    .
    
    Item
    
    (
    
    1
    
    )
    
    .
    
    Value
    
12. 
    
13. cnString =
    
    "Data Source="
    
    &
    
    ServerName &
    
    ";Initial Catalog="
    
    &
    
    DatabaseName &
    
    ";Persist Security Info=True;User ID=sa;Password=yourpasswordhere"
    
14. 
    
15. Dim
    
    CN As
    
    SqlConnection
    
16. CN =
    
    New
    
    SqlConnection
    
17. 
    
18. Try
    
19. With
    
    CN
    
20. If
    
    .
    
    State
    
    =
    
    ConnectionState.
    
    Open
    
    Then
    
    .
    
    Close
    
    (
    
    )
    
21. 
    
22. .
    
    ConnectionString
    
    =
    
    cnString
    
23. .
    
    Open
    
    (
    
    )
    
24. 
    
25. Exit
    
    For
    
26. End
    
    With
    
27. Catch
    
    ex As
    
    Exception
    
28. If
    
    Err.
    
    Number
    
    =
    
    5
    
    Then
    
29. MsgBox
    
    (
    
    "Cannot connect to server. Make sure that the server is running. "
    
    &
    
    vbCrLf &
    
    vbCrLf &
    
    "Otherwise please check for the configuration."
    
    , MsgBoxStyle.
    
    Exclamation
    
    )
    
30. 
    
31. Dim
    
    DBPath As
    
    New
    
    frmDBPath
    
32. 
    
33. DBPath.
    
    ShowDialog
    
    (
    
    )
    
34. End
    
    If
    
35. Finally
    
36. CN.
    
    Close
    
    (
    
    )
    
37. End
    
    Try
    
38. Next
    
    I
    
39. End
    
    Sub
    

Here’s the value of data.xml file.

1. <?
   
   xml version=
   
   "1.0"
   
   ?>
   
2. <
   
   database server=
   
   "computer_name"
   
   name=
   
   "database_name"
   
   >
   
3. </
   
   database>
   

If the connection is successful it will call the MySettingsChanger Class and assign the value to SetConnectionString procedure.

1. Public
   
   Class
   
   MySettingsChanger
   
2. Public
   
   Shared
   
   Sub
   
   SetConnectionString(
   
   ByVal
   
   cnnString As
   
   String
   
   )
   
3. My.
   
   Settings
   
   .
   
   RunTimeConnectionString
   
   =
   
   cnnString
   
4. End
   
   Sub
   
5. End
   
   Class
   

You can create a class MySettingsChanger under the Project menu then “Add Class” and paste the above code in the code window.

cnnString will then pass its value to RunTimeConnectionString which can be found under the Project settings.


Here’s the complete code after you click the View Code:

1. Namespace
   
   My
   
2. 
   
3. 'This class allows you to handle specific events on the settings class:
   
4. ' The SettingChanging event is raised before a setting's value is changed.
   
5. ' The PropertyChanged event is raised after a setting's value is changed.
   
6. ' The SettingsLoaded event is raised after the setting values are loaded.
   
7. ' The SettingsSaving event is raised before the setting values are saved.
   
8. Partial
   
   Friend
   
   NotInheritable
   
   Class
   
   MySettings
   
9. Public
   
   WriteOnly
   
   Property
   
   RunTimeConnectionString(
   
   )
   
10. 
    
11. Set
    
    (
    
    ByVal
    
    value)
    
12. My.
    
    Settings
    
    (
    
    "ADC_DataConnectionString"
    
    )
    
    =
    
    value
    
13. End
    
    Set
    
14. End
    
    Property
    
15. End
    
    Class
    
16. End
    
    Namespace
    

Some of the code above is generated automatically. Only the RunTimeConnectionString Property has been set here.