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 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()
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
If .State = ConnectionState.Open Then .Close()
.ConnectionString = cnString
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
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
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:
'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()
My.Settings("ADC_DataConnectionString") = value
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.