Thread: Adding a filter to admin Orders.aspx

Hi we have many filters in admin/Orders.aspx

can we have one more filter for Manufacturers


I thought some thing like this

If i add

Code:

<td align="left" valign="top">
                                                    <asp:DropDownList ID="ddManufacturer" runat="Server"></asp:DropDownList>
                                                </td>

in orders.aspx

and in Orders.aspx.cs

Code:

 protected void loadDD()
        {
           ddManufacturer.Items.Clear();
            
            ddManufacturer.Items.Add(new ListItem(" - All Manufacturers -", "0"));
            
        //Manufacturers
            Helper = new EntityHelper(EntityDefinitions.LookupSpecs("Manufacturer"));
            al = Helper.GetEntityArrayList(0, String.Empty, 0, cust.LocaleSetting, false);
            foreach (ListItemClass li in al)
            {
                ddManufacturer.Items.Add(new ListItem(li.Item, li.Value.ToString()));
            }
        }

and

Code:

 if (!ddManufacturer.SelectedValue.Equals("0"))
            {
                //Section selected
                fromM += ", Manufacturer M, ProductManufacturer PM";
                whereM += " AND M.ManufacturerID=" + ddManufacturer.SelectedValue + " AND M.ManufacturerID=PM.ManufacturerID AND PM.ProductID=P.ProductID";
                orderM += " M.Name ASC, ";
            }

this may work.

but i am not cent percent sure about it.

can anyone guide if it is right, or
at-least help me if there is nay other way to get Manufacturers as a filter in admin Orders.aspx

thanks for your time.

Orders.aspx
================================================== =========
I have added

Code:

<asp:dropdownlist id="ddManufacturer" runat="server" Width="150px" datatextfield="Manufacturer" datavaluefield="Manufacturer"></asp:dropdownlist>

Orders.aspx.cs
================================================== =========
I have Added

Step One____________________________

Code:

 ddCouponCode.SelectedIndex = 0;
            ddShippingState.SelectedIndex = 0;
// My Work 1 line			
			ddManufacturer.SelectedIndex = 0;

Step Two____________________________

Code:

 if (ddManufacturer.SelectedItem != null)
            {
                if (ddManufacturer.SelectedValue != "-" && ddManufacturer.SelectedItem.Text.Length != 0)
                {
                    result += String.Format(sQuery, "Manufacturer", DB.SQuote(ddManufacturer.SelectedValue));
                }
            }

Step Three____________________________

Code:

protected DataSet dsCouponCode = null;
        protected DataSet dsState = null;
//My Work		
		protected DataSet dsManufacturer = null;

Step Four____________________________

Code:

using (SqlConnection dbconn = DB.dbConn())
                {
                    dbconn.Open();
                    using (IDataReader rs = DB.GetRS("select Name from Manufacturer   with (NOLOCK)  order by Name", dbconn))
                    {
                        ddManufacturer.DataValueField = "Name";
                        ddManufacturer.DataTextField = "Name";
                        ddManufacturer.DataSource = rs;
                        ddManufacturer.DataBind();
                        ListItem item = new ListItem("-", "-");
                        ddManufacturer.Items.Insert(0, item);
                    }
                }

It looks like Correct for me.

Please help me If i am doing it right.


thanks for your time

That looked good to me. The next step you'll do (in case you haven't) is, modify the WhereClause() method in Order.aspx.cs and make sure the manufacturer dropdown box is filtered when the 'Submit' button is pressed.

Originally Posted by AspDotNetStorefront Staff - Alfred

That looked good to me. The next step you'll do (in case you haven't) is, modify the WhereClause() method in Order.aspx.cs and make sure the manufacturer dropdown box is filtered when the 'Submit' button is pressed.

Hi Alfred, as you mentioned
i have added below Code in public string WhereClause()

Code:

 if (ddManufacturer.SelectedItem != null)
            {
                if (ddManufacturer.SelectedValue != "0" && ddManufacturer.SelectedItem.Text.Length != 0)
                {
                    result += String.Format(sQuery, "ManufacturerID", ddManufacturer.SelectedValue);
                }
            }

Not sure if this is sufficient.

I see the Manufacturers in Drop Down

as you mentioned it is not filtering when i click submit.

