Tiaan Tiaan - 1 year ago 51
SQL Question

Connection String stored in Web.config file doesn't open SQL connection

So I am trying to learn to use VS2015 and I have seen a number of similar postings on the internet relating to this problem and none of them have gotten me any closer to a solution.

I am trying to write into a SQL database and have tested both my database and connection string and confirm they are both working well.

So with that out of the way I went into Web.config and created a definition for said connection string:

<add name ="Name" connectionString="Data Source=<desktopname>\\SQLEXPRESS01;Integrated Security=SSPI;Initial Catalog=Database1"/>

As far as I can tell this works fine...

However, when I declare the string as follows:

MyConnectionString = ConfigurationManager.ConnectionStrings["Name"].ConnectionString;

Somehow what this returns is not understandable by:

SqlConnection iData = new SqlConnection(myConnectionString);

As when I try and "open" the connection it fails... Despite the exact same connection string works when declared like this within the C# code:

SqlConnection iData = new SqlConnection("Data Source=<desktopname>\\SQLEXPRESS01;Integrated Security=SSPI;Initial Catalog=Database1");

Does anyone have any ideas?

the problem lies with how the information is handled from Web.config to the string to the SqlConnection as far as I can tell.

Notably everything runs perfectly unless I use the string as the connection string...

Thanks in advance!

Answer Source

In C# the backslash needs to be doubled, because the compiler treats the backslash as an escape character. And then the \\ compiles to \ in the actual string used at runtime.

In XML that is not the case, it uses a different parser, so in Web.Config you should use a single \:

<add name="Name" connectionString="Data Source=<desktopname>\SQLEXPRESS01;Integrated Security=SSPI;Initial Catalog=Database1"/>

On top of that you may also need to add providerName="System.Data.SqlClient":

    connectionString="Data Source=<desktopname>\SQLEXPRESS01;Integrated Security=SSPI;Initial Catalog=Database1"