Unable to add attribute to item model. c# SQL Server

[PsychicTide]

Hey guys, having a bit of trouble with a customized eCommerce. The part I want to focus on is the adding of an item to the shopping cart (and the details that go along with each item). Everything works as is with the things I need commented...

initial call to add item to my cart model

<a href="/AddToCart.aspx?productID=<%# Eval("ItemID") %>">  
                    <span style="border:1px solid black;padding:2px;">
                        Add To Cart
                    </span>
                </a>

backend logic (called when addtocart[blank page with backend] loads)

public void AddToCart(int id)
        {
            // Retrieve the product from the database.           
            ShoppingCartId = GetCartId();

            var cartItem = _db.ShoppingCartItems.SingleOrDefault(
                c => c.CartId == ShoppingCartId
                && c.ProductId == id);

            if (cartItem == null)
            {
                // Create a new cart item if no cart item exists.                 
                cartItem = new CartItem
                {
                    ItemId = Guid.NewGuid().ToString(),
                    ProductId = id,
                    CartId = ShoppingCartId,
                   // ProductName = GetName(id),
                   // ProductPrice = GetPrice(id),
                    Quantity = 1,
                    DateCreated = DateTime.Now
                };

                _db.ShoppingCartItems.Add(cartItem);
            }
            else
            {
                // If the item does exist in the cart,                  
                // then add one to the quantity.                 
                cartItem.Quantity++;
            }
            _db.SaveChanges();
        }

CartItem model

public class CartItem
    {
        [Key]
        public string ItemId { get; set; }
        public string CartId { get; set; }
        public int Quantity { get; set; }
        public System.DateTime DateCreated { get; set; }
        public int ProductId { get; set; }
        //public string ProductName { get; set; }
        //public string ProductPrice { get; set; }
        public virtual Product Product { get; set; }
    }

As it shows currently here, it works. Adds a unique product item to the cart with ID retrieved from the DB, but When I uncomment either the Product name or ProductPrice attributes (and their corresponding functions/etc) I get an inner exception... something along the lines of invalid column name ProductPrice (even if I use hard-typed dummy data).

Let me know if i can clarify at all... any ideas are much appreciated!

[PsychicTide]

Should have meantioned, the error ocurs on these lines...

var cartItem = _db.ShoppingCartItems.SingleOrDefault(
                c => c.CartId == ShoppingCartId
                && c.ProductId == id);

[Traevel]

You have a CartItem object with a ProductId, a ProductName, a ProductPrice ..and a Product? Could it be that you are mixing your CartItem and Product entities? That you are looking for a ProductName in CartItem when CartItem only has a reference to ProductId in Product?

What would be the purpose of a ProductName and ProductPrice in a CartItem if CartItem already contains the entire Product. Or do you get the name/price from the Product object inside the CartItem with those two methods GetName(id) and GetPrice(id)?

[PsychicTide]

Should have clarified that. I've tried to both add these new attributes, and have also tried to create a whole product object, both seem to fail. GetName() and GetPrice() are my attempt to omit the product object and just go straight for the details I need from the DB, however, even if I put in dummy data instead of calling those functions, the same result happens (see below for error).

If I uncomment the lines above, it gives an error along the lines of "invalid column name ProductPrice/ProductName" and if I attempt to submit the name and price via a whole product object (dummy data for each attribute [strings]), it says something along the lines of "invalid object name dbo.Product" (the product object has attributes such as price,name, id, etc...)

[Traevel]

Are you using Entity Framework? It sounds like a database issue, not a code issue. Double check your names and objects. The variables in the objects need to have the same name as the database objects if you haven't remapped them.

It seems to me like it's looking for a column named ProductPrice in your database object Product. It could also be that EF renamed renamed/pluralized your tables, so that it's now named Products. EF will also create the object classes (they might not show up in your editor), so it could have connected different classes (with the same name perhaps) to your database then the ones you wrote yourself.

If you're not using Entity Framework it might be an idea to look at it.

[PsychicTide]

To be honest, I'm not 100% sure what EF is, although I've heard the acronym a lot throughout my current venture. The website I've attempted to customize is loosly based off of the wingtip toys example here http://www.asp.net/web-forms/tutorials/aspnet-45/getting-started-with-aspnet-45-web-forms/shopping-cart (pretty sure that is the correct link anyway).

I would think the database (local MS SQL server) would not be a factor as the attributes are mainly not pulled from there as of yet (product ID, yes). if I were to submit the following code, the same result would apply ("invalid column name 'ProductPrice'")

if (cartItem == null)
            {
                // Create a new cart item if no cart item exists.                 
                cartItem = new CartItem
                {
                    ItemId = Guid.NewGuid().ToString(),
                    ProductId = id,
                    CartId = ShoppingCartId,
                   // ProductName = GetName(id),

                    ProductPrice = "10.00", /// dummy data string (also uncomment the ProductPrice in the CartItem model so it builds)

                    Quantity = 1,
                    DateCreated = DateTime.Now
                };
                _db.ShoppingCartItems.Add(cartItem);
            }

[Traevel]

EF is Entity Framework, and on top of that tutorial page it does say:

