ASP.NET - Retrieving data from SQL gives an error - System.InvalidCastException:

Question

I'm trying to run my ASP.NET project but get a error - "System.InvalidCastException:"
I don't understand why, I checked all the types and all are equal to the SQL table, I also debuged to find where it falls.
In other older project it worked with same casting.

BLL.cs

   public static string ProductList()

        {
            DataTable productsTable = DAL.GetProducts();
            DataSet ds = new DataSet();
            ds.Merge(productsTable);

            List<Product> productsList = new List<Product>();

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count ; i++)
                {
                    productsList.Add(new Product((int)ds.Tables[0].Rows[i]["ProductID"], // Erorr starts here
                        (string)ds.Tables[0].Rows[i]["ProductName"],
                        (string)ds.Tables[0].Rows[i]["CategoryName"],
                        (float)ds.Tables[0].Rows[i]["Price"],
                        (int)ds.Tables[0].Rows[i]["Stock"],
                        (string)ds.Tables[0].Rows[i]["ProductDescription"],
                        (string)ds.Tables[0].Rows[i]["ProductOverview"],
                        (string)ds.Tables[0].Rows[i]["ProductImage"])); 
                }
            }

            return new JavaScriptSerializer().Serialize(productsList);
        }

When I Add Watch to productsList.Add(new Product((int)ds.Tables[0].Rows[i]["ProductID"]
This is what I get this -

Product error CS0119: 'Product' is a type, which is not valid in the given context

SQL

     CREATE TABLE [Products]
     (
        ProductID INT IDENTITY(1,1) PRIMARY KEY,
        ProductName NVARCHAR(60),
        CategoryName NVARCHAR(20),
        Price DECIMAL(4,2),
        Stock INT NULL DEFAULT NULL,
        ProductDescription NVARCHAR(75) NULL DEFAULT NULL,
        ProductOverview NVARCHAR(500) NULL DEFAULT NULL,
        ProductImage NVARCHAR(max) NULL DEFAULT NULL
     )

The whole error

System.InvalidCastException
Business_Logic_Layer.BLL.ProductList() ב- C:\Users\tn\Desktop\ShopWebSite1\Business_Logic_Layer\BLL.cs:46
WebService.ProductList() ב- C:\Users\tn\Desktop\ShopWebSite1\WebSite1\App_Code\WebService.cs:38

Answer 1

If you have time, you can debug or add to Watch window the expressions that probably cause the error. For example, you can write inside the loop:

 var p = (float)ds.Tables[0].Rows[i]["Price"];

If this line generates the exception, then try decimal instead of float.

Answer 2

you also have bug with:

(int)ds.Tables[0].Rows[i]["Stock"],

as Stock is nullable, it will fail if the column has a null value, which can not be cast to an int.