How to Change Connection String at Run Time

Submitted by: 
Visitors have accessed this post 17364 times.

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.

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.


Comments

Root element is missing. what can I do?

I need vb 2008 code for searching records through Datagrid using access 2007

Thanks & Warm Regards, Wasi Alam
([email protected])

I need vb 2008 code for login, create new user and change password using access 2007.
Thanks
[email protected]

Thanks & Warm Regards, Wasi Alam
([email protected])

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.