Exception User-Unhandled System.Data.SqlClient.SqlException: Invalid Object name

Question

I and V E R Y new to learning ASP.NET MVC core, and I have hit a problem which is vexing me (and I hope someone can point out where I'm going wrong).

I have following a course on Puralsight (https://app.pluralsight.com/library/courses/aspdotnetcore-web-application-building/table-of-contents), and have got to Module 6, section 7 (Creating a Simple View Component). It shows you how to add a nav bar a shopping cart (link item counter) onto every page. When I cut'n'paste the code snippet into the corresponding pages, then launch IIS express to view the website, the website fails with this message in the browser :

    HTTP Error 502.3 - Bad Gateway
The specified CGI application encountered an error and the server terminated the process.
Most likely causes:
The CGI application did not return a valid set of HTTP errors.
A server acting as a proxy or gateway was unable to process the request due to an error in a parent gateway.
Things you can try:
Use DebugDiag to troubleshoot the CGI application.
Determine if a proxy or gateway is responsible for this error.
Detailed Error Information:
Module
    AspNetCoreModule
Notification
    ExecuteRequestHandler
Handler
    aspNetCore
Error Code
    0x80072ee2
Requested URL
    http://localhost:64923
Physical Path
    c:\users\MYDIRECTORY\visual studio 2017\Projects\BethanysPieShop\BethanysPieShop
Logon Method
    Anonymous
Logon User
    Anonymous
Request Tracing Directory

Visual Studio Community 2017 highlights this code (specifically, within the curly braces) within the file ShoppingCart.cs

        public List<ShoppingCartItem> GetShoppingCartItems()
    {
        return ShoppingCartItems ??
               (ShoppingCartItems =
                   _appDbContext.ShoppingCartItems.Where(c => c.ShoppingCartId == ShoppingCartId)
                       .Include(s => s.Pie)
                       .ToList());
    }

with the error message

Exception User-Unhandled
System.Data.SqlClient.SqlException: Invalid Object name 'ShoppingCartItems'.'

I have managed to locate the line of code that , when commented out, allows the site to load (minus the shopping cart details). It is in the _Layout.chtml file, within the SHARED folder :-

@await Component.InvokeAsync('ShoppingCartSummary')

Commenting out this line allows the site to load, but that's only because it not calling the Shopping cart (so, akin to saying you Car is fixed, as long as you don't use it).

I have a ShoppingCartSummary.cs file with the line

    var items = _shoppingCart.GetShoppingCartItems();

which I have replaced with

var items = new List<ShoppingCartItem>() { new ShoppingCartItem(), new ShoppingCartItem() };

so the cart has dummy entries, but I still get the same error (System.Data.SqlClient.SqlException: Invalid Object name 'ShoppingCartItems'.') when launching IISExpress.

Is anybody able to point out what Is missed/done wrong ?

Thanks in advance

your execption is saying the object ShoppingCartItems doesn't exist in your db — jamiedanq
– jamiedanq, Commented Jun 12, 2017 at 10:34
I've run Add-Migration AddShoppingCartItem then Update-Database and I can see a new .cs file in the Migrations folder called 20170612093119_AddShoppingCartItem.cs (along with 20170612030702_initial.cs). With the @await Component.InvokeAsync('ShoppingCartSummary') code comment out, the website launches fine, including reference to tables reference in the AppDbContext file (which includes the line public DbSet<ShoppingCartItem> ShoppingCartItems { get; set; } , so I am not sure why (when the data for the others (PIE and CATEGORIES) is access/listed. — Gerald Oakham
– Gerald Oakham, Commented Jun 12, 2017 at 10:58
Using SQL Server Object Explorer , I can drill down though LocalDB\Databases\ProjectName\Tables and see dbo.Pies and dbo.Categories, but no ShoppingCartItems. So, does this mean my AppDbContext is not functioning correctly ? — Gerald Oakham
– Gerald Oakham, Commented Jun 12, 2017 at 11:45
I think I have found the error,which in turn causes the OP message.When I run Update-Database instead on Done I see System.Data.SqlClient.SqlException: There is already an object named 'Categories' in the database. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, I'm guessing that the update fails because of the There is already an object named 'Categories' in the database message....if I'm correct, how do I rectify this ? — Gerald Oakham
– Gerald Oakham, Commented Jun 12, 2017 at 12:10

Gerald Oakham · Accepted Answer · 2017-06-13 12:13:31Z

1

OK, I've got this sorted now, so posting this up for anyone else with similar issues.

There is already an object named 'Categories' in the database message

is a red herring. What fixed it was, Dropping the whole database (via SQL Server Object Explorer, right mouse clicking on the Database and selecting Delete)... granted, not the ideal solution for some poeple, then, in Solutions explorer , remove everything under the Migrations folder (again, right mouse click, delete). Close VS. Open VS. Open Sql Server Object Explorer. Open Package Manager, then run

Add-Migration initial

then

Update-Database

You can then see the database and tales being crated in the SQL Server object Manager.... and the missing table is created.

I am assuming there as an initial issue with the _MigrationHistory file.

answered Jun 13, 2017 at 12:13

Gerald Oakham

17313 bronze badges

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

Collectives™ on Stack Overflow

Exception User-Unhandled System.Data.SqlClient.SqlException: Invalid Object name

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