to get column name

Mariusp · October 15th, 2003, 06:48 AM

Help! How I can get a table colum name in asp recordset
exp.

sql="select * from Orders "
Set RS=objConnection.Execute(sql)
Set fld = server.CreateObject("ADODB.Field")

Error message:

Server.CreateObject Failed ,Invalid ProgID

Imar · October 15th, 2003, 06:58 AM

Hi there,

The Field object is part of ADODB as well:

set fld = server.CreateObject("ADOR.Field")

should be:

set fld = server.CreateObject("ADODB.Field")

Why do you assign a recordset to RS by executing your SQL statement, and then resetting it again to a new and empty recordset using set rs = Server.CreateObject("ADODB.Recordset")?

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

OgunNike · January 28th, 2004, 06:04 PM

Hi! I'm having the exact same problem in retrieving the field names. In fact, we just patched to the latest MDAC 2.8 but it didn't help. Here's my code:

     <%
        Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
    OBJdbConnection.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ="&server.mappath("****/******.mdb")&";"

    Set FieldList = Server.CreateObject("ADODB.Recordset")
    FieldList.Open "SELECT * FROM Roadshows", OBJdbConnection

    Set F = Server.CreateObject("ADODB.Field")
    %>

      Field:</b>  <select size="1" name="field_edit">
       <option></option>
       <%do until FieldList.EOF%>
       <option><%Response.Write("" & F.Name & "")%></option>
          <%FieldList.MoveNext%>
        <%loop
        FieldList.close
        OBJdbConnection.close
        %>
         </select>
************************************************** **************

I'm getting the following error:

Server object error 'ASP 0177 : 800401f3'

Server.CreateObject Failed

/Roadshow_Creator.asp, line 375

Invalid ProgID.

************************************************** ***********
The other objects are working fine: parameter and command. But field and property are not working.

Could there possibly be anything wrong with my code? If not, what patch is needed on the server?

Please help!! Thanks! Noah

Imar · January 28th, 2004, 06:28 PM

Hi there,

What are you using the Field object for? To retrieve the column name?

If that's the case, take a look at this thread:
http://p2p.wrox.com/topic.asp?TOPIC_ID=6751

Also, take a look here for a more up-to-date connection string example:
http://www.able-consulting.com/MDAC/...orMicrosoftJet

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

OgunNike · January 28th, 2004, 06:37 PM

Inmar,
Thanks so much for getting back to me so quickly!

I'm using it to populate a drop-down selector. I've had plenty of success before getting the column names to populate <th> like in the example, but I couldn't get it to work for my drop down. I wonder why the ADODB.Field is out of reach. We just investigated the IUSER_MACHINE account's permissions but they don't see any failures when they audit so I don't think that's it.

Best, Noah

Imar · January 29th, 2004, 04:27 AM

I think there are a couple of problems. First of all, I don't think you can instantiate an object of type Field. It looks to me that there is no constructor, so you can't use New ADODB.Field in VB, which, I believe, is what Server.CreateObject does. To get a field, simple declare a variable and then assign a field to it.

The second part is the loop for the recordset. I don't get what you're trying to do. You loop through the records in the recordset, yet you expect F, the Field, to be assigned automagically and have a value....

If you want to display the meta data in the recordset, like the Field names, try this:

Code:

Set FieldList = Server.CreateObject("ADODB.Recordset")
FieldList.Open "SELECT * FROM Roadshows", OBJdbConnection

' Don't create the Field object
'Set F = Server.CreateObject("ADODB.Field")
' Just declare a variable for it
Dim  F ' As ADODB.Field

For Each F In FieldList.Fields
  Response.Write("Fieldname: " & F.Name & "<br>")
Next

This code will write out all the fieldnames that are in the recordset. This means that it returns al the columnheaders that are retrieved with the SELECT * SQL statement.

If you also want to display the records in the recordset, use the do until FieldList.EOF construct.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

aurelioa · August 18th, 2004, 01:22 PM

I use adoRS.Fields(IntColumnsStart0).Name as well

topdog1 · September 1st, 2004, 01:50 PM

Quote:

quote:Originally posted by Imar
I think there are a couple of problems. First of all, I don't think you can instantiate an object of type Field. It looks to me that there is no constructor, so you can't use New ADODB.Field in VB, which, I believe, is what Server.CreateObject does. To get a field, simple declare a variable and then assign a field to it.

The second part is the loop for the recordset. I don't get what you're trying to do. You loop through the records in the recordset, yet you expect F, the Field, to be assigned automagically and have a value....

If you want to display the meta data in the recordset, like the Field names, try this:

Code:

Set FieldList = Server.CreateObject("ADODB.Recordset")
FieldList.Open "SELECT * FROM Roadshows", OBJdbConnection

' Don't create the Field object
'Set F = Server.CreateObject("ADODB.Field")
' Just declare a variable for it
Dim  F ' As ADODB.Field

For Each F In FieldList.Fields
  Response.Write("Fieldname: " & F.Name & "<br>")
Next

This code will write out all the fieldnames that are in the recordset. This means that it returns al the columnheaders that are retrieved with the SELECT * SQL statement.

If you also want to display the records in the recordset, use the do until FieldList.EOF construct.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

binobose · September 27th, 2004, 01:46 AM

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'employee'

this is the Syntax
Bino Bose

happygv · September 30th, 2004, 09:59 AM

Bino Bose,

You are right, if you tries to get the column names from SQL. But in this post the OP has asked for getting the column names from the ADO RECORDSET of which the resultset(rows from orders table) is too extracted. So I don't think this could help the OP.

Cheers!

_________________________
- Vijay G
Strive for Perfection