Author |
Topic |
|
PabloHoney
Starting Member
Canada
20 Posts |
Posted - September 24 2007 : 15:16:26
|
Hi,
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!
|
|
Kidd
VP-CART Super User
Australia
373 Posts |
Posted - September 27 2007 : 08:18:45
|
if will help if you can tell us which page were you on when this error occured |
|
|
PabloHoney
Starting Member
Canada
20 Posts |
Posted - September 27 2007 : 08:25:16
|
I was on the homepage that grabs random products. |
|
|
PabloHoney
Starting Member
Canada
20 Posts |
Posted - September 27 2007 : 13:56:03
|
Come on guys, someone at VP-ASP must know where they put their own SQL statements. |
|
|
support
Administrator
4679 Posts |
Posted - September 28 2007 : 05:20:50
|
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: https://www.vpasp.com/virtprog/helpdesk/
Thanks Cam
VP-ASP Support |
|
|
eHobin
Starting Member
USA
2 Posts |
Posted - August 02 2011 : 11:28:01
|
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. |
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - August 03 2011 : 01:17:45
|
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 Developer [email protected] www.BigYellowZone.com www.BigYellowKey.com 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 |
|
|
|
Topic |
|