SQL Connection String Problem

[LOOKUP_BI]

I have databases that have names as XXX.XX_ll.

During a regular querying of database in SQL as well, the database name has to be put into [ ] as [XXX.XX_ll]

When I pass the [] around the database name in connection string,it fails on me

Here is what Im passing to my connection string

Dim str As String = "Data Source=" + "'" + ServerName + "'" + ";Initial Catalog=" + "'[" + dbname + "]'" + ";User ID=;Password=p"

 

[jmcilhinney]

First up, what's the point of concatenation two string literals? This does the same thing as your code and is easier to read:

Dim str As String = "Data Source='" + ServerName + "';Initial Catalog='[" + dbname + "]';User ID=;Password=p"

Secondly, I strongly suggest using the concatenation operator to concatenate rather than the addition operator:

Dim str As String = "Data Source='" & ServerName & "';Initial Catalog='[" & dbname & "]';User ID=;Password=p"

As for the question, I would assume that the issue would be due to the fact that you have wrapped the server name and database name in single quotes, which are not used in connection strings. Also, I'm not sure that you'd need the brackets in the connection string either. Connection strings are not SQL code.

 

[cjard]

This does the same thing as your code and is easier to read:

I recommend:

Dim connStr as String = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", servername, dbname, user, pass)

Most readable

 

[jmcilhinney]

I recommend:

Dim connStr as String = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", servername, dbname, user, pass)

Most readable

Personally, I'd use a SqlConnectionStringBuilder, but I do agree that String.Format is preferable to string concatenation in this case. I was referring to string concatenation specifically though, i.e. if you're going to use it then don't make it more complicated than it needs to be.

 

[cjard]

Amen!

Actually, most readable is to use the wizard and let it hide the string in the settings file, then we don't have to gaze upon its ugliness in code