// This method populates the combo box with available Suppliers
private void LoadSupplierCB()
{
int prodId = ProductsDB.getProductId(prodName);
List <Suppliers> temp = new List <Suppliers>(); // Temp variable to store in order to convert string to int
List <Suppliers> supplierList = new List <Suppliers>();
// Grabs all supplier ID that doesn't have the particular supplier ID in Product_Supplier
temp = Products_SuppliersDB.GetNotInSuppliersID(prodId);
try
{
foreach (Suppliers supplier in temp)
{
// Converts the Supplier IDs to Supplier Names
supplier.SupName = SuppliersDB.getSupName(supplier.SupplierId);
supplierList.Add(supplier);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
// Populate the combo box
try
{
cbSupplierName.DataSource = supplierList;
cbSupplierName.DisplayMember = "SupName";
cbSupplierName.ValueMember = "SupplierId";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
//This method is used to get the suppliers for a certain productId. It returns a List of suppliers
public static List <Suppliers> GetSupForProd(int productId)
{
List <Suppliers> supplierList = new List <Suppliers>(); //create a list of suppliers
Suppliers supplier = null; //create a supplier and set it to null
SqlConnection con = TravelExpertsDB.GetConnection(); //Create a connection
//Create a query that selects all the supplier ID's from the product suppliers where the product ID is given
string selectQuery = "SELECT SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductId = @ProductId";
SqlCommand selectCommand = new SqlCommand(selectQuery, con); //Create a selectCommand using SqlCommand
selectCommand.Parameters.AddWithValue("@ProductId", productId); //Bind the given productId to @ProductId
try
{
con.Open(); //Open connection
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read()) //read the supplierId's if they exist
{
supplier = new Suppliers(); //create a new supplier
supplier.SupplierId = (int)reader["SupplierId"]; //Add the Id to the supplier
supplierList.Add(supplier); //Add the supplier to the list of suppliers
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();//Close the connection
}
//We now have all the id's for the suppliers but do not have there supplier names
//Go through each of the suppliers in the supplier list and find there names
foreach (Suppliers sup in supplierList)
{
//use getSupName which is from the SuppliersDB to find the names of the suppliers and add them to there
//corresponding suppliers.
sup.SupName = SuppliersDB.getSupName(sup.SupplierId);
}
return(supplierList);//return the supplier list
}
