Reputation: 95
I have this SQL query which work when I run it in SQL Management Studio.
select Products.ProductName, ProductTypes.ProductType, count(SaleID) numSales
from Products
join ProductTypes on ( ProductTypes.ProductTypeID= Products.ProductTypeID)
join Sales on ( Sales.ProductID = Products.ProductID)
group by Products.ProductName, ProductTypes.ProductType
It returns this table.
Am am integrating this in with Visual Studio Data Grid View and am struggling to get the numSales column to work, right now it just shows 0 for everything like this.
Here is my code for populating the table
private void DisplayDashboard()
{
string numSalesQuery = "select Products.ProductName, ProductTypes.ProductType, count(SaleID) numSales " +
"from Products join ProductTypes on (ProductTypes.ProductTypeID = Products.ProductTypeID)" +
"join Sales on (Sales.ProductID = Products.ProductID)" +
"group by Products.ProductName, ProductTypes.ProductType";
List<_Dashboard> dashList = new List<_Dashboard>();
try
{
// Automatically open and close the connection
using (var conn = ConnectionManager.DatabaseConnection())
using (var cmd = new SqlCommand(numSalesQuery, conn))
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
//Define the list items
var dashboard = new _Dashboard(
rdr["ProductName"].ToString(),
rdr["ProductType"].ToString(),
int.Parse(rdr["numSales"].ToString()));
dashList.Add(dashboard);
}
dgvDashboard.DataSource = dashList;
}
}
catch (Exception ex)
{
MessageBox.Show("Unsuccessful" + ex);
}
}
Thanks for any help regarding the issue.
Edit: Here is the _Dashboard Class
namespace Acme_Project.Business_Logic_Layer
{
public class _Dashboard
{
//Declare properties of a Customer
public String ProductName { get; set; }
public string ProductType { get; set; }
public int NumSales { get; set; }
//Declaring Default Constructor
public _Dashboard() { }
//Parameterised Constructor
public _Dashboard(string productname, string productype, int numsales)
{
ProductName = productname;
ProductType = productype;
NumSales = NumSales;
}
}
}
Upvotes: 0
Views: 96
Reputation: 621
Set NumSales = NumSales
to NumSales = numsales
inside your parameterised constructor
Upvotes: 1