VB6/ASP Get ordinals with field names from ADODB RecordSet

[neualex]

Hi guys,

I use GetRows to simulate the disconnected recordset, and loop the array to display the values.
However, I use hardcode values (1, 2,...) for the array indices to refer to the fields.

Is it possible to get the column numbers passing the field name as shown in the code below with asp.net?
...I use classic ASP.

Dim First_Name__Ordinal as Int32 = reader.GetOrdinal("FirstName")
Dim Last_Name__Ordinal as Int32 = reader.GetOrdinal("LastName")
Dim Hire_Date__Ordinal as Int32 = reader.GetOrdinal("HireDate")
Dim EmployeeID__Ordinal as Int32 = reader.GetOrdinal("EmployeeID")

Thanks for your help,
neualex

 

[SBerthold]

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

 

[neualex]

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

 

[HarleyQuinn]

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.

MsgBox GetColumnOrdinal(rs, "field2")

Here's the function:

Function GetColumnOrdinal(recset As Recordset, fieldname As String) As Integer

Dim intCounter As Integer
Dim blnFoundFlag As Boolean

intCounter = 0
blnFoundFlag = False

For intCounter = 0 To recset.Fields.Count - 1
   If recset.Fields(intCounter).Name = fieldname Then
      blnFoundFlag = True
      Exit For
   End If
Next intCounter

GetColumnOrdinal = IIf(blnFoundFlag, intCounter, -1)

End 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.

 

[neualex]

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

 

[SBerthold]

>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.

 

[harebrain]

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

 

[neualex]

>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

 

[SBerthold]

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:

Public Function GetTblColOrdinal(oConn As ADODB.Connection, sTable As String, sField As String) As Integer
    With oConn.OpenSchema(adSchemaColumns, Array(Empty, Empty, sTable, sField))
        If Not (.EOF And .BOF) Then GetTblColOrdinal = .Fields("ORDINAL_POSITION").Value
    End With
End Function