SqlConnection is null - Possible Reasons?

Question

I was following this tutorial in order to get a dropdown menu working with a database:
http://www.c-sharpcorner.com/UploadFile/4d9083/binding-dropdownlist-in-mvc-in-various-ways-in-mvc-with-data/

However, I encountered a problem.

Whilst following the tutorial, I got to this step: "For a Dapper User I am adding another class with the name MobileContext."

On line 3 of the code, it says: SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MYConnector"].ToString());

^ This is where I get my runtime/ compile error.

When I try to implement the same into my program, I get the following error:

An exception of type 'System.NullReferenceException' occurred in Project_v3.dll but was not handled in user code

Additional information: Object reference not set to an instance of an object.

Here are my relevant files:

Server Explorer view: http://gyazo.com/288a07eb2ec5c2aa4ea25d1eb6eda187

Contents of FlightsTable: http://gyazo.com/9d1b014ecdba1e244c2f6957b6d9397c

Table Layout of FlightsTable: http://gyazo.com/b8a25fd48e725dc4690ed54bb3b0cca2

FlightModel:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Project_v3.Models
{
    [Table("FlightsTable")]
    public class FlightModel
    {
        [Key]
        public int FlightID { set; get; }
        public string Departure { set; get; }
        public string Arrival { set; get; }
        public int NumberOfSeats { set; get; }
        public int NumberOfFlights { set; get; }
        [NotMapped]
        public SelectList FlightList { get; set; }
    }
}

FlightContext:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Dapper;
using System.Data;

namespace Project_v3.Models
{
    public class FlightContext
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MYConnector"].ToString());
        public IEnumerable<FlightModel> GetFlightList()
        {
            string query = "SELECT [FlightID],[Departure]FROM [MyMainDBEntities2].[dbo].[FlightsList]";
            var result = con.Query<FlightModel>(query);
            return result;
        }  
    }
}

MyTemplateController (basically my HomeController):

FlightContext FCon = new FlightContext();
public ActionResult BookFlight()
{
    FlightModel FD = new FlightModel();

    FD.FlightList = new SelectList(FCon.GetFlightList(), "FlightID", "Departure");
    return View();
}

EDIT Web.config (as requested) however there are two:

Please See screenshot: http://gyazo.com/1bfb0886f82ac6dc2d1a739ddcb02999 https://gist.github.com/anonymous/e7552b6ee5609205ac18

Would somebody be able to point out what I'm doing wrong/ where I've messed up? What have I missed that's causing this error to continue appearing?

EDIT 3: It works however I now get this error on this line of FlightContext: var result = con.Query<FlightModel>(query);

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Invalid object name 'MyMainDBEntities2.dbo.FlightsList'.

EDIT 4: AttachDbFilename="C:\Users\[MyName]\Desktop\ASP.NET Project v3 - (Original - Edit Version)\Project v3\Project v3\App_Data\MyMainDB.mdf";Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework

where is your connection string. this statement `ConfigurationManager.ConnectionStrings["MYConnector"].ToString()` is not pointing to it. just check your config file for the same — Ameya Deshpande, Apr 10 '15 at 07:40
I'm not sure you have to call `.ToString()` with `ConnectionStrings["name"]` — Maxim Zhukov, Apr 10 '15 at 07:43
Table name is wrong in your query. Replace FlightsList with FlightsTable — Rahul Nikate, Apr 10 '15 at 08:13
@RahulNikate Yep thank you, I've noticed that. It's changed but still gives the same error. `An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Additional information: Invalid object name 'MyMainDBEntities2.dbo.FlightsTable'.` — , Apr 10 '15 at 08:19

score 1 · Accepted Answer · edited Mar 25 '17 at 21:56

1

Look for the connection string in web.config named "MYConnector"

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MYConnector"].ToString());

There is no connection string defined with name "MYConnector" in your web.config. Create connection string in web.config like below

web.config :

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="MYConnector" providerName="System.Data.SqlClient" connectionString="Data Source=111.111.111.111;Initial Catalog=DatabaseName;user id=UserName;password=Password;" />

</connectionStrings>

edited Mar 25 '17 at 21:56

Amit Kumar

85
1
2
8

answered Apr 10 '15 at 07:42

Rahul Nikate

6,192
5
42
54

Hi, thanks for the reply. Does it matter that I'm hosting the database on the Visual Studio 2013 solution file? Also; under which header do I place the `` tag? Thanks! – Apr 10 '15 at 07:50
@BorisSmith Right click on MyMainDBEntities2 database under data connections in server explorer and click Properties>Look for connection string. Take that connection string and add it to web.config – Rahul Nikate Apr 10 '15 at 07:56
Okay, thanks. I have done so however I now get another error xD Please see edit 3. Thank you very much for the reply! – Apr 10 '15 at 08:03
I have one more question. In MyMainDBEntities2 <- Located in the Server Explorer. When I right click and go to connection string, it says that my connection string contains the following: (see edit 4). Where would I place the additional code? – Apr 10 '15 at 08:14

User2012384 · Answer 2 · 2015-04-10T08:11:28.980

0

Your web.config doesn't have the connection string named "MYConnector", try adding it into the web.config:

<connectionStrings>
     <add name="MYConnector" providerName="System.Data.SqlClient" connectionString="ConnectionStringHere" />
</connectionStrings>

Here's your reference to build a connection string:

http://www.connectionstrings.com/

In that case, why not store the connectionstring as a string?

<configSections>
   <appSettings>
      <add key="MYConnector" value="ConnectionString"/>
   </appSettings>
</configSections>

In your code:

SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["MYConnector"].ToString());

edited Apr 10 '15 at 08:11

answered Apr 10 '15 at 07:48

User2012384

4,769
16
70
106

Hi thank for the reply! Much appreciated. Is there a way to find the connection string? ie the `metadata=res://blabla`? Sorry, I am very new to this. Thanks! – Apr 10 '15 at 07:52
It's wrong connection string. "" – Rahul Nikate Apr 10 '15 at 07:53
@BorisSmith Are you using entity framework? – User2012384 Apr 10 '15 at 07:55
@Mr.香港人 The tutorial I used said to use Dapper. – Apr 10 '15 at 08:00
Depending on what kind of database you use, this site might help you to figure out the correct connection string: http://www.connectionstrings.com/ – Corak Apr 10 '15 at 08:07
@BorisSmith Then I suggest to store the connectionstring as a string, and read the string directly, I'll update my answer – User2012384 Apr 10 '15 at 08:07
@BorisSmith I've updated, please try, see if it works – User2012384 Apr 10 '15 at 08:11
@Mr.香港人 That's fantastic, thanks! However, I have one more question. In `MyMainDBEntities2` <- Located in the Server Explorer. It says that my connection string contains the following: (see edit 4). Where would I place the additional code? – Apr 10 '15 at 08:13
@BorisSmith Oh that's the connection string, put it in the "value", it should work – User2012384 Apr 10 '15 at 08:16

SqlConnection is null - Possible Reasons?

2 Answers2