how to insert variables into vb.net connection string

Question

I'm trying to insert variables into my connection string hoping that they will be saved for executing multiple queries.

What I'm trying is something like this:

Private sConnectionString As String = "Provider=TDOLEDB;Data Source=TDDEV;Persist Security Info=True;User ID="&user&";Password="&pass&";Default Database=bcpm_ddbo;Session Mode=ANSI;"

but it's not working.

OR

I wouldn't mind doing something like below, but only one prompt and leave the connection open?

Private sConnectionString As String = "Provider=TDOLEDB;Data Source=TDDEV;Persist Security Info=True;Prompt=Complete;Default Database=bcpm_ddbo;Session Mode=ANSI;

"

any help would be greatly appreciated!!!

Konrad Rudolph · Accepted Answer · 2011-01-12 18:09:22Z

5

The reason why your original code is not working is that you’re using the following:

"…"&user&"…"

The first & is fine. But the second & is ambiguous in this case because a variable name immediately followed by a & has a special meaning in VB¹. To make this work, you need to insert a whitespace before the second &.

In fact, you should always insert whitespace before and after every binary operator. This just makes the code infinitely more readable.

"…" & user & "…"

Now the code works. But using String.Format is yet more readable here.

¹ The real reason is that with older BASIC and VB versions (and still with Option Explicit Off, which is very bad style!) variables didn’t have to be declared. In order to still have the possibility to say that variable x is of a given type, one could append a type suffix. & is the type suffix for Long in VB6 (and probably also in VB.NET but I haven’t looked it up).

answered Jan 12, 2011 at 18:09

Konrad Rudolph

549k142 gold badges967 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris Haas Over a year ago

I second using String.Format instead of concatenation.

gfuller40 Over a year ago

But in the "String.Format" it's looking to specify a username and password - these are set server side and can't be hard coded into the app because they will need to be changed among at least 10 users, once every 3 months. - Konrad I really appreciate the explanation!

Konrad Rudolph Over a year ago

@gfuller: nothing needs to be hard-coded. String.Format can use variables just fine.

gfuller40 Over a year ago

THANK YOU so much - you are absolutely right and it works perfect. Awesome!

Nickolodeon · Accepted Answer · 2011-01-11 20:46:59Z

0

Depending on db client provider you can use OracleConnectionStringBuilder or SqlConnectionStringBuilder. These classes expose common connection string fields as properties (like DataSource, Password etc) which are get;set;

answered Jan 11, 2011 at 20:46

Nickolodeon

2,9663 gold badges26 silver badges41 bronze badges

Comments

Jeff · Accepted Answer · 2011-01-11 20:51:21Z

0

Im not sure if I understand. You cannot reuse a connection with different users. You are going to need a new connection for each user that you want to connect to the database. If you are just trying to make it easier to format the string itself, you could use Nick's answer or string.Format:

Dim format As String = "Provider=TDOLEDB;Data Source=TDDEV;Persist Security Info=True;User ID={0};Password={1};Default Database=bcpm_ddbo;Session Mode=ANSI;"
Dim connectionString1 As String = String.Format(format, "some username", "some password")
Dim connectionString2 As String = String.Format(format, "another username", "another password")

answered Jan 11, 2011 at 20:51

Jeff

14.3k12 gold badges61 silver badges107 bronze badges

Comments

BenAlabaster · Accepted Answer · 2011-01-12 18:00:38Z

0

I'm not exactly following what you're trying to do, but it seems to me that using String.Format would go part way to solve your problem - build a format:

Private sConStrFormat As String = "Provider=TDOLEDB;Data Source=TDDEV;Persist Security Info=True;User ID={0};Password={1};Default Database=bcpm_ddbo;Session Mode=ANSI;"

I would assume that you would be reading your connection string format in from your configuration file (app.config/web.config), so you would define the format in there and then use String.Format to push in the correct username and password.

Then when you want to use it you just do:

Dim UserName As String = "MyUserName"
Dim Password As String = "MyPassword"
sConStr = String.Format(sConStrFormat, UserName, Password)
Using Con As SqlConnection = new SqlConnection(sConStr)
    Con.Open
    'Do whatever you need with your open connection...
End Using
'....

Does that do what you're looking for? The alternative ConnectionStringBuilder would be more preferable - you just need to overwrite the UserID and Password properties (which are Get/Set), but this is perfectly usable.

Alternatives are to enable integrated security and have the user log into the application and use impersonation or use their windows identity for their interaction with the database. Check out the System.Security.Principal namespace and the WindowsIdentity class for pointers on how to do this.

edited Jan 12, 2011 at 18:00

answered Jan 11, 2011 at 20:50

BenAlabaster

40k22 gold badges115 silver badges152 bronze badges

2 Comments

gfuller40 Over a year ago

Thanks a ton you guys! It's a multi-user app, but every user will have there own Teradata login. Will this work with that? I want to have a login form and store the data from the txtboxes to string and reuse here in the connection string.

BenAlabaster Over a year ago

You should probably also check out SecureString if you're going to be keeping it around for reuse. Leaving the password kicking around in memory in an easily readable format isn't really the best thing to be doing.

Collectives™ on Stack Overflow

how to insert variables into vb.net connection string

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related