locked
SqlConnection String “object reference not set to an instance of an object RRS feed

  • Question

  • User1571524970 posted

    Hi guys,

    I am getting an error in my asp.net mvc project when I try to access the 'TestController' and I am not sure why. This controller will be used to allow a user to upload an excel file from the index view; the data in this excel file will be used to populate the dbo.SchoolTest database table.

    The error is shown below. For some reason it is not accepting the connection string to my BookingSys.Models.BookingSysDb entity framework database. I also have a 2nd database which is used to store user authentication information (asnet-BookingSys-201~).

    System.NullReferenceException: 'Object reference not set to an instance of an object.' System.Configuration.ConnectionStringSettingsCollection.this[string].get returned null.

    j

    My Test Controller:

    namespace BookingSys.Controllers
    {
      public class TestController : Controller
      {
        //private BookingSysDb db = new BookingSysDb();
        SqlConnection con = new 
     SqlConnection(ConfigurationManager.ConnectionStrings[@"Data Source= (localdb)\MSSQLLocalDB;Initial 
     Catalog=BookingSys.Models.BookingSysDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;
    TrustServerCertificate=False; ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
    ].ConnectionString); OleDbConnection Econ; public ActionResult Index() { return View(); } [HttpPost] public ActionResult Index(HttpPostedFileBase file) { string filename = Guid.NewGuid() + Path.GetExtension(file.FileName); string filepath = "/excelfolder/" + filename; file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename)); InsertExceldata(filepath, filename); return View(); } private void ExcelConn(string filepath) { string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= {0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filepath); Econ = new OleDbConnection(constr); } private void InsertExceldata(string filepath, string filename) { string fullpath = Server.MapPath("/excelfolder/") + filename; ExcelConn(fullpath); string query = string.Format("Select * from [{0}]", "Sheet1$"); OleDbCommand Ecom = new OleDbCommand(query, Econ); Econ.Open(); DataSet ds = new DataSet(); OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ); Econ.Close(); oda.Fill(ds); DataTable dt = ds.Tables[0]; SqlBulkCopy objbulk = new SqlBulkCopy(con); objbulk.DestinationTableName = "dbo.SchoolTests"; objbulk.ColumnMappings.Add("AcademicYear", "AcademicYear"); objbulk.ColumnMappings.Add("RollNumber", "RollNumber"); objbulk.ColumnMappings.Add("OfficialSchoolName", "OfficialSchoolName"); objbulk.ColumnMappings.Add("Address1", "Address1"); objbulk.ColumnMappings.Add("Address2", "Address2"); objbulk.ColumnMappings.Add("Address3", "Address3"); objbulk.ColumnMappings.Add("Address4", "Address4"); objbulk.ColumnMappings.Add("County", "County"); objbulk.ColumnMappings.Add("Eircode", "Eircode"); objbulk.ColumnMappings.Add("LocalAuthority", "LocalAuthority"); objbulk.ColumnMappings.Add("X", "X"); objbulk.ColumnMappings.Add("Y", "Y"); objbulk.ColumnMappings.Add("ITMEast", "ITMEast"); objbulk.ColumnMappings.Add("ITMNorth", "ITMNorth"); objbulk.ColumnMappings.Add("Latitude", "Latitude"); objbulk.ColumnMappings.Add("Longitude", "Longitude"); con.Open(); objbulk.WriteToServer(dt); con.Close(); } } }

    can anyone clear up this error for me?

    Monday, July 22, 2019 11:53 PM

Answers

  • User1520731567 posted

    Hi darego,

    According to your error message,I find your connection string is wrong,you could modify it :

        SqlConnection con = new 
     SqlConnection(ConfigurationManager.ConnectionStrings[@"Data Source= (localdb)\MSSQLLocalDB;Initial 
     Catalog=BookingSys.Models.BookingSysDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;
    TrustServerCertificate=False; ApplicationIntent=ReadWrite;MultiSubnetFailover=False"].ConnectionString);

    to:

          SqlConnection con = new
    SqlConnection(ConfigurationManager.ConnectionStrings["yourConnectionStringName"].ConnectionString);

    or:

    SqlConnection con = new SqlConnection(@"Data Source= (localdb)\MSSQLLocalDB;Initial 
    Catalog=BookingSys.Models.BookingSysDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;
    TrustServerCertificate=False; ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 8:39 AM

All replies

  • User753101303 posted

    Hi,

    You are confusing an actual connection string and the "name" of a connection string. ConfigurationManager.ConnectionString["MyString"].ConnectionString is to retrieve a connection string stored in the web.config file using :

    <configuration>
    <connectionStrings>  
      <add name="MyString"   
       providerName="System.Data.ProviderName"   
       connectionString="Data Source=(localdb)\MSSQLLocalDB;etc..." />  
    </connectionStrings>
    </configuration>
    Tuesday, July 23, 2019 8:34 AM
  • User1520731567 posted

    Hi darego,

    According to your error message,I find your connection string is wrong,you could modify it :

        SqlConnection con = new 
     SqlConnection(ConfigurationManager.ConnectionStrings[@"Data Source= (localdb)\MSSQLLocalDB;Initial 
     Catalog=BookingSys.Models.BookingSysDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;
    TrustServerCertificate=False; ApplicationIntent=ReadWrite;MultiSubnetFailover=False"].ConnectionString);

    to:

          SqlConnection con = new
    SqlConnection(ConfigurationManager.ConnectionStrings["yourConnectionStringName"].ConnectionString);

    or:

    SqlConnection con = new SqlConnection(@"Data Source= (localdb)\MSSQLLocalDB;Initial 
    Catalog=BookingSys.Models.BookingSysDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;
    TrustServerCertificate=False; ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 8:39 AM
  • User1571524970 posted

    edit: the below method works now, I had to remove the breaks in the connection string. thank you :)

    Thanks for the replies guys,

    Yuki I tried:

    SqlConnection con = new SqlConnection(@"Data Source= (localdb)\MSSQLLocalDB;Initial 
    Catalog=BookingSys.Models.BookingSysDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;
    TrustServerCertificate=False; ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

    And got the following error: 

    System.ArgumentException: 'Keyword not supported: 'initial
    catalog'.'

    d

    Tuesday, July 23, 2019 12:32 PM
  • User1520731567 posted

    Hi darego,

    According to your picture,I find you have a new line between "Initial" and "Catalog".

    Replace it with a space.It will be OK.

    Best Regards.

    Yuki Tao

    Wednesday, July 24, 2019 2:06 AM
  • User1571524970 posted

    I noticed that and corrected it, works fine now :) thanks Yuki

    Wednesday, July 24, 2019 12:53 PM