public sub or function ?

kscase · May 18th, 2007, 11:03 PM

I have been trying to move the creation of my database connection string to a public subroutine or function in Public Class Main, instead of putting it in each form.
I put a call to the sub/function inside the Main_Load routine.
The problem is the routine isn't passing back the value like I thought it should to all the routines in the application.

Here's the creation code:

Code:

Public Class Main
    ' The application is using Windows authentication.
    ' The name format is DOMAIN\USERNAME.
    Public APC_WirelessConnectionString As String
    '"Data Source=KAREN\APCD1;Initial Catalog=APC_Wireless;Integrated Security=True"        ' Laptop
    '"Data Source=APC-WS01\APCP1;Initial Catalog=APC_Wireless;Integrated Security=True"     ' Development
    '"Data Source=APC-SRV01\APCP1;Initial Catalog=APC_Wireless;Integrated Security=True"    ' Production

    Public Function DetermineServer(ByVal strConn As SqlConnection)
        Dim strParts() As String = Split(My.User.Name, "\")
        Dim strDomain As String = strParts(0)
        Dim strUserName As String = strParts(1)
        Dim strComputerName As String = My.Computer.Name
        Dim strLaptopSrvr As String = "KAREN\APCD1"
        Dim strDvlpSrvr As String = "APC-WS01\APCP1"
        Dim strProdSrvr As String = "APC-SRV01\APCP1"
        Dim strSrvr As String = String.Empty

        Select Case strComputerName
            Case "APC-WS01"
                strSrvr = strDvlpSrvr
            Case "KAREN"
                strSrvr = strLaptopSrvr
            Case Else
                strSrvr = strProdSrvr
        End Select
        APC_WirelessConnectionString = "Data Source=" & strSrvr & _
            ";Initial Catalog=APC_Wireless;Integrated Security=True"
        Return APC_WirelessConnectionString
    End Function
    Private Sub Main_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As New SqlConnection
        Call DetermineServer(strConn)
        'APC_WirelessConnectionString = strConn
        'MsgBox(APC_WirelessConnectionString)
    End Sub

When I try to use the connection string in other subroutines in the application and open the connection, I get an exception error about the string being uninitialized.

Code:

        ' connect to SQL Server
        Dim strConn As New SqlConnection(APC_WirelessConnectionString)
        strConn.Open()

Does anyone have a clue how to help me straighten out the mess I'm trying hard NOT to create? I really am trying to streamline my code, but I'm not being real successful so far.
I would really appreciate your help.

Karen

Imar · May 19th, 2007, 01:46 PM

Hi Karen,

Is it the purpose of DetermineServer to simply define the connection string, and not the connection? If so, simply have the method return the connection string, without passing a connection object to it. Then use the returned connection string to initialize the connection. Something like this should give you an idea:

Public Function DetermineServer(ByVal strConn As SqlConnection) As String
  ' Do whatever you need to do here, building up APC_WirelessConnectionString
  ' as you currently do

  ' then return the connection stirng
  Return APC_WirelessConnectionString
End Function

Since this code returns a connection string, you can initialize a connection object like this:

Dim strConn As New SqlConnection (DetermineServer())

With this code, DetermineServer is called first. It returns the proper connection string which is then passed to the constructor of the SqlConnection object.

Hope this helps,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

kscase · May 20th, 2007, 09:33 AM

Imar,
Thanks for the help.

Using the initialization method for the connection string,
Dim strConn As New SqlConnection (DetermineServer())

I get the message that DetermineServer is not declared, which I really don't understand.
DetermineServer is a publicly declared function, defined in the main.vb file.

What else do I need to do to make this work? I am really confused.

Karen

Imar · May 20th, 2007, 03:14 PM

If it's declared elsewhere (e.g. outside the class you're using it in), you may need to define it as a Shared function:

Public Shared Function DetermineServer.....

End Function

Then you should be able to access it as Main.DetermineServer()

Imar