Sorting ADO recordset on unknown fieldname...?

[cliffhanger9]

Hi all,

I have an ADO recordset that is populated from a stored procedure that uses several functions to concatenate one of the fields of the recordset so as such there is not a direct field name to reference in the sort (i.e. <%
rs.Sort="CompanyName,ContactName"
%> )

I know that there is no field name because I applied the following to the recordset:

Response.Write("<u>Fields in rs:</u> <br />")
For i = 0 To rs.Fields.Count - 1
   Response.Write( "Field " & i & "  = " & rs.Fields(i).Name & "<br />")
Next

...and it returned the following:

Fields in rs:
Field 0 = person_id
Field 1 =

(there are only two fields in this recordset)

I am able to write the data that I need because I know the field's index within the recordset (1), so I print it via:

<%= rs.Fields.Item(1) %>

My question is, is there a way to sort using this index as well? or is there another way to sort a recordset on a specific field without a field name?

cheers and happy holidays

 

[DotNetGnat]

you can do something like this:

ORDER BY 1

which means order by the first field in the select list...

example:

SELECT field1, field2, field3 from mytable ORDER BY 2

-DNG

 

[cliffhanger9]

thanks for the response - i was more interested in an ADO solution if possible as the database function is called by alot of different places - thats the beauty of these things - most of which are not sorted the way this particular instance needs to be so I was looking for a way to merely sort the recordset.

Anyone else..?

 

[onpnt]

hmm..how about the .Sort property

although without benchmarks and tests on the fly I would say I agree SQL would be best performance here. The tools for the needs type instance ;-)

 

[cliffhanger9]

Not to come across as a jackass here but...

do you guys read my post at all?

I know about the sort property - infact the example on that link of yours is the same one i used in the first paragraph of my initial post.

That sort property as far as I can tell requires a field name. there is not a field name. so I cant imagine how this could work.

I know that SQL could do it. but also as mentioend above, this data is being built dynamically from a userdefined function in the database from the stored procedure.

The beauty of these procedures is that you build one and then store them and then reuse them. Setting the Order By in the SQL would make this procedure quite specific to only this instance. Which is why I want to do it on the page via ADO.

Maybe its not possible to do this. If not, then I guess theres not much to do about it. But please, do not ignore my post, post things that I obviously already have knowledge of and then offer a solution to a problem that I dont have.

Thanks for youre time

 

[Sheco]

Hmmm, this is an interesting problem.

 

[vongrunt]

<blindshot>
.Name is read/write property, right?
</blindshot>

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Um... not. At least not for ADODB.Field in opened recordset.

On the second thought... why re-inventing the wheel? Make stored procedure always return named columns (AS blah) and voila .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cliffhanger9]

BINGO - I was just suggested the same thing in the MSDN Newsgroup for ADO

i ll try giving it a column name alias

 

[onpnt]

I guess years of seeing the same thread titles kind of made me use bad form in not reading the post completeing and I apologize for not seeing the .sort property mentioned.


Although...on this

Setting the Order By in the SQL would make this procedure quite specific to only this instance

I have a hard time seeing the logic in that. whatever the SP is doing and however you are building the column to return dynamically in the script (SP) I'm having a real hard time understanding why you can't apply that declaration in the SP to a ORDER BY in that very SP? I don't see how that makes the dynamic SP an more specific other then using the value you are returning from it in a different place on a different method.


Maybe the logic here is to complex for me though or its the eggnog

 

[cliffhanger9]

onpnt - you certainly can add an order by clause to the stored procedure. However the convenience of having SQL stored in a reusable procedure is that you can call it from many different places - code reuse. So since this is the only instance that I want to have the data sorted this way - I'd prefer to keep the result set as flexible as possible by not specifically defining an order.

Giving the column name an alias looks like it will be a better solution for any instance so in this case I have no problem altering the stored proc

Thanks again