VB6/ASP Get ordinals with field names from ADODB RecordSet

If you already have the recordset, then pass the index to the Fields collection and get the field's name:
rs.Fields(1).Name

Or if you are using an Alias field name, get the field's base name using
rs.Fields(1).Properties("COLUMNBASENAME").Value

Thanks for your reply.

I am not trying to get the field name or value -I know how to do this.

What I am trying to get is the column number based on the field name.
ie. If you SQL query is:

SELECT Name, Last, DOB FROM tblCustomers

Then, you dump the results to an array.

myArray = RS.GetRows()

To access the values from the array you have to hard code the column numbers:

Dim name, last, dob
name = myArray(0, 0) 'column 0 is name
last = myArray(1, 0) 'column 1 is last
dob = myArray(2, 0) 'column 2 is dob

I want to avoid this hard coding by initializing some column variables:

Dim NAME_COLUMN, LAST_COLUMN, DOB_COLUMN
'getColumnOrdinal would let us pass a field name to get the ordinal/column number
NAME_COLUMN = RS.getColumnOrdinal("name")
LAST_COLUMN = RS.getColumnOrdinal("last")
DOB_COLUMN = RS.getColumnOrdinal("dob")

Then, accessing the values this way:

Dim name, last, dob
name = myArray(NAME_COLUMN, 0)
last = myArray(LAST_COLUMN, 0)
dob = myArray(DOB_COLUMN, 0)

Is this possible on classic ASP or VB6?

PS. I make up the getColumnOrdinal method since I don't know how to accomplish this.

Thanks,
neualex

Go on then, I'm bored so here's a function that will do this. Please bear in mind this is only a quickly knocked up function so error handling is almost non existant (i.e. not production code). It will return the ordinal if found and if not will return -1. It should give you some idea's of a direction to go in this (as it's not quite as simple as .Net's .GetOrdinal method). You have to pass in your Recordset object (as I said, it's quickly knocked up ) and then the field name e.g. Here's the function: There will be other ways of doing this but as I said, this should give you some idea's.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

Thanks for the reply.

I wanted to avoid using an external function, I thought there was some hidden RecordSet property-method to accomplish this.
It seems there is not.

...neualex

>I thought there was some hidden RecordSet property-method to accomplish this.

neualex, yes, I mis-read your question, or more so, I read into it.

There is no OrdinalPosition field property under ADODB, as there was under DAO, to get the Ordinal position of a field in a recordset. There is only an ADODB dynamic method to get the ordinal position of a field with in it's underlying table.

Why don't you just create an Enum for your field names?

>There is only an ADODB dynamic method to get the ordinal position of a field with in it's underlying table?

Could you share which one is it?

Thanks,
...neualex

This returns the Ordinal Position of a field in a table (not a field in a recordset - for this use something along the lines of what HarleyQuinn posted), based on the information returned by the provider. It usually has a base of 1, and not 0, so you may need to subtract 1 from it: