logo-dw

Go Back   Dreamweaver Club Forums > Dreamweaver forums > Dreamweaver General
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 06-02-2009, 06:01 PM   #1
rico1931
 
Join Date: Jun 2008
Location: Buffalo
Posts: 198
Default Dynamic ORDER BY

Hello folks,

I'm trying to do a dynamic ORDER BY using DW and I keep coming up with this error
"
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. "

So I did some google searching and I found that I would have to use a CASE statement to achieve what I really want. Now I'm no expert in SQL but if I have to go write it I will I was just wondering if anyone else out there found out an easier way to do it using DW recordset option?
rico1931 is offline   Reply With Quote
Old 06-03-2009, 01:35 AM   #2
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,211
Default

what do you want to order by? 1 doesnt make much sense, order by column name ascending or descending, if you use a value the closest would be (in DW) order by entered value
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 06-03-2009, 01:38 AM   #3
rico1931
 
Join Date: Jun 2008
Location: Buffalo
Posts: 198
Default

right but I want it to be dynamic and take a col name but DW doesn't allow you to do ORDER BY 'parm from URL' in the recordset option
rico1931 is offline   Reply With Quote
Old 06-03-2009, 01:48 AM   #4
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,211
Default

no , what does parm from URL' mean? im not clear what you want. sorry its early here
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 06-03-2009, 02:00 AM   #5
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,211
Default

if you meant pasing a value from a url ( im waking up a bit ) you need to pass the value from a link such as actionpage.php?id=1
then in your record set use order by id (url)
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 06-03-2009, 02:02 AM   #6
rico1931
 
Join Date: Jun 2008
Location: Buffalo
Posts: 198
Default

ok so you have a SQL statement
[code]
SELECT USER_ID, USER_Fname
FROM table A
ORDER BY sort
[code]

Now 'sort' is a URL parameter so when a user goes to mypage.com
and click on a header like Fname it will pass the parm mypage.com?sort=Fname and by default its always ASC

haha and Good Morning!
rico1931 is offline   Reply With Quote
Old 06-03-2009, 02:03 AM   #7
rico1931
 
Join Date: Jun 2008
Location: Buffalo
Posts: 198
Default

Exactly! that's what I mean... Wow got a cup of coffee that fast huh? haha but I get this SQL error
Code:
 The SELECT item identified by the ORDER BY 
number 1 contains a variable as part of the expression
 identifying a column position. Variables are only allowed 
when ordering by an expression referencing a column name. "
rico1931 is offline   Reply With Quote
Old 06-03-2009, 02:05 AM   #8
rico1931
 
Join Date: Jun 2008
Location: Buffalo
Posts: 198
Default

wow 9am over there.. Must be beautiful there too!
rico1931 is offline   Reply With Quote
Old 06-03-2009, 02:10 AM   #9
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,211
Default

ok in your statement you select USER_ID, USER_Fname but you are asking it to order by sort.
the value is read by $_GET['sort']; which will return the value that is passed by mypage.com?sort=Fname in this case Fname
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 06-03-2009, 01:55 PM   #10
rico1931
 
Join Date: Jun 2008
Location: Buffalo
Posts: 198
Default

right thats what I have in my SQL but since i'm using ASP...don't ask why... its Request.QueryString("sort") but is this right?

Code:
SELECT *
FROM dbo.USERS_CURRENT
ORDER BY sort
Something above is wrong because I keep getting this error

Code:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 
 [Microsoft][SQL Server Native Client 10.0][SQL Server]The SELECT item identified by the ORDER BY 

number 1 contains a variable as part of the expression identifying a column position. 

Variables are only allowed when ordering by an expression referencing a column name.

Also it tells me my error is on this line

Code:
Set rsCONTROL = rsCONTROL_cmd.Execute



So it def doesn't like that 'sort' parameter in there for some reason
rico1931 is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 06:30 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com