Using Variables to connection to SQL2000 Database

  • Thread starter Thread starter Swifty_SySoP
  • Start date Start date
S

Swifty_SySoP

Hello everyone,
I have a question about SQL connection strings. I have a VB.NET
application where the user enters in a name and password into 2 text
boxes: txtName and txtPass, respectively. I have 2 variables that
takes any input from txtName and txtPass and puts them into two
variables: strUser and strPass, respectively.

I would like to use variables because not only is strUser used in
the ID, it is also the name of the database that the user connects to.
Here's what I have so far:

Imports System.Data.SqlClient
....
Dim oSQLConn As String

oSQLConn.ConnectionString = "Data Source=Bluffton_4" & _
"Initial Catalog=strUser;" & _
"User ID=strUser;" & _
"Password=strPass" & _
"Encrypt=yes"

oSQLConn.open()

I have noticed with the strUser and strPass it does act like a
variable: it only acts like text which really will not work well with
me. Is there some way to make the program recognize a variable for
the SQL Connection? Or some other idea that would be better? If
anyone could help me, this would be greatly appreciated. Thank you
for your time.

BTW - This program would only be used in our intranet and we do not
run Active Directory so I cannot use SSPI. Thanks again!


James Haynes
 
The code you posted is passing a literal string into the ConnectionString.
You need to evaluate the variables and store their contents in the string
you pass to ConnectionString. Try the following:

oSQLConn.ConnectionString = "Data Source=Bluffton_4" & _
"Initial Catalog=" & strUser & ";" & _
"User ID=" & strUser & ";" & _
"Password=" & strPass & ";" & _
"Encrypt=yes"

You may also want to look into the String.Format static method for
formatting string and the StringBuilder object in the System.Text namespace.

-Mike McGuire
 
Swifty:

Microsoft Highly encourages you not to accept user input for your connection
string. It's a security risk and in addition, many subtle changes can
circumvent connection pooling. With that said, it's your string
concatenation that's the problem: It should work if you do this:

oSQLConn.ConnectionString = "Data Source=Bluffton_4" & _
"Initial Catalog='" & strUser & "';" & _
"User ID='" & strUser;" & _ "Password='" &
strPass& "'" & _
"Encrypt=yes"
I may have made a typo b/c I don't have my IDA, but you need to concatenate
the variables outside of the quotes or it will use the "strUser" as a
literal instead of the variable you pass in. You may also want to look into
String.Format("DataSource={0} InitialCatalog={1}; UserID={2}; Password={3};
Encrypt = Yes", strUser, strUser, strPass. However, it also looks like
strUser is being used for both Initial catalog and UserID which may be a
problem. HTH,

Bill
 
Back
Top