ADO Field Order

[Evertjan.]

Douglas wrote on 05 apr 2004 in microsoft.public.inetserver.asp.db:

> DAO has an OrdinalPosition property for the Fields collection, but how
> about ADO ?

A 4e generation relational database
should not have an intrinsic field order.


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows]

Douglas wrote:

> Sorry for the repost, I have had problems with my ISP(s) news
> servers... I'm on my third news server and hopefully things have
> improved :P... grr
>
> anyways...
>
> Gday,
>
> I need to retrieve a list of FieldNames from a table.
>
> I've done this successfully using ADOX.
>
> The question is, I want to arrange the fields in the same order that
> they appear in the MDB, not alphabetically as ADOX returns.
>

As Evertjan says, there is no need to ever worry about the order of the
fields in a relational database. The only reason you would ever need to
worry about it is if you were planning to use selstar (select *) in
production code, which is a bad practice. Always name the fields that your
query will be returning. This will guarantee that the fields will be
returned in the order that you expect.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Douglas]

> A 4e generation relational database

> should not have an intrinsic field order.

Nevertheless, the fields are 'relatively' organised in the table design
view. The aim here is to reduce development time :)

MSAccess seems to remember the field order in the design view...

I have written a little tool that connects to the specified DB, and I select
a table from the list. Then I have a series of 'row templates' one of which
I select. Then I pres the [Generate] button, and a the ASP/HTML <table> code
is copied to the clipboard.

This saves me a lot of time, however, the table rows <tr> always need
reorganising which just about cancels out the time saved :(



So the question is...

Can I arrange the fields in the same order that they appear in the MDB, not
alphabetically ?

Thanks,
-Douglas



PS: What is '4e generation...' - is that 4th instead of 4e?

[Bob Barrows [MVP]]

Douglas wrote:

>
> So the question is...
>
> Can I arrange the fields in the same order that they appear in the
> MDB, not alphabetically ?
>

A better plan would be to allow your interface to specify the field order,
and generate the select list so that the columns are selected in the proper
order. Depending on the physical field order may work for a while, but will
bite you in the @ss when you move to other database systems.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Evertjan.]

Douglas wrote on 05 apr 2004 in microsoft.public.inetserver.asp.db:

> PS: What is '4e generation...' - is that 4th instead of 4e?

Not all have English as their first language.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Douglas]

Gday Evertjan,

Sorry mate, wasnt being critical, just wanted to clarify :)

Some poor souls only have english as their ONLY language ;)

-Douglas

"Evertjan." <exjxw.hannivoort@interxnl.net> wrote in message
news:Xns94C29C1B7B251eejj99@194.109.133.29...

> Douglas wrote on 05 apr 2004 in microsoft.public.inetserver.asp.db:

> > PS: What is '4e generation...' - is that 4th instead of 4e?

>
> Not all have English as their first language.
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

[Douglas]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uPsMSuwGEHA.3816@TK2MSFTNGP10.phx.gbl...

> Douglas wrote:
>

> >
> > So the question is...
> >
> > Can I arrange the fields in the same order that they appear in the
> > MDB, not alphabetically ?
> >

> A better plan would be to allow your interface to specify the field order,

ok...

directly speaking, can it be done specifically using ado/adox ?

> and generate the select list so that the columns are selected in the

proper

> order. Depending on the physical field order may work for a while, but

will

> bite you in the @ss when you move to other database systems.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Al Reid]

Have you tried looking at the OpenSchema method on the ADO Connection Object? Look at the adSchemaColumns. There you will find
ORDINAL_POSITION.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

"Douglas" <post.to.the.group@so.everyone.can.learn.com.au> wrote in message
news:40716062$0$27648$61ce578d@news.syd.swiftdsl.c om.au...

>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:uPsMSuwGEHA.3816@TK2MSFTNGP10.phx.gbl...

> > Douglas wrote:
> >

> > >
> > > So the question is...
> > >
> > > Can I arrange the fields in the same order that they appear in the
> > > MDB, not alphabetically ?
> > >

> > A better plan would be to allow your interface to specify the field order,

>
>
> ok...
>
> directly speaking, can it be done specifically using ado/adox ?
>
>

> > and generate the select list so that the columns are selected in the

> proper

> > order. Depending on the physical field order may work for a while, but

> will

> > bite you in the @ss when you move to other database systems.
> >
> > Bob Barrows
> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >

>
>

[Bob Barrows [MVP]]

Douglas wrote:

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:uPsMSuwGEHA.3816@TK2MSFTNGP10.phx.gbl...

>> Douglas wrote:
>>

>>>
>>> So the question is...
>>>
>>> Can I arrange the fields in the same order that they appear in the
>>> MDB, not alphabetically ?
>>>

>> A better plan would be to allow your interface to specify the field
>> order,

>
>
> ok...
>
> directly speaking, can it be done specifically using ado/adox ?
>

Yes it can:

set rs=cn.OpenSchema(adSchemaColumns, _
array(empty,empty,"Categories",empty))
do until rs.eof
Response.Write rs("COLUMN_NAME") & ": " & _
rs("ORDINAL_POSITION") & "<BR>"
rs.movenext
loop
rs.close:set rs=nothing
cn.close:set cn=nothing

But don't depend on it.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Douglas]

"Al Reid" <areidjr@reidDASHhome.com> wrote in message
news:OwA7kTxGEHA.3164@TK2MSFTNGP11.phx.gbl...

> Have you tried looking at the OpenSchema method on the ADO Connection

Object? Look at the adSchemaColumns. There you will find

> ORDINAL_POSITION.
>
> --
> Al Reid
>
> "It ain't what you don't know that gets you into trouble. It's what you

know

> for sure that just ain't so." --- Mark Twain

Thanks very much Al, those two words was all i needed !


Heres what I've got so far for anyone thats interested...


Private Sub Command1_Click()

Dim rsSchema As ADODB.Recordset
Dim fld As ADODB.Field
Dim rCriteria As Variant

Dim cnn
Set cnn = New ADODB.Connection

With cnn
.CursorLocation = adUseClient
.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=X:\your.mdb;"
End With

Set rsSchema = cnn.OpenSchema(adSchemaColumns, Array(Empty, Empty,
"tblAddresses"))

rsSchema.Sort = "ORDINAL_POSITION"

Debug.Print "Fields according to Ordinal_Position:"

While Not rsSchema.EOF
Debug.Print rsSchema!COLUMN_NAME
rsSchema.MoveNext
Wend

End Sub