Getting SQL Server error when adding data to database using ASP.NET Web API

Question

I am trying to add some data to a ASP.NET Web API from the same solution, but somehow I am getting this error from SQL Server.

This is my context

public class SampleCtxt: DbContext
{
    public DbSet<TodoItem> TodoItems { get; set; }

    public SampleCtxt(DbContextOptions<SampleCtxt> options)
        : base(options)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.\SQLEXPRESS;Database=APITESTDB; Initial Catalog=APITestDb; Trusted_Connection=True;");
    }
}

Configure services method from API

public void ConfigureServices(IServiceCollection services)
{
        services.AddDbContext<SampleCtxt>(opt =>
            Catalog=master;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False; Database = APITESTDB;"));
            opt.UseSqlServer(
                Configuration
                .GetConnectionString("DefaultConnection")));
        services.AddControllers();
}

Connection string from json

"ConnectionStrings": {
    "DefaultConnection": "Server=.\\SQLEXPRESS;Database=APITESTDB; Initial Catalog=APITestDb Trusted_Connection=True;"
},

Adding data from another console project

static void Main(string[] args)
{
    using (SampleCtxt ctxt = new SampleCtxt(
        new Microsoft.EntityFrameworkCore.DbContextOptionsBuilder<SampleCtxt>().Options))
        {
            TodoItem todoItem = new TodoItem() { Name = "qualquer" };
            ctxt.TodoItems.Add(todoItem);
            ctxt.SaveChanges(); 
        }
}

Everything seems fine but I am getting this error:

Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Are you able to connect to the server from SQL management studio? — Chetan
– Chetan, Commented Mar 21, 2020 at 23:22
Your connection string is missing a semicolon after APITestDb @"Server=.\SQLEXPRESS;Database=APITESTDB; Initial Catalog=APITestDb; Trusted_Connection=True;" — user12493039
– user12493039, Commented Mar 22, 2020 at 11:38
@ChetanRanpariya No, I am only using the SQL Object Explorer on Visual Studio — NetoTI
– NetoTI, Commented Mar 22, 2020 at 11:49
Are you running your application locally and could you try connecting using SQL Server Management Studio like suggested by @ChetanRanpariya — user12493039
– user12493039, Commented Mar 22, 2020 at 11:57

NetoTI · Accepted Answer · 2020-03-23 12:41:13Z

1

Its seens that the ConnectionString was wrong and the instantiation of the class context, I solved the problem by adding a parameterless constructor and by correcting the OnConfiguring Method

public class SampleCtxt: DbContext
{
    public DbSet<TodoItem> TodoItems { get; set; }

    public SampleCtxt()
    {

    }

    public SampleCtxt(DbContextOptions<SampleCtxt> options)
        : base(options)
    {

    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=APITESTDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;");
    }
}

answered Mar 23, 2020 at 12:41

NetoTI

1791 gold badge2 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting SQL Server error when adding data to database using ASP.NET Web API

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related