Sort Columns directly from table

# Thread: Sort Columns directly from table

1. Junior Member
Join Date
Dec 1969
Posts
7

## Sort Columns directly from table

Hi, <BR><BR>I need a little help here. Hope you guys can help me out. I have tables which are created when my user creates a product. Therefore, I cant predict what columns there are in a table. But i Need to display these column names in alphabetical order. <BR>My codes are below, how should I do to get it in alphabetical order?<BR><BR><BR>&#060;%For fnum = 0 To RS.Fields.Count-1<BR>fieldname = rs.fields(fnum).name<BR>&#039; response.write fieldname<BR>next%&#062;<BR><BR>Thank you!<BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Why alphabetical order?

Wouldn&#039;t it make more sense to allow the user to *control* the order?<BR><BR>For that matter, why not just show them in the order the user gave them in?<BR><BR>Oh, well...if you must, you must:<BR><BR>&#060;%<BR>&#039; so I assume you have done something like this:<BR>Set RS = conn.Execute( "SELECT * FROM someTable" )<BR><BR>fldNumMax = RS.Fields.Count-1<BR><BR>Dim flields()<BR>ReDim fields( fldNumMax )<BR>&#039; get the field names into an array<BR>For fnum = 0 To fldNumMax<BR> fields(fnum) = RS.Fields(fnum).Name<BR>Next<BR>&#039; sort the array of names<BR>For i = fldNumMax-1 To 0 Step -1<BR> For j = 0 To i<BR> If fields(j) &#062; fields(j+1) Then<BR> temp = fields(j)<BR> fields(j) = fields(j+1)<BR> fields(j+1) = temp<BR> End If<BR> Next <BR>Next<BR><BR>&#039; and now display the names<BR>Response.Write "&#060;TR&#062;" & vbNewLine<BR>For fnum = 0 To fldNumMax<BR> Responses.Write "&#060;TH&#062;" & fields(fnum) & "&#060;/TH&#062;" & vbNewLine<BR>Next<BR>Response.Write "&#060;/TR&#062;" & vbNewLine<BR><BR>&#039; and then show all the records<BR>Do Until RS.EOF<BR> Response.Write "&#060;TR&#062;" & vbNewLine<BR> For fnum = 0 To fldNumMax<BR> Responses.Write "&#060;TD&#062;" & RS(fields(fnum)) & "&#060;/TD&#062;" & vbNewLine<BR> Next<BR> Response.Write "&#060;/TR&#062;" & vbNewLine<BR> RS.MoveNext<BR>Loop<BR>%&#062;<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
449

## maybe

and this is just a suggestion!!!<BR><BR>use the following sql statement. These are system tables so there may be many reasons that stop you from getting access to them but otherwise will give you the table column names already sorted alphabetically.<BR><BR>SELECT syscolumns.name<BR>FROM syscolumns, sysobjects<BR>WHERE syscolumns.id = sysobjects.id <BR>AND sysobjects.name=&#039;&#060;name of table&#062;&#039;<BR>ORDER BY syscolumns.name<BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## You are assuming...

...a particular DB, aren&#039;t you? SQL Server?<BR><BR>Want to bet that this person has only Access?<BR><BR>Also, all that does is get the columns in alphabetical order.<BR><BR>How does that help get the *data* from the given table after getting those names? You end up having to get all the column names into a single string and then using that string as your SELECT list for another query.<BR><BR>Yeah, this work. And you could use ADODB.Connection.OpenSchema to make it work with virtually any DB. But it *does* involve two queries to the DB, whereas grabbing the info the way I did can be done with one.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
449

## Yes,

that sql will not work on access database,<BR>most other databases SQL Server, Sybase etc should be OK<BR>I apologise for not pointing this out, sorry, once again I have made an *** out of me and...me. <BR><BR>To the originator, I was just looking at an alternative to the question you asked, this was just something different. <BR>As Bill says if you want to then display whats in the tables (and lets face it why wouldn&#039;t you) another request to the DB would obviously be required since this only gives the column names. However I&#039;m not making any assumptions on what you ultimately want, err... except that your not using Access. <BR><BR>OK, I&#039;ll shutup now.

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Now stop doing that!

It was a *GREAT* answer. It demonstrated how to get info about fields in a given table from the catalog(s).<BR><BR>Hell, it&#039;s certainly not a *WRONG* answer! There&#039;s no reason you couldn&#039;t use that code to get a list of fields and then use that list to do a SELECT in another query. Let&#039;s face it, in some circumstances it&#039;s gonna be the best way to go.<BR><BR>I was only *quibbling* over *this* particular case.<BR><BR>I am a pedant and a quibbler. Don&#039;t cave in! Fight back and tell me *my* stupid mistakes! And believe me, I make plenty of them. (Search for my trademark "IYAMADORK" posts.)<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•