ADO recordset sort, causing EOF 1

[XgrinderX]

Hello! I have the following code:

set rsMsgs = server.CreateObject("adodb.recordset")
with rsMsgs.Fields
.Append "iMessageID", adInteger
.Append "iMessageIconID", adInteger
.Append "dtSent", adDBTimeStamp
.Append "iFromID", adInteger
.Append "tiSentArchive", adTinyInt
.Append "sFromToName", adLongVarChar,65538
.Append "sSubject", adVarChar,150
.Append "tiAttach", adTinyInt
.Append "tiUrgent", adTinyInt
.Append "tiAlert", adTinyInt
end with
rsMsgs.Open()
for preIndex = iRecFirst to iRecLast
rsMsgs.AddNew
rsMsgs("iMessageID") = arPreSort(0,preIndex)
rsMsgs("iMessageIconID") = arPreSort(1,preIndex)
rsMsgs("dtSent") = arPreSort(2,preIndex)
rsMsgs("iFromID") = arPreSort(3,preIndex)
rsMsgs("tiSentArchive") = arPreSort(4,preIndex)
rsMsgs("sFromToName") = arPreSort(5,preIndex)
rsMsgs("sSubject") = arPreSort(9,preIndex)
rsMsgs("tiAttach") = arPreSort(10,preIndex)
rsMsgs("tiUrgent") = arPreSort(11,preIndex)
rsMsgs("tiAlert") = arPreSort(12,preIndex)
rsMsgs.Update
next
rsMsgs.Sort = forderBy

I can sort on sSubject and dtSent without any problems. When I do a sort on the sFromToName field the recordset comes back as EOF. Does this have something to do with it being a LongVarChar that is so huge? How can I fix this?

 

[Tarwn]

Try doing a .MoveFirst after your sort. I've noticed some odd behavior with the recordset in the past, occasionally when you re-order your data the pointer gets stuck sitting at EOF or partially down the recordset and a simple MoveFirst will get it back to the top of the newly ordered RS.

 

[XgrinderX]

I tried that and I get the EOF error on the MoveFirst line :-(

It's a very weird situation. If I do a RecordCount before and after the Sort, it gives me the correct number (271). But I have tried MoveFirst and GetRows and both of those throw the EOF error. I have done a Response.Write of the EOF and BOF value after the Sort and they both come back true.

The only thing I can think of is that the fact that I am sorting on such a large text field is somehow causing the recordset to get messed up.

Any other thoughts?

 

[tsuji]

> .Append "sFromToName", adLongVarChar,65538

There is a upper limited on the length. 66538 is beyond the limit. Change it to a number less than 60852 inclusive in this case.
[tt] .Append "sFromToName", adLongVarChar,[blue]60852 '60852 or less, but not bigger[/blue][/tt]
It would then be fine.

 

[XgrinderX]

AH! So I had my size incorrect. Interesting now it gives me this message:

Relate, Compute By, and Sort operations cannot be performed on column(s) whose defined size is unknown or too large.

So I came up with a workaround - that field was holding a list of names that could be very long. In the display I decided to only show some of the names and then just append "..." to the end to indicate there are more names. So I converted that field to a standard adVarChar and everything works fine now.

I wonder what "too large" means in that message I got? I am curious to know if there would be a work around for it.

Thanks for your help!

-Greg