Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

SQL Help

i am trying to get records from Orders.ShipVia (Northwind database). i have a query in access that does exactly what i need, it returns the name of the shipper. in my aspx page, the exact same sql statement returns the shipper ID number instead of the name. any ideas on why its doing this? in the database you choose which shipper you want for an order from a dropdownbox, i assume it has something to do with this.

thanks in advance

Here is the entire sql statement:
SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, [Order Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount, CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS Revenue FROM Products INNER JOIN (Customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID) ON Customers.CustomerID=Orders.CustomerID) ON Products.ProductID=[Order Details].ProductID WHERE (((Customers.CompanyName)=@strCustomers) AND ((Products.ProductName)=@strProducts))

Comments

  • Geoff-SGeoff-S Posts: 82Member
    : i am trying to get records from Orders.ShipVia (Northwind database). i have a query in access that does exactly what i need, it returns the name of the shipper. in my aspx page, the exact same sql statement returns the shipper ID number instead of the name. any ideas on why its doing this? in the database you choose which shipper you want for an order from a dropdownbox, i assume it has something to do with this.
    :
    : thanks in advance
    :
    : Here is the entire sql statement:
    : SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, [Order Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount, CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS Revenue FROM Products INNER JOIN (Customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID) ON Customers.CustomerID=Orders.CustomerID) ON Products.ProductID=[Order Details].ProductID WHERE (((Customers.CompanyName)=@strCustomers) AND ((Products.ProductName)=@strProducts))
    :
    :
    Hi,
    Yup - you are quite correct. It is all to do with the drop-down list in Access. This list held details of all the shippers and their ID's. Only the ID is stored in the Orders Table, but when you bind the drop-down list to that field it displays the entry in the list which has a matching ID - but you don't see that as the first column of that list has its width set to Zero, so you just see the shippers name.
    The easiest way for you to overcome this in your ASPX page would be to put another INNER JOIN into the SQL Statement to Join ShipperID to ShipVia, and then include the ShippersName in the SELECT part of the Statement. Then you can put the Name into the ShipVia instead of the ID.
    :-)

Sign In or Register to comment.