Code features in this tutorial: Entity Framework Code First

In the part where you create a new cart item, did the following give you problems?

Product = _db.Products.SingleOrDefault(
           p => p.ProductID == id),

Because that should add the product as a reference to the cartitem, you would then be able to refer to product price/name as cartItem.Product.Price or cartItem.Product.ProductPrice depending on whatever it's called in the class (and/or database).

The code in the tutorial is based on earlier tutorials according to this:

Earlier in this tutorial series, you added pages and code to view product data from a database.

So the code is written based on the assumption that you are getting your information from a database. In other words, it might be trying to find your new columns in a database that was created in an earlier tutorial, since you are using the context class _db. Even if the data is from dummy info, it is still trying to save the information into a database via the context. If the class CartItem you made does not match the CartItem in the database (where it's trying to store your cartitem in the cartitem(s) table) it will throw an error, since you're trying to store data it can't fit in anywhere. The product has a price and name, but cartitem does not, it only has a product.

Again, this is based on what I saw at the tutorial page you linked, I don't know in what ways you followed it or not, since you say it's loosely based upon it. For instance, in the third part of the tutorial the Domain Model was made. In the Product class I see there, the price column is defined as follows:

[Display(Name = "Price")]
    public double? UnitPrice { get; set; }

So it's not named ProductPrice, in the original code.

It's hard to tell what's going wrong without seeing the other parts of your code, mainly the context class, product class and database. If you read the third tutorial it will tell you a little about Entity Framework and how to set it up.

Also, in part 6 it says:

When the AddToCart.aspx page is loaded, the product ID is retrieved from the query string. Next, an instance of the shopping cart class is created and used to call the AddToCart method that you added earlier in this tutorial. The AddToCart method, contained in the ShoppingCartActions.cs file, includes the logic to add the selected product to the shopping cart or increment the product quantity of the selected product. If the product hasn’t been added to the shopping cart, the product is added to the CartItem table of the database.

So there should be a cartitem table in the database (whether added by hand or by the initializer class (see part 3), or by some other part of your code) where it will try to store CartItem. Considering the error I would try to find out if the table matched the class and move on from there.

[PsychicTide]

Excellent, finally someone who knows what they're talking about! Self-initializing SQL instances, EF, and code first are new concepts to me, I'm used to open-source languages/frameworks (PHP, MySQL, etc...).

My product.cs model is the same as the one posted in the toturial (minus the Category and CategoryID attributes).

At the lines of code you spoke of, this is probably where I initially started to deviate from the tutorial...

Product = _db.Products.SingleOrDefault(
           p => p.ProductID == id),

this is making a call to the ProductDatabaseInitializer.cs, which I skipped completely in an attempt to just query the DB for such information. In the toturial it has a hard-coded list of product items.

EG (product id 1):

private static List<Product> GetProducts()
    {
      var products = new List<Product> {
                new Product
                {
                    ProductID = 1,
                    ProductName = "Convertible Car",
                    Description = "This convertible car is fast! The engine is powered by a neutrino based battery (not included)." + 
                                  "Power it up and let it go!", 
                    ImagePath="carconvert.png",
                    UnitPrice = 22.50,
                    CategoryID = 1
               },
                ...

so when it is called, the information is already there (no need to wait for a query result), this is what made me think to skip that section and attempt to replace it with one of my own retreival methods (codebehind query) since there are thousands of products and it would be crazy to hard-code them.

Product.cs

namespace Project1.Models
{
    public class Product
    {
        [ScaffoldColumn(false)]
        public int ProductID { get; set; }

        [Required, StringLength(100), Display(Name = "Name")]
        public string ProductName { get; set; }

        [Required, StringLength(10000), Display(Name = "Product Description"), DataType(DataType.MultilineText)]
        public string Description { get; set; }

        public string ImagePath { get; set; }

        [Display(Name = "Price")]
        public double? UnitPrice { get; set; }
    }
}

ItemContect.cs

namespace Project1.Models
{
    public class ItemContext : DbContext
    {
        public ItemContext()
            : base("Project1")
    {
    }
        public DbSet<Category> Categories { get; set; }
        public DbSet<Transactions> TransId { get; set; }
        public DbSet<CartItem> ShoppingCartItems { get; set; }
        public DbSet<Order> Orders { get; set; }
        public DbSet<OrderDetail> OrderDetails { get; set; }
    }
}

I'm not sure how to show you the temporarily created DB.
What if I were to create a function that would query all of the Product fields individually and build a product object out of that (one of my first failed attempts I think)?

Product = function(id), //return a newly created object of product

Hopefully the variables I show here match up, I've attempted to change most specific names in an attempt to make this code generic.

Any ideas are much appreciated!

[Traevel]

Well the whole purpose of Entity Framework is to stop writing data-access code, so I wouldn't do that. Basically the only queries you want to write are things like:

List<Products> cheapProducts = repository.Products.Where(p => p.Price < 50).ToList();

And not queries to get the entire dataset, that's what EF is for. I'm not familiar with such initializers myself, and as you yourself mentioned, what's the point of having it when you're hardcoding products. However, I don't think that your information at runtime is coming from those hardcoded products. The initializer is run once (not just on every start, but once entirely), it adds the hardcoded information to the database, and from that moment on it uses the database. There's a big warning somewhere in the third tutorial about making changes to the initializer and how they will probably not work after running the first time. The reason they are using it I imagine is because of the code first approach. In other words, Entity Framework creates the database based on the models. The other way around is DB first; EF creates the model based on the database.

In your ItemContext you have a DbSet<CartItem> ShoppingCartItems (a database set of cartitems). When you call the Add() method on that, you're basically telling EF to add that CartItem to your database. It doesn't do so right away, you have to call SaveChanges() on your context first, which you do at the bottom _db.SaveChanges();. However, it might never even get there because EF notices a difference between your model (CartItem.cs) and your database (which it created using the initializer).

Can you look in the database yourself? Check if there actually is a CartItem table in there. And if there is, does it have all the columns that you use in your CartItem.cs?

You mention getting products via queries, but the problem was with CartItems I thought. Is there something not working with products? The code provided by the tutorial, and Entity Framework, work so well because you can reference (and get all the values from) Product via CartItem by only using

    Product = _db.Products.SingleOrDefault(
    p => p.ProductID == id),

in your CartItem creation and then calling it by using item.Product.Price or something similar. You say that gives you errors, which ones exactly?

But before you make further changes, it might be smart to make a backup copy of your entire project.

[PsychicTide]

Thank you for your quick responses, Traevel. You have already been very helpful with my basic understanding of ASP.NET structure. I've continually looked at the product.cs and ProductDatabaseInitializer.cs as static/hard-coded elements, so I've chosen to omit both of these models as of now (I know that may be wrong, but I'm trying to go with what I know).

I've looked into your ideas of productContext, EF, and actually created temporary databases. What I'm now attempting to do is recreate the temporarily created database schema for CartItem(s) to have the correctly added new columns using Code First Migrations (update temp table columns).

I've uncommented my ProductPrice and ProductName attributes on both the CartItem and AddToCart function. I then attempted to Migrate via Visual Studio menus...

1) I went to Tools –> Library Package Manager –> Package Manager Console and typed 'Install-Package EntityFramework' in the command prompt shown.
2) Then ran the command 'Enable-Migrations -ContextTypeName Project1.Models.ItemContext –EnableAutomaticMigrations' to initiate auto-migrations for the model
3) I then setup an initial migration context 'add-migration initial' , which built fine.
4) I then attempted to update the temporary databases to the new CF models 'update-database -Verbose (-verbose is so I can see each line as it is processed)'

