I am still working on building a better mousetrap, or in my case a better 

stats program.  I would like to use an INNER JOIN sql statement using sum 

statements inner joining with a player names table.  The problem I have is 

that I don't want to hard-code the field names in case I later decide to 

add or delete a field.  I currently have a program which visits each table 

twice: Once just to get the field names (through a loop) for the SQL 

statement and another time to actually perform the completed SQL.  

   Here is what I currently do, which I know is a waste for the server: 

First I do a "SELECT * FROM tablename" and go through a (While NOT EOF) 

loop to find each field's name and put it into a SUM statement.  When I'm 

done with all fields, I then have a statement such as (SELECT 

Players.Name, SUM(Field1) AS Sum1, SUM(Field2) AS Sum2,.... INNER JOIN 

Players.

   I then wipe out the recordset and build another one using the SQL 

statement I just made.  This, in turn, creates a recordset with each 

player's totals in each field.  Now this may sound all good and well, but 

I still see an area where it could improve and that is where I get the 

field names.  I would like to know if there is a way to get all the field 

names without having to get all of the table data and looping through all 

of the field names?  It is such a waste to gather all of the data just to 

have it deleted when I get all of the field names.



Chris

Chris,



You could set the MaxRecords property of the first recordset to 1 and then

loop through the Fields collection using the FOR EACH ... IN ... NEXT

looping statements. This should be fairly efficient. You have to get the

field names from somewhere:







' Instantiate the first recordset

set StatRecordset = CreateObject( "ADODB.Recordset" )



' Limit records to one

set StatRecordset.MaxRecords = 1



' Open the recordset

''' Put your code here



' Loop through the fields collection

FOR EACH StatField IN StatRecordset



    ' Current Field name

    ThisFieldName = StatField.Name



    ' Add to SQL statement here

    ''' Put your code here



NEXT



' Close the stat recordset

StatRecordset.Close





----------------------------------------------------





----- Original Message -----

From: "Christopher Cote" <chrscote@9...>

To: "Access ASP" <access_asp@p...>

Sent: Monday, October 15, 2001 12:59 AM

Subject: [access_asp] Retrieving only Field Names





> I am still working on building a better mousetrap, or in my case a better

> stats program.  I would like to use an INNER JOIN sql statement using sum

> statements inner joining with a player names table.  The problem I have is

> that I don't want to hard-code the field names in case I later decide to

> add or delete a field.  I currently have a program which visits each table

> twice: Once just to get the field names (through a loop) for the SQL

> statement and another time to actually perform the completed SQL.

>    Here is what I currently do, which I know is a waste for the server:

> First I do a "SELECT * FROM tablename" and go through a (While NOT EOF)

> loop to find each field's name and put it into a SUM statement.  When I'm

> done with all fields, I then have a statement such as (SELECT

> Players.Name, SUM(Field1) AS Sum1, SUM(Field2) AS Sum2,.... INNER JOIN

> Players.

>    I then wipe out the recordset and build another one using the SQL

> statement I just made.  This, in turn, creates a recordset with each

> player's totals in each field.  Now this may sound all good and well, but

> I still see an area where it could improve and that is where I get the

> field names.  I would like to know if there is a way to get all the field

> names without having to get all of the table data and looping through all

> of the field names?  It is such a waste to gather all of the data just to

> have it deleted when I get all of the field names.

>

> Chris

> 

Hello folks,

   Well, once again I asked some people at work about this method for 

getting fields and I came across someone else who knew an even better way 

without having to use a loop to get the field names.  I would like to 

thank everyone, though, for their help.  While this was a very big 

improvement over what I had, I have found something even better.  Here's 

what another of my collegues told me.  All you need to do is call your 

database connection's schema object with the name of the table you want. 

Or, if you want to display each table's field names, do a loop through 

each table's name. Here is what I learned:



set cn = Server.CreateObject("ADODB.Connection")

cn.Open "DBName"                 'Insert the database name here

Set RS = cn.OpenSchema(adSchemaColumns, Array(empty, empty, 

empty, "TableName"))



This returns a record set with the field names of the table "TableName".  

If you are looking for the actual table names, use adSchemaTables instead 

of adSchemaColumns.

In order to use this method, though, you need to include adovbs.inc which 

is usually included on all servers that allow asp anyway.  Just add a line 

to the top of you code that reads:

<!--#include virtual="/adovbs.inc"-->



Hope this helps everyone else as much as it did me.



Chris



> Chris,

> 

> You could set the MaxRecords property of the first recordset to 1 and 

then

> loop through the Fields collection using the FOR EACH ... IN ... NEXT

> looping statements. This should be fairly efficient. You have to get the

> field names from somewhere:

> 

> 

> 

> ' Instantiate the first recordset

> set StatRecordset = CreateObject( "ADODB.Recordset" )

> 

> ' Limit records to one

> set StatRecordset.MaxRecords = 1

> 

> ' Open the recordset

> ''' Put your code here

> 

> ' Loop through the fields collection

> FOR EACH StatField IN StatRecordset

> 

>     ' Current Field name

>     ThisFieldName = StatField.Name

> 

>     ' Add to SQL statement here

>     ''' Put your code here

> 

> NEXT

> 

> ' Close the stat recordset

> StatRecordset.Close

>