how to insert variables into vb.net connection string

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&";Pa开发者_StackOverflow中文版ssword="&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!!!

---

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).

---

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;

---

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")

---

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.