Sorting data from a recordset by using Sort property

>>I get the following error

What error ?

why not pass the column to sort on into the COM object, and include it the SQL as an ORDER BY clause.

I've done this many times without data type specific errors.

Whats the error by the way...?

Sicknote

I think you may have to use quotes around for char columns

you would have to use quotes whatever... its a column name, and not the value from the column.

Sounds like the recordset doesn't allow the sort clause. Is it a forward only recordset?

I use this, it works;

' Open the recordset for All data
    m_rsAll.Open strSqlAll, m_cn, 3, 1, 1
' Sort the grid on Index
    m_rsAll.Sort = "index Asc"

Its a static cursor. Its working fine for non character columns as I said.

Asc is by default. Ading it didn't help.

Did you add quotes around the field ? Lets see the code

jitganguly,
this is the code

set rs = comSvc.getallrows("xyz")

if request("sort")=null then
    rs.Sort = "ticket"
else
    rs.Sort = Request("sort")
end if

Maybe this;

sortName = Asc(request("column_name"))" 'convert the CHAR to ASCII
rs.Sort = "sortName Desc"

Curious though... what are you storing in the CHAR field?

Yes, whats there  in ticket field. Is that avalid field name ?
What if you just try to use a SQL interactively

Select * from yourtable order by ticket
Do you see any error.

Your code looks fine, may be its data or wrong field name

jitganguly,
this is the code

set rs = comSvc.getallrows("xyz")

if request("sort")=null then
    rs.Sort = "ticket"
else
    rs.Sort = Request("sort")
end if

Don't reload, rfresh this page. Read my and mgfranz's  comment and then reply.We are here to help you out

sorry for the doublepost.

ticket is a column name and of long type. Its a default value for sort order. it works fine. And the data I am getting in the character field is usual data like a company name OR currency code(USD, JPY, EUR). So I dont understand why this is causing trouble. Its not data (i am sure about it because another application is sorting the same data via a Stored proc but I dont have that choice)

Are you able to run a SORT in the SQL Query Engine in SQL?  I am not sure you can even sort by CHAR type...

I am confused. If ticket is a long type then why r u getting character field ?

Sorry, Query Analyzer...

Here is a thought, what character set are you using in your page?  maybe it can't support the CHAR value...

As I said earlier, I am getting the sort results fine when I sort Non-Char fields . Ticket is a default column and is of Number type. That is working fine.
When the user clicks on a column like Currency (which has a value of "USD", "EUR" or "JPY", then the code will be:
   rs.Sort = "Currency"

this is when I get the above error.

May be currency is reserved word. Try enclosign withing braces


  rs.Sort = "[Currency]"

Wait a minute... if the field is just containing a test string, why are you storing it as a CHAR? "USD, EUR and JPY" are just text strings.

Perhaps you should change this:

if request("sort")=null then
   rs.Sort = "ticket"
else
   rs.Sort = Request("sort")
end if

to

if IsNull(request("sort")) then
   rs.Sort = "ticket"
else
   rs.Sort = Request("sort")
end if

Anthony

mgfranz,

>> Wait a minute... if the field is just containing a test string, why are you storing it as a CHAR? "USD, EUR and JPY" are just text strings. <<
Why not?  Char is just a fixed length character field. What other data type would you suggest?

>>Are you able to run a SORT in the SQL Query Engine in SQL?  I am not sure you can even sort by CHAR type... <<
Sure you can.  Again why not?

Anthony

tilakv,

>>When the user clicks on a column like Currency (which has a value of "USD", "EUR" or "JPY", then the code will be:
  rs.Sort = "Currency"<<

In order to use the Sort Property, aside from the fact the provider and recordset must suport it (check out the Supports method), you must set the Index first.

Try posting all the relevant code and it may be more obvious.

Anthony

I thought it is a simple question but obviously it isn't.
Adding [ ] to the sort is actually creating an error.

The code is as simple as I wrote above. Also IsNull is not the problem as it is always passing the value whether the field type is of CHAR or NUMBER. when the user clicks on a button in one frame, it passes value to the other frame where I am retrieving the recordset and adding filter based on the button value.

>>In order to use the Sort Property, ... you must set the Index first.<<
Ignore this comment.

>>Adding [ ] to the sort is actually creating an error.<<
Besides, "Currency" is not a keyword.

Anthony

yes acperkins,
keyword is not the issue i think. I only gave an example as "currency". The actual columns are like curr1, curr2, account etc.. this is bothering me a great bit and cant get any documentation specifying of this problem either.

if IsNull(request("sort")) then
  rs.Sort = "ticket"
else
  rs.Sort = Request("sort")
end if

might be

if IsNull(request("sort")) then
  rs.Sort = "ticket"
else
  rs.Sort = chr(34)&Request("sort")&chr(34)
end if

Although this doesn't answer the question as to why the sort property cannot be applied to columns of a certain datatype, have a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A2FA.asp

In the page there is the comment:
"Note In many cases, it's faster to open a new Recordset object by using an SQL statement that includes the sorting criteria."

Is there a reason for wishing to specifically use the sort proerty?

Sicknote

sicknote,
I am receiveing the recordset from a com object which is used by other apps too. so, i cant ask them to change it for me.. u know.. too much headache going around for it.

Louis,
the cursor is a a client side cursor. and the column is not a reserved word either as mentioned earlier.
its a varchar type and the values are less than 30 char.
Its not working on 3 chars either..for example Currency codes USD, EUR..

i am going nuts trying to figure this out. the database is Sybase.

If it is any consolation, a search on Google for "Sort order cannot be applied." proves your not the only one with this problem:
http://textcontrol.com/tx/newsgroups/microsoft_public_vb_database/2001/11/23/0003.htm 
I do not believe the solution suggested is correct.

Anthony

I'm stumped... If you do find a solution please let us know what resolved this.

Hi everybody,

I think I figured out why it is not working. Actually, the recordset was created by Appending fields to it and the columns were defined as the data type "adBSTR". I requested to change the data type to "adVARCHAR" in the DLL and it worked like a charm.

Sorry if I have misled any of you but it was completely unintentional. I was under the impression too that the data type was varchar as per what I was told.

Now I need to find out how to lock this thread.

I suggest you accept Louis comment of 10/20/2002 07:53AM PST, he seems to be the only one to mention that the type may be wrong, even though you stated on a few occations that it was a CHAR type...

Thank's a lot for the misinformation...

:-)
And I suggest you to split points

sorry.. I was told by the people working on COm object that it was character type. when i went to double check, i found this out. But this forum sure helped as it led me to the suspicion on data type.

thanks everybody.. i will accept Louis's answer..
i have no idea what these point mean though.. havent read the rules properly yet. trying to find out..

Thanks again

I found two things in this solution that helped me:
Changing the data type for the column to adVarChar  from adString
Enclosing the column name in brackets because it contained a space "[Login Name]"

I can now sort my disconnected record set.  Thanks.

sgdought
Scott Doughty