Order By Clause Error

[GeorgeBC]

Hi,

My database system updated from Access to MS SQL. But, i m receiving this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be used in an ORDER BY clause.
/files/sql/kategori.asp, line 89

My code first 100 lines:

<%
' ADO Constants - Don't Change Them
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const adUseClient = 3

Dim currentPage,sorgu

'---------------------------------------------------------------------------------------------

if request.querystring("durum")="" then
sorgu=" order by mydate desc,title asc"
end if

if request.querystring("durum")="program" then
sorgu=" order by title asc"
end if

if request.querystring("durum")="tarih" then
sorgu=" order by mydate desc,title asc"
end if

if request.querystring("durum")="dosya" then
sorgu=" order by btur desc, dosyaboyutu desc,title asc"
end if

if request.querystring("durum")="hit" then
sorgu=" order by hit desc, title asc"
end if

if request.querystring("durum") = "lisans" then
sorgu = "order by lisans asc, title asc"
end if

if request.querystring("durum") = "oy" then
sorgu = "order by pOy desc, title asc"
end if

Function tur(noo)
select case noo
case "1"
Response.Write "Freeware"
case "2"
Response.Write "Shareware"
case "3"
Response.Write "Demo"
end select
end function

If Len(Request.QueryString("currentPage")) = 0 Then
currentPage = 1
Else
currentPage = CInt(Request.QueryString("currentPage"))
End If

Dim recordsToShow
recordsToShow = 30

' Keyword/s to search
Dim strKeyword, strArray

strKeyword = Trim(Request.QueryString("lr"))
if len(strkeyword)<>0 then
strArray = split (strkeyword , " ")
end if

' Our Connection Object
Dim con
Set con = CreateObject("ADODB.Connection")
con.Open strDB

' Our Recordset Object
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.PageSize = recordsToShow
rs.CacheSize = recordsToShow

' Searching the records for the keywords entered
if isArray(strArray) then
Select Case UBound(strArray)
Case 0 rs.Open "select * from all_pages where active<>0 and altkategori_id = " &strKeyword & sorgu, con, adOpenForwardOnly, adLockReadOnly, adCmdText
Case 1 rs.Open "select * from all_pages where active<>0 and altkategori_id = '%" & strArray(0) & "%' and altkategori_id = '%" & strArray(1) & "%'" & sorgu, con
Case 2 rs.Open "select * from all_pages where active<>0 and altkategori_id = '%" & strArray(0) & "%' and altkategori_id = '%" & strArray(1) & "%' and altkategori_id = '%" & strArray(2) & "%'" & sorgu, con
Case Else rs.Open "select * from all_pages where active<>0 and altkategori_id = '%" & strArray(0) & "%' and altkategori_id = '%" & strArray(1) & "%' and altkategori_id = '%" & strArray(2) & "%'" & sorgu, con
End Select
else
rs.Open "select * from all_pages where active<>0 and altkategori_id = " &strKeyword & sorgu, con, adOpenForwardOnly, adLockReadOnly, adCmdText
end if ' If the returning recordset is not empty

%>

Line 89:
Case 0 rs.Open "select * from all_pages where active<>0 and altkategori_id = " &strKeyword & sorgu, con, adOpenForwardOnly, adLockReadOnly, adCmdText

This code was working fine MS Access DB system.
What can I do?

Thank you.

 

[SQLDenis]

you can't order by blobs

try order by convert(varchar(8000),ColumnNameHere)

>>My database system updated from Access to MS SQL

don't use text columns use varchar instead

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

Do you have Query Analyzer available? If so, run this...

[COLOR=blue]Select[/color] Column_Name, Data_Type
[COLOR=blue]From[/color]   Information_Schema.Columns
[COLOR=blue]Where[/color]  Table_Nmae = [COLOR=red]'all_pages'[/color]

Post the reply here. This should give us the information we need to help you better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

In Access, I believe the memo field was used if there were more than 255 characters inthe field. IN SQl Server , you can store up to 8000 in a varchar field but the upgrade process would make those fields text or ntext datatype by default. Look at the max number of characters inthe field and see if you can convert it to a varchar.

In fact if you let the upgrade wizard select the datatypes, you probably need to review all your datatypes, it often does not pick the best choices.

Questions about posting. See faq183-874