Visual Studio / VB connection problem 1

[Catadmin]

I have a form with 4 radio buttons in an groupBox that I'm using with the SQL Server SMO object to backup databases. Each radio button represents an environment or SQL Server instance that I am connecting to. I only want one connection at a time, hence the use of RadioButton instead of CheckBox.

I have an initial connections module coded as below.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Data.SqlClient

Module CreateInitialConnection
    Public ProdServer As New Server()
    Public ProdConn As ServerConnection
    Public MyServerEnv As String
    Public MyEnv As String

    Public Sub CreateMyConnection(ByVal Environment As String)
        If MyServerEnv = Nothing Then
            MyServerEnv = Environment
        Else
            MsgBox("Sorry.  The program must be closed and restarted to choose a new environment.")
            End
        End If

        ProdConn = ProdServer.ConnectionContext

        Select Case MyServerEnv
            Case "Dev"
                ProdConn.ServerInstance = "MyDevServer, 1433"
            Case "Test"
                ProdConn.ServerInstance = "MyTestServer, 1433"
            Case "QC"
                ProdConn.ServerInstance = "MyQCServer, 1433"
            Case "Production"
                ProdConn.ServerInstance = "MyProdServer, 1433"
            Case Else
                MsgBox("You must choose an environment first.")
        End Select
        ' MsgBox("MyServerEnv is " & MyServerEnv)
        'MsgBox("Connection String is " & ProdConn.ServerInstance.ToString)

    End Sub
End Module

Since I haven't figured out how to switch the connection between instances while the program is running, I want to force the user to close the program and re-open it if they change instances. Unfortunately, the initial start up form always opens the connection to Dev (since it's the first of the RadioButtons) and closes the program when I try to choose another. I've even tried forcing all the radio buttons to False, but it doesn't work.

I know the problem is when the startup form gets initialized, it runs through all the code and sees Dev is chosen first and forces the initial connection to be created before the form comes up on the screen. The problem is, how do I force the to wait until the user clicks on the "ChooseDatabaseButton"? Or, how do I close and re-initialize the connection every time that button is chosen?

The code that sets the instance information is below:

Public Class ChooseEnvironment

    Private Sub ChooseEnvironment_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.StartPosition = FormStartPosition.CenterScreen
        Me.Development.Checked = False
        Me.Test.Checked = False
        Me.QC.Checked = False
        Me.Production.Checked = False
    End Sub

    Private Sub Development_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Development.CheckedChanged
        If Me.Development.Checked = True Then
            MyEnv = "Dev"
            CreateMyConnection(MyEnv)
        End If
    End Sub
...
           MyEnv = "Test"
            CreateMyConnection(MyEnv)
 ...
           MyEnv = "QC"
            CreateMyConnection(MyEnv)
...
           MyEnv = "Production"
            CreateMyConnection(MyEnv)
...

Private Sub ChooseDBButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChooseDBButton.Click
        My.Forms.BackupDatabase.ShowDialog()
    End Sub
End Class

I've dotted out all the extra "Private Sub" stuff which is the same across all buttons.

Thank you in advance for any advice you can give me.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Borvik]

Try taking the CreateMyConnection(MyEnv) out of the CheckChanged events and put in the the ChooseDBButton_Click subroutine instead.

 

[Catadmin]

Thanks, Borvik. I appreciate the help. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Borvik]

Not a problem - glad to be of service.