If you don't mind can you please help me with the WhereClause() Method for Manufacturers

please take a look at changes i made.

once again thank you very much for your time.

Sure. From 'Step Four', it looks like you are missing the ManufacturerID in the select clause, that's why this line (see below) doesn't returned anything.

Code:

if (ddManufacturer.SelectedItem != null)
            {
                if (ddManufacturer.SelectedValue != "0" && ddManufacturer.SelectedItem.Text.Length != 0)
                {
                    result += String.Format(sQuery, "ManufacturerID", ddManufacturer.SelectedValue);
                }
            }

Try adding the ManufacturerID there and will see:

i.e.

Code:

 using (IDataReader rs = DB.GetRS("select ManufacturerID, Name from Manufacturer   with (NOLOCK)  order by Name", dbconn))

No use Alfred,

i explaining my changes again here. so that it will be clear to understand.

Stepone

Code:

protected DataSet dsState = null;
//My Work		
		protected DataSet dsManufacturer = null;

Step Two

Code:

using (SqlConnection dbconn = DB.dbConn())
                {
                    dbconn.Open();
                    using (IDataReader rs = DB.GetRS("select ManufacturerID,Name from Manufacturer   with (NOLOCK)  order by displayorder,name", dbconn))
					
                    {
                        ddManufacturer.DataValueField = "ManufacturerID";
                        ddManufacturer.DataTextField = "Name";
                        ddManufacturer.DataSource = rs;
                        ddManufacturer.DataBind();
                        ListItem item = new ListItem("-", "-");
                        ddManufacturer.Items.Insert(0, item);
                    }
                }

Step Three

Code:

 ddShippingState.SelectedIndex = 0;
// My Work 1 line			
			ddManufacturer.SelectedIndex = 0;

Step Four

Code:

  if (ddManufacturer.SelectedItem != null)
            {
                if (ddManufacturer.SelectedValue != "-" && ddManufacturer.SelectedItem.Text.Length != 0)
                {
                    result += String.Format(sQuery, "ManufacturerID", DB.SQuote(ddManufacturer.SelectedValue));
                }
            }

i can see the Manufacturers Drop Down in Orders.aspx but when i submit it. it gives pagenotfound error.

May be this will help you understand the situation clearly.

once again thanks a lot for your effort.

Send me your order.aspx and order.aspx.cs file directly to my email (alfred@aspdotnetstorefront.com) and I'll help you determine the problem.

Originally Posted by AspDotNetStorefront Staff - Alfred

Send me your order.aspx and order.aspx.cs file directly to my email (alfred@aspdotnetstorefront.com) and I'll help you determine the problem.

Thanks Alfred, I sent you email.

Hi alfread hope you got my email

thanks

...checking my SPAM Folder, yes I did. I'll get back to you shortly.

When I copied your order.aspx(cs) file into my local site and generate order report, I got 'Invalid Column ManufacturerID' error. I dug the code and it turned out there was really no manufacturerID field in the orders table.

What you can try is to create a subquery in the Whereclause() method if the manufacturer dropdown box is not empty.

Something like:

Code:

 if (ddManufacturer.SelectedItem != null)
            {
                if (ddManufacturer.SelectedValue != "-" && ddManufacturer.SelectedItem.Text.Length != 0)
                {
                   // result += String.Format(sQuery, "ManufacturerID", DB.SQuote(ddManufacturer.SelectedValue));
                    result += String.Format(" and OrderNumber =(select top 1 os.ProductID from Orders_ShoppingCart os where os.productid IN (SELECT pm.manufacturerid from productmanufacturer pm where pm.ManufacturerID={0})))",ddManufacturer.SelectedValue);

                }
            }

I tried your query it didn't work.

HTML Code:

result += String.Format(" and OrderNumber =(select top 1 os.ProductID from Orders_ShoppingCart os where os.productid IN (SELECT pm.Productid from productmanufacturer pm where pm.ManufacturerID={0})))",ddManufacturer.SelectedValue);

when i run this SQL Query It works fine

HTML Code:

select top 1 os.ProductID 
from Orders_ShoppingCart os 
where os.productid IN (SELECT pm.productid 
						from productmanufacturer pm 
						where pm.ManufacturerID='2')

But when i place the same query in Sting.Format
it is not working through error.