VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-Cart StoreFront Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 Product Sort Order
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

20 Posts

Posted - September 24 2007 :  15:16:26  Show Profile  Reply with Quote

I was going to add a custom field to sort my products but then I decided that there were already fields that I wasn't using such as product weight so I added a bunch of numbers in that field and changed xsortproducts to weight. This did not work, it caused a database error because weight isn't an item that is included in the SQL statement.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
/store/shop$db.asp, line 615

So my question is, Where is the SQL statement? How can I edit it so weight is something included in the Select statement?

Thanks for your help!

VP-ASP Super User

373 Posts

Posted - September 27 2007 :  08:18:45  Show Profile  Reply with Quote
if will help if you can tell us which page were you on when this error occured
Go to Top of Page

Starting Member

20 Posts

Posted - September 27 2007 :  08:25:16  Show Profile  Reply with Quote
I was on the homepage that grabs random products.
Go to Top of Page

Starting Member

20 Posts

Posted - September 27 2007 :  13:56:03  Show Profile  Reply with Quote
Come on guys, someone at VP-ASP must know where they put their own SQL statements.
Go to Top of Page


4282 Posts

Posted - September 28 2007 :  05:20:50  Show Profile  Visit support's Homepage  Reply with Quote
Hi there,

It may be best for us to have a look at your site directly to work out what the issue is.

I tried to replicate locally and was not able to so will need to have a look at your code directly.

You can post a ticket at:


VP-ASP Support
Go to Top of Page

Starting Member

2 Posts

Posted - August 02 2011 :  11:28:01  Show Profile  Reply with Quote
I've encountered this same issue.
I wanted to sort a group of products by "pother5" but when I refreshed the product page on the site after making the change to the configuration page I got the same error. What this says to me is that "pother5" has to be specifically stated in the SQL statement. Which means that using a "spare" field to do a sort isn't going to work.
Go to Top of Page

Senior Member

United Kingdom
1898 Posts

Posted - August 03 2011 :  01:17:45  Show Profile  Visit devshb's Homepage  Reply with Quote
This is usually just a temporary session issue and would be fine once your session expires.

The bit that sets the list of distinct product fields to select uses a session value (which in turn gets set from the xsortproducts config option), but the bit that sets the order-by list uses the xsortproducts config value directly.

The session value only gets set on a fresh session (ie it doesn't re-set it from the xsortproducts config option if your session is still active), so if your session is still active from before you changed the xsortproducts config option, then the query will fail.

But if your session was created after you made your xsortproducts config change then the query should work fine.

So, although it might be failing for you, for all your normal customers (apart from maybe a handful who are currently active) it'd work fine, and it'd also work fine for you once your session expires.

we had a similar issue when we first coded-up our byz116 (enhanced searching/paging) addon, but in the end the logic we used for our byz116 product queries ended up completely different to how vpasp would normally do order-by/select-distinct stuff anyway (because byz116 is a lot more dynamic), so we totally restructured how our byz116 stuff does queries compared to standard vpasp

Simon Barnaby
[email protected]
Follow us on Twitter: http://twitter.com/bigyellowzone
Web Design, Online Marketing and VPASP addons

Edited by - devshb on August 03 2011 01:23:09
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000