recordset.sort invalid row handle

Question

I have a MySQL stored procedure generating a resultset. The query uses GROUP_CONCAT to select rows as columns.

SQL Code snippet

SELECT CONCAT(
 ' SELECT MDE_ID, MDE_CreatedOn, MDE_LastEditedOn '

,  GROUP_CONCAT(CONCAT(
   ' ,t', MDF_ID, '.MDEMDF_Value AS `', REPLACE(MDF_Label, '`', '``'), '`'
   ) SEPARATOR ' ')

Following, I want to sort the recordset on a column.

SQL = "CALL moduleentry_select(" & prjid & ",0,26,0)"
set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.LockType = 3
rs.Open SQL, Conn
rs.Sort = "volgnummer"

I have verified and the column is in the recordset.

I get the following error:

Microsoft Cursor Engine error '80040e04'
Row handle is invalid.
default.asp, line 176

Line 176 is rs.Sort = "volgnummer"

edit

The column 'Volgnummer' doesn't actually exist in the database. It is selected dynamically via a pivoting principle, using GROUP_CONCAT. Referring to this SA answer: joined table rows into columns with column titles from original rows

Sorry, meant to write "**How** have you verified the column exists in the recordset", blonde moment :/ — Shadow The GPT Wizard, Apr 24 '13 at 11:52
Ahh, no problem :) I ran the stored procedure call in MySQL Workbench and saw the column in the resultset. — Steven Ryssaert, Apr 24 '13 at 11:55
Oh, that's far from meaning the column exists in the **classic ASP recordset** then. If you just remove the `rs.Sort` line, what do you get? I suspect you'll get empty recordset or unexpected columns. — Shadow The GPT Wizard, Apr 24 '13 at 11:56
It works fine without the sort, **and** it works fine when I sort on column `MDE_ID` which wasn't created with `GROUP_CONCAT`, **and** I can print the value in the recordset by `<%=rs("volgnummer")%>`. — Steven Ryssaert, Apr 24 '13 at 11:58
Hmm... last one before I'm out of ideas: what is the type of "volgnummer" column? Is it different from the type of "MDE_ID"? — Shadow The GPT Wizard, Apr 24 '13 at 12:00
The resultset was created using the answer in this question. http://stackoverflow.com/questions/10677700/joined-table-rows-into-columns-with-column-titles-from-original-rows . I believe it is either a `MEDIUMTEXT`, or `BLOB` — Steven Ryssaert, Apr 24 '13 at 12:03
So maybe ADO can't handle that type when sorting... can you convert the type to something more common? — Shadow The GPT Wizard, Apr 24 '13 at 12:10
I am not sure I will be able to convert the column, but I can try to convert or cast the datatype in the query. I'll keep in touch when changed. Thx — Steven Ryssaert, Apr 24 '13 at 12:16
It is a `MEDIUMTEXT` column, but I cannot change the type, nor cast it to a different type as the data length requires `MEDIUMTEXT`. — Steven Ryssaert, Apr 26 '13 at 17:42
If you store the value of `<%=rs("volgnummer")%>` in local variable, then call `rs.MoveNext` then compare the values does it work? — Shadow The GPT Wizard, Apr 26 '13 at 19:47

recordset.sort invalid row handle

0 Answers0