Results 1 to 7 of 7

Thread: [RESOLVED] If statement for dual login on sql server

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2016
    Posts
    113

    Resolved [RESOLVED] If statement for dual login on sql server

    I have a laptop that I travel with and just got it connected to my home laptop so I can access my sql server database. I have a class set up with my sql login
    and other sql commands.
    If I am running my program on my home laptop, I want the login to be: Dim DBCon As New SqlConnection("Server=" & My.Computer.Name & "\SQLEXPRESS;Database = master;Integrated Security=SSPI;")
    If I am running on a remote laptop, then I want the login to be: Dim DBCon As New SqlConnection("Server= & 1.1.1.1,1433 ; Database = database;uid = user;pwd = password;")

    Using the code below, I get an error that says DBCon is not Declared. What am I doing wrong? Thanks

    Code:
    Public Class SQ_Control
        Public Sub Pick_Con()
                  If My.Computer.Name = "Laptop" Then
                Dim DBCon As New SqlConnection("Server=" & My.Computer.Name & "\SQLEXPRESS;Database = master;Integrated Security=SSPI;")
            Else Dim DBCon As New SqlConnection("Server= & 1.1.1.1,1433 ; Database = database;uid = user;pwd = password;")
            End If
        End Sub
    	  Public Sub New(Connectionstring As String)
            Call Pick_Con()
            DBCon = New SqlConnection(Connectionstring)
    		    End Sub
    	End Class
    	
    	I get an error that says DBCon is not Declared
    I don't program, I beat code into submission!!!

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: If statement for dual login on sql server

    Quote Originally Posted by vbcub View Post
    What am I doing wrong?
    Pretty much everything. I'd start by getting the horizontal spacing corrected in your code so that things line up and people don't go cross-eyed trying to follow where code blocks begin and end.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2016
    Posts
    113

    Re: If statement for dual login on sql server

    Thank you.
    I don't program, I beat code into submission!!!

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: If statement for dual login on sql server

    Code:
    Public Class SQ_Control
    
        Public Sub Pick_Con()
            If My.Computer.Name = "Laptop" Then
                Dim DBCon As New SqlConnection("Server=" & My.Computer.Name & _
                       "\SQLEXPRESS;Database = master;Integrated Security=SSPI;")
            Else
                Dim DBCon As New SqlConnection("Server= & 1.1.1.1,1433 ; _
                       Database = database; uid = user;pwd = password;")
            End If
        End Sub
        
        Public Sub New(Connectionstring As String)
            Call Pick_Con()
            DBCon = New SqlConnection(Connectionstring)
        End Sub
    
    End Class
    	
    	I get an error that says DBCon is not Declared

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2016
    Posts
    113

    Re: If statement for dual login on sql server

    Thank you.
    I don't program, I beat code into submission!!!

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: If statement for dual login on sql server

    Here are some of the things that are wrong with the code:
    • If you declare a variable inside a Sub/Function, it only exists inside that Sub/Function - which means that DBCon only exists inside Pick_Con (so you cannot use it in New).
    • If you declare a variable inside a control structure (If/For/Case/...) it only exists inside that control structure - which means that there are two completely different and unrelated DBCon's in your code, one that only exists between the If/Else, and one that only exists between the Else/EndIf.
    • Even if the declaration of DBCon was moved appropriately, anything Pick_Con does is ignored - because the last line of New overwrites the value.
    • As you are passing a ConnectionString into New, you should have altered the code that uses this class, and probably not this class (either have the If in the calling class to set up the connection string, or remove the connection string from the calling code and only have it inside this class).

    Assuming that there is no other code inside the SQ_Control class...
    • If you set up connections in multiple places and want to centralise the connection string, you could use this version:
      Code:
      Public Static Class SQ_Control
          Public Static Function ConString() as String
              If My.Computer.Name = "Laptop" Then
                  Return "Server=Laptop\SQLEXPRESS;Database = master;Integrated Security=SSPI;"
              Else
                  Return "Server=1.1.1.1,1433;Database = database; uid = user;pwd = password;"
              End If
          End Function
      End Class
      Then whenever you need a connection string use it like this:
      Code:
            Dim myCon as New SQLConnection(SQ_Control.ConString)
    • If there is only one place SQ_Control is called from, you could remove it entirely, and do the work in the caller. Assuming your current caller code is like this:
      Code:
            Dim myCon as New SQ_Control("Server=Laptop\SQLEXPRESS;Database = master;Integrated Security=SSPI;")
      ...you could use this:
      Code:
            Dim connectionString as String
            If My.Computer.Name = "Laptop" Then
               connectionString = "Server=Laptop\SQLEXPRESS;Database = master;Integrated Security=SSPI;")
            Else
               connectionString = "Server=1.1.1.1,1433;Database = database; uid = user;pwd = password;")
            End If
            Dim myCon as New SQLConnection(connectionString)

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Feb 2016
    Posts
    113

    Re: If statement for dual login on sql server

    Thank you si_the_geek. You pointed me in the right direction. The explanation that you took the time to write taught me things that I didn't know or even think about. Thank you for your thoughtful and informative post.

    The reason that I wanted to have dual logins is that I tried to log in with the ip address for the home computer that the database was on and a remote computer. The code below worked, but took forever to connect especially on my home computer:

    Code:
      Dim DBCon As New SqlConnection("Server= & 1.1.1.1,1433 ; _
                       Database = database; uid = user;pwd = password;")
    So then I decided to try to use the if,then statement to determine if I was at home or away so I could run the code faster if I was at home. After your help, I went looking for the definitive login for an ip address in vb to code into my program and found the code below :

    Code:
     Public DBCon As New SqlConnection("Network Library=DBMSSOCN;" &
                                "Data Source=x.x.x.x,1433 ;" &
                                "Initial Catalog=Master;" &
                                "User ID=id;" &
                                "Password=pwd")
    Just for the heck of it, I ran it standalone from my home computer and found out it worked great. It also is very quick on the remote computer as well. Without your help, I probably would have been very frustrated.

    Thank you very much for the time and talents you shared with me.

    Carroll
    I don't program, I beat code into submission!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width