S
Sankalp
Hi,
I am using VB 2005. My application has many data bound controls. The
connection is stored in the app.config file.
I want the application to start with a default connection string and
while during the runtime, the user can click on a button and change the
connection string without exiting the application.
I would really appreciate any sort of help.
I have already something working but this requires the application to
close and restart. ( If i dont close and restart the application dosent
change the connection string) . Here is the code
locatedatabase.vb
Imports System.Xml
Public Class LocateDatabase
Private Const AuthenticationTypeWindows As Integer = 1
Private Const AuthenticationTypeSQL As Integer = 2
Dim ProgramTitle As String = "BDM Security"
Public Function PromptUser() As String
' ----- Prompt the user for database connection details. Return
a
' valid ADO.NET connection string for SQL Server, or a
blank
' string if the user clicks Cancel.
Dim newConnection As String
' ----- Prompt the user.
Try
Me.ShowDialog()
Catch ex As Exception
End Try
' ----- Build the new connection string.
If (Me.DialogResult = Windows.Forms.DialogResult.OK) Then
newConnection = "Data Source=" & Trim(server.Text) & _
";Initial Catalog=" & Trim(database_name.Text)
If authentication.SelectedIndex = 0 Then
' ----- Use Windows security.
newConnection &= ";Integrated Security=true"
Else
' ----- Use SQL Server security.
newConnection &= ";User ID=" & Trim(user_id.Text) & _
";Password=" & Trim(password.Text)
End If
Return newConnection
Else
Return ""
End If
End Function
Private Sub LocateDatabase_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
' ----- Prepare the form.
Dim counter As Integer
Dim connectionString As String
Dim oneKey As String
Dim oneValue As String
On Error Resume Next
' ----- Load in the existing data.
connectionString = My.Settings.con & ""
For counter = 1 To CountSubStr(connectionString, ";") + 1
' ----- Each comma-delimited part has the format
"key=value".
oneKey = GetSubStr(connectionString, ";", counter)
oneValue = Trim(GetSubStr(oneKey, "=", 2))
oneKey = Replace(UCase(Trim(GetSubStr(oneKey, "=", 1))), "
", "")
' ----- Process each part.
Select Case oneKey
Case "DATASOURCE"
' ----- Show the server host.
server.Text = oneValue
Case "INITIALCATALOG"
' ----- Show the default database name.
database_name.Text = oneValue
Case "INTEGRATEDSECURITY"
' ----- Only check for "true". False is assumed.
If (UCase(oneValue) = "TRUE") Then _
authentication.SelectedIndex = 0
Case "USERID"
' ----- A user ID forces SQL authentication.
authentication.SelectedIndex = 1
user_id.Text = oneValue
Case "PASSWORD"
' ----- A password forces SQL authentication.
authentication.SelectedIndex = 1
password.Text = oneValue
End Select
Next counter
End Sub
Private Sub RecordDatabase_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles database_name.Enter
' ----- Highlight the entire text.
database_name.SelectAll()
End Sub
Private Sub RecordPassword_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles password.Enter
' ----- Highlight the entire text.
password.SelectAll()
End Sub
Private Sub RecordServer_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles server.Enter
' ----- Highlight the entire text.
server.SelectAll()
End Sub
Private Sub RecordUser_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles user_id.Enter
' ----- Highlight the entire text.
user_id.SelectAll()
End Sub
Private Sub RecordAuthentication_SelectedIndexChanged(ByVal sender
As Object, ByVal e As System.EventArgs) Handles
authentication.SelectedIndexChanged
' ----- Enable dependent fields as needed.
If authentication.SelectedIndex = 0 Then
' ----- Windows authentication requires no user/password.
Label4.Enabled = False
user_id.Enabled = False
Label5.Enabled = False
password.Enabled = False
Else
' ----- Requires specific SQL Server user and password.
Label4.Enabled = True
user_id.Enabled = True
Label5.Enabled = True
password.Enabled = True
End If
End Sub
Private Sub ActOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles ActOK.Click
'Clear the previous connection string
Dim con_Z As String
con_Z = My.Settings.con
My.Settings.con = ""
If (ValidateFormData() = False) Then Return
Me.DialogResult = Windows.Forms.DialogResult.OK
If Form1.ConnectDatabase() = False Then
My.Settings.con = con_Z
Me.Close()
Exit Sub
End If
Form1.Close()
Me.Close()
End Sub
Public Function CountSubStr(ByVal mainText As String, ByVal subText
As String) As Integer
' ----- Return a count of the number of times that a subText
occurs in
' a string (mainText).
Dim totalTimes As Integer
Dim startPos As Integer
Dim foundPos As Integer
totalTimes = 0
startPos = 1
' ----- Keep searching until we don't find it no more!
Do
' ----- Search for the subText.
foundPos = InStr(startPos, mainText, subText)
If (foundPos = 0) Then Exit Do
totalTimes = totalTimes + 1
' ----- Move to just after the occurrence.
startPos = foundPos + Len(subText)
Loop
' ----- Return the count.
Return totalTimes
End Function
Public Function GetSubStr(ByVal origString As String, ByVal delim
As String, _
ByVal whichField As Integer) As String
' ----- Extracts a delimited string from another larger string.
Dim stringParts() As String
' ----- Handle some errors.
If (whichField < 0) Then Return ""
If (Len(origString) < 1) Then Return ""
If (Len(delim) = 0) Then Return ""
' ----- Break the string up into delimited parts.
stringParts = Split(origString, delim)
' ----- See if the part we want exists and return it.
If (whichField > UBound(stringParts) + 1) Then Return "" _
Else Return stringParts(whichField - 1)
End Function
Private Function ValidateFormData() As Boolean
' ----- Check the supplied data, and return True if it is all
valid.
On Error Resume Next
' ----- Server name is required.
If (Trim(server.Text) = "") Then
MsgBox("The Server or Host name is required.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
server.Focus()
Return False
End If
' ----- Database name is required.
If (Trim(database_name.Text) = "") Then
MsgBox("The Database Name is required.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
database_name.Focus()
Return False
End If
' ----- For SQL Server authentication, the User ID is required.
If authentication.SelectedIndex = 1 Then
If (Trim(user_id.Text) = "") Then
MsgBox("The User Name is required for SQL Server
authentication.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
user_id.Focus()
Return False
End If
End If
' ----- Success.
Return True
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub unit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles unit.Click
server.Text = "SQLDEV2\SQL_DEV2"
database_name.Text = "fgb01q_unit"
authentication.SelectedIndex = 0
user_id.Text = ""
password.Text = ""
End Sub
Private Sub demo_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles demo.Click
server.Text = "BUF53460"
database_name.Text = "fgb01q_demo"
authentication.SelectedIndex = 1
user_id.Text = "sa"
password.Text = "blues"
End Sub
Private Sub demo1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles demo1.Click
server.Text = "BUF53460"
database_name.Text = "fgb01q_demo1"
authentication.SelectedIndex = 1
user_id.Text = "sa"
password.Text = "blues"
End Sub
End Class
Main Form (Form1)
Private Sub ChangeDatabase_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles ChangeDatabase.Click
LocateDatabase.Show()
End Sub
Public Function ConnectDatabase() As Boolean
' ----- Connect to the database. Return True on success.
Dim connectionString As String
Dim configChanged As Boolean
Dim ProgramTitle As String = "BDM Security "
' ----- Initialize.
HoldTransaction = Nothing
configChanged = False
' ----- Obtain the connection string.
If (Trim(My.Settings.con & "") = "") Then
' ----- Inform the user about the need to configure the
database.
If (MsgBox("The application will exit for the new
connection. Please restart the application. Would you like to
proceed?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question,
ProgramTitle) _
<> MsgBoxResult.Yes) Then Return False
' ----- Prompt for the new connection details.
connectionString = LocateDatabase.PromptUser()
If (connectionString = "") Then Return False
configChanged = True
Else
connectionString = My.Settings.con
End If
TryConnectingAgain:
' ----- Attempt to open the database.
Try
DB = New SqlClient.SqlConnection(connectionString)
DB.Open()
Catch ex As Exception
' ----- Some database failure.
MsgBox("Database Connection Error ")
' ----- Perhaps it is just a configuration issue.
If (MsgBox("The connection to the database may have failed
due to " & _
"invalid configuration settings. Would you like to
change the " & _
"database configuration at this time?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question,
ProgramTitle) _
<> MsgBoxResult.Yes) Then Return False
' ----- Prompt for new details.
connectionString = LocateDatabase.PromptUser()
If (connectionString = "") Then Return False
configChanged = True
GoTo TryConnectingAgain
End Try
' ----- Save the udpated configuration if needed.
If (configChanged = True) Then _
My.Settings.con = connectionString
' ----- Success.
Return True
End Function
End Class
I am using VB 2005. My application has many data bound controls. The
connection is stored in the app.config file.
I want the application to start with a default connection string and
while during the runtime, the user can click on a button and change the
connection string without exiting the application.
I would really appreciate any sort of help.
I have already something working but this requires the application to
close and restart. ( If i dont close and restart the application dosent
change the connection string) . Here is the code
locatedatabase.vb
Imports System.Xml
Public Class LocateDatabase
Private Const AuthenticationTypeWindows As Integer = 1
Private Const AuthenticationTypeSQL As Integer = 2
Dim ProgramTitle As String = "BDM Security"
Public Function PromptUser() As String
' ----- Prompt the user for database connection details. Return
a
' valid ADO.NET connection string for SQL Server, or a
blank
' string if the user clicks Cancel.
Dim newConnection As String
' ----- Prompt the user.
Try
Me.ShowDialog()
Catch ex As Exception
End Try
' ----- Build the new connection string.
If (Me.DialogResult = Windows.Forms.DialogResult.OK) Then
newConnection = "Data Source=" & Trim(server.Text) & _
";Initial Catalog=" & Trim(database_name.Text)
If authentication.SelectedIndex = 0 Then
' ----- Use Windows security.
newConnection &= ";Integrated Security=true"
Else
' ----- Use SQL Server security.
newConnection &= ";User ID=" & Trim(user_id.Text) & _
";Password=" & Trim(password.Text)
End If
Return newConnection
Else
Return ""
End If
End Function
Private Sub LocateDatabase_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
' ----- Prepare the form.
Dim counter As Integer
Dim connectionString As String
Dim oneKey As String
Dim oneValue As String
On Error Resume Next
' ----- Load in the existing data.
connectionString = My.Settings.con & ""
For counter = 1 To CountSubStr(connectionString, ";") + 1
' ----- Each comma-delimited part has the format
"key=value".
oneKey = GetSubStr(connectionString, ";", counter)
oneValue = Trim(GetSubStr(oneKey, "=", 2))
oneKey = Replace(UCase(Trim(GetSubStr(oneKey, "=", 1))), "
", "")
' ----- Process each part.
Select Case oneKey
Case "DATASOURCE"
' ----- Show the server host.
server.Text = oneValue
Case "INITIALCATALOG"
' ----- Show the default database name.
database_name.Text = oneValue
Case "INTEGRATEDSECURITY"
' ----- Only check for "true". False is assumed.
If (UCase(oneValue) = "TRUE") Then _
authentication.SelectedIndex = 0
Case "USERID"
' ----- A user ID forces SQL authentication.
authentication.SelectedIndex = 1
user_id.Text = oneValue
Case "PASSWORD"
' ----- A password forces SQL authentication.
authentication.SelectedIndex = 1
password.Text = oneValue
End Select
Next counter
End Sub
Private Sub RecordDatabase_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles database_name.Enter
' ----- Highlight the entire text.
database_name.SelectAll()
End Sub
Private Sub RecordPassword_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles password.Enter
' ----- Highlight the entire text.
password.SelectAll()
End Sub
Private Sub RecordServer_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles server.Enter
' ----- Highlight the entire text.
server.SelectAll()
End Sub
Private Sub RecordUser_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles user_id.Enter
' ----- Highlight the entire text.
user_id.SelectAll()
End Sub
Private Sub RecordAuthentication_SelectedIndexChanged(ByVal sender
As Object, ByVal e As System.EventArgs) Handles
authentication.SelectedIndexChanged
' ----- Enable dependent fields as needed.
If authentication.SelectedIndex = 0 Then
' ----- Windows authentication requires no user/password.
Label4.Enabled = False
user_id.Enabled = False
Label5.Enabled = False
password.Enabled = False
Else
' ----- Requires specific SQL Server user and password.
Label4.Enabled = True
user_id.Enabled = True
Label5.Enabled = True
password.Enabled = True
End If
End Sub
Private Sub ActOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles ActOK.Click
'Clear the previous connection string
Dim con_Z As String
con_Z = My.Settings.con
My.Settings.con = ""
If (ValidateFormData() = False) Then Return
Me.DialogResult = Windows.Forms.DialogResult.OK
If Form1.ConnectDatabase() = False Then
My.Settings.con = con_Z
Me.Close()
Exit Sub
End If
Form1.Close()
Me.Close()
End Sub
Public Function CountSubStr(ByVal mainText As String, ByVal subText
As String) As Integer
' ----- Return a count of the number of times that a subText
occurs in
' a string (mainText).
Dim totalTimes As Integer
Dim startPos As Integer
Dim foundPos As Integer
totalTimes = 0
startPos = 1
' ----- Keep searching until we don't find it no more!
Do
' ----- Search for the subText.
foundPos = InStr(startPos, mainText, subText)
If (foundPos = 0) Then Exit Do
totalTimes = totalTimes + 1
' ----- Move to just after the occurrence.
startPos = foundPos + Len(subText)
Loop
' ----- Return the count.
Return totalTimes
End Function
Public Function GetSubStr(ByVal origString As String, ByVal delim
As String, _
ByVal whichField As Integer) As String
' ----- Extracts a delimited string from another larger string.
Dim stringParts() As String
' ----- Handle some errors.
If (whichField < 0) Then Return ""
If (Len(origString) < 1) Then Return ""
If (Len(delim) = 0) Then Return ""
' ----- Break the string up into delimited parts.
stringParts = Split(origString, delim)
' ----- See if the part we want exists and return it.
If (whichField > UBound(stringParts) + 1) Then Return "" _
Else Return stringParts(whichField - 1)
End Function
Private Function ValidateFormData() As Boolean
' ----- Check the supplied data, and return True if it is all
valid.
On Error Resume Next
' ----- Server name is required.
If (Trim(server.Text) = "") Then
MsgBox("The Server or Host name is required.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
server.Focus()
Return False
End If
' ----- Database name is required.
If (Trim(database_name.Text) = "") Then
MsgBox("The Database Name is required.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
database_name.Focus()
Return False
End If
' ----- For SQL Server authentication, the User ID is required.
If authentication.SelectedIndex = 1 Then
If (Trim(user_id.Text) = "") Then
MsgBox("The User Name is required for SQL Server
authentication.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
user_id.Focus()
Return False
End If
End If
' ----- Success.
Return True
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub unit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles unit.Click
server.Text = "SQLDEV2\SQL_DEV2"
database_name.Text = "fgb01q_unit"
authentication.SelectedIndex = 0
user_id.Text = ""
password.Text = ""
End Sub
Private Sub demo_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles demo.Click
server.Text = "BUF53460"
database_name.Text = "fgb01q_demo"
authentication.SelectedIndex = 1
user_id.Text = "sa"
password.Text = "blues"
End Sub
Private Sub demo1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles demo1.Click
server.Text = "BUF53460"
database_name.Text = "fgb01q_demo1"
authentication.SelectedIndex = 1
user_id.Text = "sa"
password.Text = "blues"
End Sub
End Class
Main Form (Form1)
Private Sub ChangeDatabase_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles ChangeDatabase.Click
LocateDatabase.Show()
End Sub
Public Function ConnectDatabase() As Boolean
' ----- Connect to the database. Return True on success.
Dim connectionString As String
Dim configChanged As Boolean
Dim ProgramTitle As String = "BDM Security "
' ----- Initialize.
HoldTransaction = Nothing
configChanged = False
' ----- Obtain the connection string.
If (Trim(My.Settings.con & "") = "") Then
' ----- Inform the user about the need to configure the
database.
If (MsgBox("The application will exit for the new
connection. Please restart the application. Would you like to
proceed?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question,
ProgramTitle) _
<> MsgBoxResult.Yes) Then Return False
' ----- Prompt for the new connection details.
connectionString = LocateDatabase.PromptUser()
If (connectionString = "") Then Return False
configChanged = True
Else
connectionString = My.Settings.con
End If
TryConnectingAgain:
' ----- Attempt to open the database.
Try
DB = New SqlClient.SqlConnection(connectionString)
DB.Open()
Catch ex As Exception
' ----- Some database failure.
MsgBox("Database Connection Error ")
' ----- Perhaps it is just a configuration issue.
If (MsgBox("The connection to the database may have failed
due to " & _
"invalid configuration settings. Would you like to
change the " & _
"database configuration at this time?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question,
ProgramTitle) _
<> MsgBoxResult.Yes) Then Return False
' ----- Prompt for new details.
connectionString = LocateDatabase.PromptUser()
If (connectionString = "") Then Return False
configChanged = True
GoTo TryConnectingAgain
End Try
' ----- Save the udpated configuration if needed.
If (configChanged = True) Then _
My.Settings.con = connectionString
' ----- Success.
Return True
End Function
End Class