// 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 }