Thread: Filter Orders with Manufacturer Name

in orders.aspx.cs

Code:

private void GenerateReport()
        {
            string sql = "select OrderNumber, OrderDate, IsNew from orders   with (NOLOCK)  where " + WhereClause() + DateClause() + " order by " + OrderByFields;

can i replace it with

HTML Code:

            string sql = "select OrderNumber, OrderDate, IsNew from Orders INNER JOIN" +
                      "Orders_ShoppingCart ON Orders.OrderNumber = Orders_ShoppingCart.OrderNumber INNER JOIN" +
                      "Product ON Orders_ShoppingCart.ProductID = Product.ProductID INNER JOIN" +
                      "ProductManufacturer ON Orders_ShoppingCart.ProductID = ProductManufacturer.ProductID INNER JOIN" +
                      "Manufacturer ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID"
                         + WhereClause() + DateClause() + " order by " + OrderByFields;

will this work?

Have you tried it?

Originally Posted by esedirect

Have you tried it?

it gives Server Error in '/' Application.

Code:

Incorrect syntax near the keyword 'ON'.

In SSMS it gives this error:

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'OrderNumber'.

So you need to fully qualify the first returned column, like this:

Code:

select Orders.OrderNumber, OrderDate, IsNew 
from Orders INNER JOIN
Orders_ShoppingCart ON Orders.OrderNumber = Orders_ShoppingCart.OrderNumber INNER JOIN
Product ON Orders_ShoppingCart.ProductID = Product.ProductID INNER JOIN
ProductManufacturer ON Orders_ShoppingCart.ProductID = ProductManufacturer.ProductID INNER JOIN
Manufacturer ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID

it still give me the error

HTML Code:

            string sql = "select Orders.OrderNumber, OrderDate, IsNew" +
                        "from Orders INNER JOIN" +
                        "Orders_ShoppingCart ON Orders.OrderNumber = Orders_ShoppingCart.OrderNumber INNER JOIN" +
                        "Product ON Orders_ShoppingCart.ProductID = Product.ProductID INNER JOIN" +
                        "ProductManufacturer ON Orders_ShoppingCart.ProductID = ProductManufacturer.ProductID INNER JOIN" +
                        "Manufacturer ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID" +
                        WhereClause() + DateClause() + " order by " + OrderByFields;

gives this error.
Incorrect syntax near the keyword 'Inner Join'.

You've got no spaces between the end of one line of your statement and the beginning of the next line. So your statement is being interpreted like this:

Code:

            string sql = "select Orders.OrderNumber, OrderDate, IsNewfrom Orders INNER JOINOrders_ShoppingCart ON Orders.OrderNumber = Orders_ShoppingCart.OrderNumber INNER JOINProduct ON Orders_ShoppingCart.ProductID = Product.ProductID INNER JOINProductManufacturer ON Orders_ShoppingCart.ProductID = ProductManufacturer.ProductID INNER JOINManufacturer ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID" +
                        WhereClause() + DateClause() + " order by " + OrderByFields;

See, no space in IsNewfrom or INNER JOINOrders_ShoppingCart, etc