protected void filldropdownlist()
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
//for product dropdown
string productquery = "SELECT ProductId,ProductName FROM Product";
cmd = new SqlCommand(productquery, sqlcon);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "product");
DropDownProduct.DataSource = ds.Tables["product"];
DropDownProduct.DataTextField = "ProductName";
DropDownProduct.DataValueField = "ProductId";
DropDownProduct.DataBind();
//for supplier dropdownlist
string supplierquery = "SELECT SupplierId,CompanyName FROM Supplier";
cmd = new SqlCommand(supplierquery, sqlcon);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "supplier");
DropDownSupplier.DataSource = ds.Tables["supplier"];
DropDownSupplier.DataTextField = "CompanyName";
DropDownSupplier.DataValueField = "SupplierId";
DropDownSupplier.DataBind();
sqlcon.Close();
}
//Refined Supplier Data Bind
protected void SupplierBind(object sender, EventArgs e)
{
using (var conn = new MySqlConnection(strcon))
{
conn.Open();
string Query = "";
if ((DropDownCategory.SelectedIndex != 0 && DropDownSubCategory.SelectedIndex != 0) || DropDownSubCategory.SelectedIndex != 0)
{
Query = "SELECT DISTINCT Supplier.* FROM Supplier INNER JOIN SubCatSupplier ON Supplier.SupId = SubCatSupplier.SupId INNER JOIN SubCategory ON SubCatSupplier.SubCatId = SubCategory.SubCatId WHERE SubCatSupplier.SubCatId ='" + DropDownSubCategory.SelectedValue + "' AND SubCatSupplier.SupId = Supplier.SupId";
}
else if (DropDownCategory.SelectedIndex != 0)
{
Query = "SELECT DISTINCT Supplier.* FROM Supplier INNER JOIN SubCatSupplier ON Supplier.SupId = SubCatSupplier.SupId INNER JOIN SubCategory ON SubCatSupplier.SubCatId = SubCategory.SubCatId WHERE SubCatSupplier.SubCatId IN (SELECT SubCatId FROM SubCategory WHERE CatId = '" + DropDownCategory.SelectedValue + "') AND SubCatSupplier.SupId = Supplier.SupId";
}
else
{
Query = "SELECT DISTINCT Supplier.SupId, Supplier.SName FROM Supplier INNER JOIN SubCatSupplier ON Supplier.SupId = SubCatSupplier.SupId INNER JOIN SubCategory ON SubCatSupplier.SubCatId = SubCategory.SubCatId";
}
using (var cmd = new MySqlCommand(Query, conn))
{
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
DropDownSupplier.DataSource = reader;
DropDownSupplier.DataValueField = "SupId";
DropDownSupplier.DataTextField = "SName";
DropDownSupplier.DataBind();
DropDownSupplier.Items.Insert(0, new ListItem("--- Choose One ---", "NA"));
}
else
{
DropDownSupplier.Items.Clear();
DropDownSupplier.Items.Insert(0, new ListItem("--- No Suppliers ---", "NA"));
}
}
}
}
}