-
Jun 26th, 2018, 01:55 PM
#1
Thread Starter
Lively Member
[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!!!
-
Jun 26th, 2018, 02:01 PM
#2
Re: If statement for dual login on sql server
Originally Posted by vbcub
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.
-
Jun 26th, 2018, 02:07 PM
#3
Thread Starter
Lively Member
Re: If statement for dual login on sql server
I don't program, I beat code into submission!!!
-
Jun 26th, 2018, 02:37 PM
#4
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
-
Jun 26th, 2018, 02:45 PM
#5
Thread Starter
Lively Member
Re: If statement for dual login on sql server
I don't program, I beat code into submission!!!
-
Jun 27th, 2018, 02:38 AM
#6
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)
-
Jun 27th, 2018, 05:14 PM
#7
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|