I came up with this error while attempting to do so, "Cannot insert the value NULL into column 'ProductId', table 'Project1.dbo.CartItems'; column does not allow nulls. UPDATE fails.
The statement has been terminated."

These are the two lines the compiler stopped on...

ALTER TABLE [dbo].[CartItems] ADD CONSTRAINT [DF_dbo.CartItems_ProductId] DEFAULT 0 FOR [ProductId]
ALTER TABLE [dbo].[CartItems] ALTER COLUMN [ProductId] [int] NOT NULL

These two lines represent my attempt to recreate the database, based on my newly created model. I'm attempting to alter the ProductId column (primary key) to be auto-incrementing, not null, and have a default value of 0 (default value is an attempt to fix the error)

Any idea why I can't make the ProductId column be unique and auto-increment on the new migration? I can't actually see the database table in SQL server manager because I'm using SQL Server 2008R2 Express which apparently has a bug with this.

I may be completely in the wrong direction here, but as long as I can understand the wheel that's already created for queries, it should work imo. I can provide more of any code/compiler info you may need to help understand what is going on.

Any ideas are much appreciated!

[PsychicTide]

So I've run through the migration scenario a few times now and tried multiple things. Most recently, I've deleted my migrations folder entirely and tried to rebuild it from the models I have. the DB migration is successful, but when I try to add an item to the cart I get the error: InnerException...

"{"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_dbo.CartItems_dbo.Products_ProductId\". The conflict occurred in database \"Project1\", table \"dbo.Products\", column 'ProductID'.\r\nThe statement has been terminated."}"

From what I've read so far this may mean it's trying to add an item to cartItems which has a Foreign Key linked to Products table, however, it needs to be inserted into the products table first? I'm not sure I understand how to fix this without removing the FK (which apparently I need). The structure was all created automatically by Visual Studio via "add-migration NAME", then "update-database" cmds in the Package Manager Console.

...Any ideas or direction someone can point me in perhaps? Even just an idea will help.

EDIT: My last post I said I omitted the Product.cs model, however, this is not true. I simply use it to store the product ID alone and am trying to use the cartItem model to hold the price, name, etc... (trying not to break what was already working).

[PsychicTide]

Figured it out. I erased the category.cs, products.cs, and my EDMX database models entirely then rebuilt the code-first migration using only the shopping cart and order models. After a while of converting object types to match between models and enabling the cartItem model to handle everything product-related, success!

Thank you very much for the help Traevel! My hair also thanks you, as I didn't have to tear it all out :) .