Skip to main content

How to Change Connection String at Run Time


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.

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

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

Public Sub ReadfromXML() Dim I As Integer For I = 0 To 2 Dim doc As XmlDocument = New XmlDocument() doc.Load("data.xml") Dim root As XmlElement = doc.DocumentElement ServerName = root.Attributes.Item(0).Value DatabaseName = root.Attributes.Item(1).Value cnString = "Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";Persist Security Info=True;User ID=sa;Password=yourpasswordhere" Dim CN As SqlConnection CN = New SqlConnection Try With CN If .State = ConnectionState.Open Then .Close() .ConnectionString = cnString .Open() Exit For End With Catch ex As Exception If Err.Number = 5 Then MsgBox("Cannot connect to server. Make sure that the server is running. " & vbCrLf & vbCrLf & "Otherwise please check for the configuration.", MsgBoxStyle.Exclamation) Dim DBPath As New frmDBPath DBPath.ShowDialog() End If Finally CN.Close() End Try Next I End Sub

Here’s the value of data.xml file.

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

Public Class MySettingsChanger Public Shared Sub SetConnectionString(ByVal cnnString As String) My.Settings.RunTimeConnectionString = cnnString End Sub 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:

Namespace My 'This class allows you to handle specific events on the settings class: ' The SettingChanging event is raised before a setting's value is changed. ' The PropertyChanged event is raised after a setting's value is changed. ' The SettingsLoaded event is raised after the setting values are loaded. ' The SettingsSaving event is raised before the setting values are saved. Partial Friend NotInheritable Class MySettings Public WriteOnly Property RunTimeConnectionString() Set(ByVal value) My.Settings("ADC_DataConnectionString") = value End Set End Property End Class End Namespace

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

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Submitted byAnonymous (not verified)on Tue, 02/15/2011 - 07:53

Root element is missing. what can I do?

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.