// This method populates the combo box with available Products
private void LoadProductCB()
{
int supId = SuppliersDB.getSupplierId(suppName);
List <Products> temp = new List <Products>(); // Temp variable to store in order to convert string to int
List <Products> productList = new List <Products>();
// Grabs all product ID that doesn't have the particular supplier ID in Product_Supplier
temp = Products_SuppliersDB.GetNotInProductsID(supId);
try
{
// Converts the Product IDs to Product Names
foreach (Products products in temp)
{
products.ProdName = ProductsDB.getProdName(products.ProductId);
productList.Add(products);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
// Populate the combo box
try
{
cbProductName.DataSource = productList;
cbProductName.DisplayMember = "ProdName";
cbProductName.ValueMember = "ProductId";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
//This method is used to get the Products for a specific supplier. It returns a list of products.
public static List <Products> GetProdForSup(int supplierId)
{
List <Products> productsList = new List <Products>(); //create a new productsList
Products products = null; //create a null product
SqlConnection con = TravelExpertsDB.GetConnection(); //create a connection
//Create a query which selects a productId from Products_Suppliers table where the supplierId is given
string selectQuery = "SELECT ProductId " +
"FROM Products_Suppliers " +
"WHERE SupplierId = @SupplierId";
SqlCommand selectCommand = new SqlCommand(selectQuery, con); //create a select command using SqlCommand
selectCommand.Parameters.AddWithValue("@SupplierId", supplierId); //Bind the supplierId to the @supplierId
try
{
con.Open(); //Open the connection
SqlDataReader reader = selectCommand.ExecuteReader(); //Execute the query and store the result in reader
while (reader.Read()) //read the products if it exists
{
products = new Products(); //create new product
products.ProductId = (int)reader["ProductId"]; //add productId to product
productsList.Add(products); //add product to product list
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();//close connection
}
//Right now all the products only have productIds and no product name. Run through each one and add product Name
foreach (Products prod in productsList)
{
//Use the ProductsDB.getProdName to get the prod name using productId
prod.ProdName = ProductsDB.getProdName(prod.ProductId);
}
return(productsList);//Return the product list
}
