The text, ntext, and image data types cannot be used in an ORDER BY clause

[three_4me]

My thanks in advance for helping me answer this question, but I've been searching the net for a while and have yet to find the solution to this problem. I'm getting the following error on one of my pages ... "Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The text, ntext, and image data types cannot be used in an ORDER BY clause."

As I'm fairly new to working with SQL Server, I'm unsure as to how to rectify this problem. Can anyone help?

Thanks,
Dan

[raf]

i'm not an SQL server expers, so might be completelay wrong, buth it seems to me you've got two options :

- use another variable in your Order by clause (one that is of number or autonumber or date/time type or one of the other non text or image types)
- redefine the variabletype of the variable you currently want to use.

maybe that variable only contains numbers or so, buth if the variabletype isn't defined (standard = text), the sql will error.

it seems verry obvious to me why ordering on a text-variable isn't possible.

another sollution (i often use) is to register an ID in that variable, with an inner join to another table where you've got the text (label) for that id. (Like for instance : if you store messages in categorys, store a numer (categoryID) in the messages-table for each record and build a category table with for each category this ID and the lable for that category. You can then order the messages by category (in sql ordered by cateh-goryID but when displayed, 'ordered' by their label)

Hopes this makes sense to you.

[three_4me]

I probably should have been a little more clear in my initial post. I've got my tables all setup with primary key fields, which are defined as integers. I can ORDER BY on these fields with no problems, but this particular database is being read by my asp page and then written out to a treeview control. So, as records are added or deleted, I need it to be able to sort in alpha order by the name and this is the field the error is occurring on. Does that make any more sense? I wasn't sure if there was some sort of CONVERT statement or something like that I need to run on this field before doing the order by.

Thanks again,
Dan

[BigDaddy]

User a different data type. Char can be used to order it. Of course, your data might be too big for a char field...

[rcreyes]

You cannot sort on blobs including TEXT and NTEXT, One work around to this is to copy the first 255 or 1024 chars of a this column to a CHAR or VARCHAR, and sort by the first 255 or 1024 chars only, you most likely need to create a temporary table or add a column to the existing table ....

Hope this answer your question.

Thanks,
Ray

[three_4me]

Problem resolved, changed field to varchar (duh!), made proper length and everything is great now. Thanks for the help.

Dan