SqlConnection giving object reference error

Question

I have to use connection string for my query, but I am getting an error

object reference not set to an instance of an object.

This is my code:

string strSql = "select first_name + ' ' + last_name name, email from user_mst where mkey in (" + Session["UserId"].ToString() + ")";

DataTable table = new DataTable();

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString()))
{
    conn.Open();

    using (SqlDataAdapter dbdata = new SqlDataAdapter(strSql, conn))
    {
        dbdata.Fill(table);
    }

    conn.Close();
}

I get error at

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString()))

but my connections seems to be exactly fine.

Try modifying your code to assign `string conString = ConfigurationManager.AppSettings["ConnectionString"].ToString();` and use the `conString` variable in your connection declaration. Likely the `ConnectionString` element doesn't exist in your configuration file. This will at least let you debug your code a little better. — gmiley, May 14 '16 at 12:51
http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it — DavidG, May 14 '16 at 12:52
Can you show the relevant parts of your web.config? Something is odd in your code. Why you use AppSettings to read the connectionstrings section? — Steve, May 14 '16 at 12:57
Well, I could if you want, but it really isn't an answer IMO, just a suggestion to help debug your problem. — gmiley, May 14 '16 at 12:57
@gmiley: i tried ur way like this `string conString = ConfigurationManager.AppSettings["ConnectionString"].ToString(); using (SqlConnection conn = new SqlConnection(conString)) {` still getting the same error — Nad, May 14 '16 at 12:59
You are using `AppSettings` and should probably be using `ConnectionStrings`. — DavidG, May 14 '16 at 12:59
Can you clarify if your connectionstring is contained in an AppSettings section or in the ConnectionStrings section of your web.config? — Steve, May 14 '16 at 13:04
Also, after stripping out any sensitive information (name/password) please post the section of your config file containing your connection string. — gmiley, May 14 '16 at 13:05
@Steve: it is in the `connectionstring` section of config file — Nad, May 14 '16 at 13:06
Add a reference to `System.Configuration`. Move your connection string in the connection strings section in your config file and get it with `string s = System.Configuration.ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;` — shadow, May 14 '16 at 15:46

gmiley · Accepted Answer · 2016-05-14T13:17:48.343

As you requested, here is the modified code should make it easier to debug, or at least just catch the missing connection string configuration element.

    string conString = string.Empty;
    try
    {
       conString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
    }
    catch(Exception)
    {
       MessageBox.Show("Unable to retrieve 'ConnectionString' from configuration file.");
    }
    string strSql = "select first_name + ' ' + last_name name, email from user_mst where mkey in (" + Session["UserId"].ToString() + ")";
    DataTable table = new DataTable();
    using (SqlConnection conn = new SqlConnection(conString))
    {
        conn.Open();
        using (SqlDataAdapter dbdata = new SqlDataAdapter(strSql, conn))
        {
            dbdata.Fill(table);
        }
        conn.Close();
    }

After debugging your code, your problem appears to be stemming from your configuration file and how you are accessing your Connection String. Changing your code from:

try
{
   conString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
}
catch(Exception)
{
   MessageBox.Show("Unable to retrieve 'ConnectionString' from configuration file.");
}

to

try
{
   conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
}
catch(Exception)
{
   MessageBox.Show("Unable to retrieve 'ConnectionString' from configuration file.");
}

Assuming the ConnectionString entry is also named "ConnectionString", this should solve your problem.

TLDR: As mentioned a few times in comments, change AppSettings to ConnectionStrings will fix your problem.

it went in catch `Object reference not set to an instance of an object.` — Nad, May 14 '16 at 13:06
Ok, that is correct then, that's what it is intended for. That means we definitely have a problem with your config file. As I mentioned above, if you could include the relevant section from your config file, we can get this fixed quickly. — gmiley, May 14 '16 at 13:09
i got it, just changed it from `AppSettings` to `Connectionstring` and it worked — Nad, May 14 '16 at 13:10

score 1 · Answer 2 · answered May 14 '16 at 15:04

1

Please check AppSettings in web.config properly .I am sure db path that you write in appsetting is wrong .

answered May 14 '16 at 15:04

Arindam

178
1
5

Whilst this may theoretically answer the question, [it would be preferable](//meta.stackoverflow.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Enamul Hassan May 15 '16 at 02:20

score 0 · Answer 3 · answered May 14 '16 at 12:49

0

System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString()) is returning null and then passed to SqlConnection constructor. Check if your configuration file is valid and it contains key named ConnectionString. Something like this: <add key="ConnectionString" values="[your connection string]"/>

answered May 14 '16 at 12:49

Nino

6,931
2
27
42

yes it contains the values, I have checked it – Nad May 14 '16 at 13:00

score 0 · Answer 4 · edited May 23 '17 at 12:08

0

Usually the connectionstrings are stored in a proper section of your web config. This section is easily read by the WebConfigurationManager and it is stored in the ConnectionString collection

// You need this reference to System.Web.Configuration.dll
// and remove the using System.Configuration;
using System.Web.Configuration;

....
string cons = WebConfiguration.ConnectionStrings["ConnectionString"].ConnectionString;

using(SqlConnection cnn = new SqlConnection(cons))
.....

Also, while probably in your context using ConfigurationManager will work as well, you really should use the WebConfigurationManager for web applications

See ConfigurationManager vs WebConfigurationManager

edited May 23 '17 at 12:08

Community

1
1

answered May 14 '16 at 12:50

Steve

213,761
22
232
286

so, should I remove `using` and directly use that. Also I don't have any issue in config file – Nad May 14 '16 at 12:53
session contains proper value . I checked it – Nad May 14 '16 at 12:54
Absolutely no, why do you want to remove using? It is always required to using around a disposable object – Steve May 14 '16 at 12:54
ohh, getting error as `string does not contain a definition for connectionstring` this is the code `using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ConnectionString))` – Nad May 14 '16 at 12:56

score 0 · Answer 5 · answered May 14 '16 at 13:11

Tostring() will throw an exception when written inside a using statement, I don't know why but I too faced this problem. Your code should be like this:

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    conn.Open();
    using (SqlDataAdapter dbdata = new SqlDataAdapter(strSql, conn))
    {
        dbdata.Fill(table);
    }
    conn.Close();
}

you have to add reference to System.Configuration in your project by right clicking "References" and "add reference" in assemblies->Framework before using this code.

SqlConnection giving object reference error

5 Answers5