Possible reasons why SqlConnection is NULL

Question

Using Visual Studio 2012 which was recently installed but now I can't connect to our SQL Server database.

These are the steps I'm following

create App1.config

type this in App1.config:

 <?xml version="1.0" encoding="utf-8" ?>
 <configuration>
   <connectionStrings>
     <add name ="xxx" connectionString="USER ID=xx;PASSWORD=xx;PERSIST SECURITY INFO=True;Data Source=xx;Initial Catalog=xx" />
   </connectionStrings>
 </configuration>

Add a reference to the project to System.Configuration

Create access to namespaces via:

 using System.Data.SqlClient;
 using System.Data;
 using System.Configuration;

implement the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace ConsoleApplication10 {
    class Program {
        static void Main(string[] args) {

            SqlConnection conn = null;
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["xxx"].ConnectionString);

        }
    }
}

I've created a new console app and added the above and I still get an error NullReferenceException was unhandled Object reference not set to an instance of an object. ...

enter image description here

EDIT

Via the immediate window I determined that the following is null:

ConfigurationManager.ConnectionStrings["xxx"].ConnectionString

If I hard-code the connection string into the constructor for SqlConnection then it connects ok

What am I missing - something really obvious!! Or is this in connection with my new VS ?

Determine exactly which variable/expression was null by using the debugger. — usr, Jan 23 '13 at 12:57
What's null? `conn` or the connection string returned by the `ConfigurationManager`? — Thorsten Dittmar, Jan 23 '13 at 12:57
@Cybermaxs ok - I'll determine exactly what is null and add to the OP — whytheq, Jan 23 '13 at 12:58
@ThorstenDittmar - thanks : looks like the configuration manager is returning a null string - if I hard-code the string in then it executes fine — whytheq, Jan 23 '13 at 13:07
@tschmit007 you can see the xml I have in my OP - it just says ` — whytheq, Jan 23 '13 at 13:09
@tschmit007 I'm pretty sure that he just put in the `xxx` to abbreviate the longer connection string name... — Thorsten Dittmar, Jan 23 '13 at 13:09
yes I quite sure also... my question is: are you sure of the coherence of the name and the litteral string. What is the result of Console.Writeline(ConfigurationManager.ConnectionStrings.Count). — tschmit007, Jan 23 '13 at 13:12
Almost all cases of NullReferenceException are the same. Please see "[What is a NullReferenceException in .NET?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-in-net)" for some hints. — John Saunders, Jan 23 '13 at 13:15
great and if you try to connect (or writeline) with ConfigurationManager.ConnectionStrings[0].ConnectionString ? And by the way WriteLine ConfigurationManager.ConnectionStrings[0].Name. — tschmit007, Jan 23 '13 at 13:16
@tschmit007 it comes up with some random connectio0n string that I've not named!! `data source=.\SQLEXPRESS;Integrated Security=SSPI...` ; I'm using full SQL-Server 2008 R2 ! — whytheq, Jan 23 '13 at 13:19
@whytheq then you have to check the properties of your app1.config and/or of your application in VS. This connection string should come from machine.config or elsewhere. The response of tomfanning seems to be the right one to investigate. — tschmit007, Jan 23 '13 at 13:21
@tschmit007 - how do I check the properties of my app1.config in VS ? — whytheq, Jan 23 '13 at 13:21
How about opening up the project properties, switching to the `Settings` tab and checking which connection strings are there? You should not edit the `app.config` file directly. — Thorsten Dittmar, Jan 23 '13 at 13:22
@tschmit007 - ok : thanks for your help - fixed now; jot down your comments as an answer as they really helped. — whytheq, Jan 23 '13 at 13:23
See my answer here for [app.config][1] [1]: http://stackoverflow.com/questions/8612560/fetching-connection-string-from-appconfig-file-in-c-sharp/32036555#32036555 — Robert Achmann, Aug 16 '15 at 16:13

score 3 · Answer 1 · answered Jan 23 '13 at 13:04

3

Check your output folder.

Assuming your application is called myapp.exe, there should be myapp.exe.config.

This should exist, and should contain the contents of your app.config file in Visual Studio.

If it doesn't, check whether you already have an app.config file elsewhere (I notice you called your file app1.config)

answered Jan 23 '13 at 13:04

tomfanning

9,552
4
50
78

Even if it doesn't exist, the default values will be used. – Thorsten Dittmar Jan 23 '13 at 13:11
+1 thanks tom - exactly as you noticed app**1**.config was the problem. I thought this was a relevent question but already on -2 ! – whytheq Jan 23 '13 at 13:25

tschmit007 · Accepted Answer · 2013-01-23T15:16:19.570

3

check what is in the ConfigurationManager.ConnectionStrings by excecuting at least the following:

if (ConfigurationManager.ConnectionStrings != null ) {
    Console.WriteLine(ConfigurationManager.ConnectionStrings.Count);
    Console.WriteLine(ConfigurationManager.ConnectionStrings[0].ConnectionString);
    Console.WriteLine(ConfigurationManager.ConnectionStrings[0].Name);
    ....
} else {
    Console.WriteLine("null");
}

This will highlight any obvious problems like duplication of the App.config file which could well be the case as you mentioned App1.config in the OP.

edited Jan 23 '13 at 15:16

answered Jan 23 '13 at 13:26

tschmit007

7,559
2
35
43

+1 thanks for all the help - a frustrating question but if you feel kind you might like to cancel out some of the downvotes it seems to be gathering! – whytheq Jan 23 '13 at 13:28
@whytheq I think downvote came from formatting more than from relevance of the question. – tschmit007 Jan 23 '13 at 13:32
formatting looks fine - I've fully explained the problem with all code and screenshots...just edited your answer with my embarrassing finding! – whytheq Jan 23 '13 at 13:34
You missed `ConfigurationManager.ConnectionStrings` – John Saunders Jan 23 '13 at 13:35
@JohnSaunders no - I added a configuration file to the solution and there was already one in there; so even though I was using the correct name I think the project was referring to the original config file. Once I deleted one of them and renamed to App.config rather than App1.config all was well in the World again ! – whytheq Jan 23 '13 at 15:01
... and then did a build. App.config becomes executableName.exe.config, which is where the real settings are. – John Saunders Jan 23 '13 at 15:05
@tschmit007: I meant you should also check if `ConfigurationManager.ConnectionStrings` is null. – John Saunders Jan 23 '13 at 15:06

score 2 · Answer 3 · answered Jan 23 '13 at 13:00

2

Have your tried checking what the value of ConfigurationManager.ConnectionStrings["xxx"].ConnectionString is and hardcoding with that value? Do you still get null?

Also it's advised to created the connection like this:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["xxx"].ConnectionString))
{
        //code        
}

I would reply but I don't have enough rep yet ... :-(

answered Jan 23 '13 at 13:00

TheKingDave

926
1
8
16

let me try with it hard-coded ; can determine if it is the connection reference causing the problem then – whytheq Jan 23 '13 at 13:01

score 2 · Answer 4 · answered Jan 23 '13 at 13:10

2

Guessing from the fact that your code does not "do anything" with conn yet, I'm pretty sure that the ConfigurationManager returns null for the connection string name you pass in. An exception is thrown by the SqlConnection's constructor when passing in null instead of a valid connection string.

answered Jan 23 '13 at 13:10

Thorsten Dittmar

55,956
8
91
139

if I add code so that my code does something - like run a stored procedure than it still errors on this line of code – whytheq Jan 23 '13 at 13:13
Yes of course! I meant: As you are not using `conn` in the source code you posted in your question, the only thing that **can** be `null` is the result of the `ConfigurationManager` call. Had there been other code actually **using** `conn`, `conn` could also have been `null`, causing the exception. But that isn't the case, so the exception **must** come from `SqlCommand`s constructor. – Thorsten Dittmar Jan 23 '13 at 13:17
+1 thanks for help (although I could almost see the smoke coming out of your ears!!) seems like I had duplicate app files in the solution! – whytheq Jan 23 '13 at 13:27
I know: I could punch myself in the face!...can i edit them _not_ manually? ...via the properties section in the solution? – whytheq Jan 23 '13 at 14:54
Yes, the connection string is a setting. – Thorsten Dittmar Jan 24 '13 at 08:10
where do I find this setting in Visual Studio ...as someone with no xml knowledge I think it'd be safer if I found another way of setting someof this information – whytheq Jan 24 '13 at 08:25
Double click "Properties" in your solution explorer for the project, then, in the Project Properties, select the "Settings" tab. There are all the program settings. Connection strings are just a setting of type "Connection String". – Thorsten Dittmar Jan 24 '13 at 09:44

score 0 · Answer 5 · edited May 23 '17 at 12:29

0

When you place your connection strings in Properties->Settings, as a connection string, access them with full namespace, and your done.

See my answer here

edited May 23 '17 at 12:29

Community

1
1

answered Aug 16 '15 at 16:15

Robert Achmann

1,986
3
40
66

Possible reasons why SqlConnection is NULL

5 Answers5