locked
ASP.Net MVC 4 - Connection Issue SQL Server Database RRS feed

  • Question

  • User1984873286 posted

    I am trying to connect to a SQL Server database from my ASP.Net MVC 4 application.

    The connection string I have is:

    <add name="EmployeeContext" 
         connectionString="server=DEVELOPER-PC; database=Sample; integrated security=SSPI" 
         providerName="System.Data.SqlClient"/>

    The error message I get is this:

    The underlying provider failed on Open.

    To try and fix this issue added this piece of code into my connection string in VB user id=js;password=jess1; but to no avail.

    Another site said to add my main computer user to SQL and this didnt work either.

    My model class:

    public class EmployeeContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }
    }

    My controller:

    public ActionResult Details(int id)
    {
        EmployeeContext employeeContext = new EmployeeContext();
        Employee employee = employeeContext.Employees.Single(x => x.EmployeeId == id);
    
        return View(employee);
    }

    EDIT

    I also have this model in my application

    namespace Department_Employee.Models
    {
        [Table("tblEmployee")]
        public class Employee
        {
            public int EmployeeId { get; set; }
            public string Name { get; set; }
            public string Gender { get; set; }
            public string City { get; set; }
        }
    }

    Can anybody see where I have gone wrong?

    Saturday, November 3, 2018 11:55 AM

Answers

  • User1120430333 posted

    The underlying provider failed on Open.

    The error message is a generic error message. In order to get the real error message, you need a try/catch that exposes the inner.exception.message.

    Why didn't the exception popup box show for unhandled exception where you could have done a View Detail so that you could see the inner.exception.message?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 3, 2018 12:14 PM
  • User-271186128 posted

    Hi sherloc987,

    Agree with DA924, there are multiple reason causing the "The underlying provider failed on Open" error, if you could post the detail error message, it might be easier for us to help you solve the problem.

    Besides, I suggest you could check this blog and this thread, you could try to generate Context and Entity Classes from an Existing Database (using the new Connection to select your database). Then, try to change your code as below:

    using (EmployeeContext employeeContext = new EmployeeContext())
    {
        employeeContext.Connection.Open();
        // the rest
    }

    and 

    public class EmployeeContext : DbContext
    {
        public EmployeeContext()
                : base("name=EmployeeContext")
        {
        }
        public DbSet<Employee> Employees { get; set; }
    }

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 5, 2018 2:48 AM

All replies

  • User1120430333 posted

    The underlying provider failed on Open.

    The error message is a generic error message. In order to get the real error message, you need a try/catch that exposes the inner.exception.message.

    Why didn't the exception popup box show for unhandled exception where you could have done a View Detail so that you could see the inner.exception.message?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 3, 2018 12:14 PM
  • User-271186128 posted

    Hi sherloc987,

    Agree with DA924, there are multiple reason causing the "The underlying provider failed on Open" error, if you could post the detail error message, it might be easier for us to help you solve the problem.

    Besides, I suggest you could check this blog and this thread, you could try to generate Context and Entity Classes from an Existing Database (using the new Connection to select your database). Then, try to change your code as below:

    using (EmployeeContext employeeContext = new EmployeeContext())
    {
        employeeContext.Connection.Open();
        // the rest
    }

    and 

    public class EmployeeContext : DbContext
    {
        public EmployeeContext()
                : base("name=EmployeeContext")
        {
        }
        public DbSet<Employee> Employees { get; set; }
    }

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 5, 2018 2:48 AM