Using fields

morpheus · November 20th, 2003, 08:59 AM

<%
If Not objRec.EOF Then

    Response.Write("<table>")
    Response.Write("<tr>")
    For Each objField In objRec.Fields
        Response.Write("<td>" & objField.name & "</td>")
    Next
    Response.Write("</tr>")
    While Not objRec.EOF
        Response.Write("<tr>")
        For Each objField In objRec.Fields
            Response.Write("<td>" & objRec(objField.Name)& "</td>")
        Next
        Response.Write("</tr>")
        objRec.MoveNext
    Wend
    Response.Write("</table>")

End If
%>

I have the above code which works fine, but I only want to show 5 fields in the table instead of all of them, there is too many. How can I modify the code to only show the field names and fields I want?
Thanks

planoie · November 20th, 2003, 10:40 AM

Very simply, you could change the query that generates the recordset so select only the field you want.

Peter
------------------------------------------------------
Work smarter, not harder.

morpheus · November 20th, 2003, 10:54 AM

objRec.Open "Books",strConnect,0,1,2

I am just using this to grab all the fields in the talbe.
How do I define certain field names here?

Imar · November 20th, 2003, 11:22 AM

Well, in that case, simply refer to the columns in the recordset by name:

Response.Write("<tr>")
  Response.Write("<td>" & objRec("FirstColumn")& "</td>")
  Response.Write("<td>" & objRec("ThirdColumn")& "</td>")
  Response.Write("<td>" & objRec("SecondColumn")& "</td>")
Response.Write("</tr>")
objRec.MoveNext

This will write out the values of the three columns. Note that you can refer to the column (as in this example) or by an index:

Response.Write("<tr>")
  Response.Write("<td>" & objRec(0)& "</td>")
  Response.Write("<td>" & objRec(2)& "</td>")
  Response.Write("<td>" & objRec(1)& "</td>")
Response.Write("</tr>")
objRec.MoveNext

In both examples I used a mixed column order, just to show you that it is possible.

Cheers,

Imar

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

morpheus · November 20th, 2003, 11:33 AM

Thanks Imar that works great, but how do I get the field names I want?

Response.Write("<td>" & objField.name & "</td>")

this line here is messing me up, I can fill the table with certain records but all the field names are still showing up. Or should I just make a table row in html for the headings and fill in in with only the columns I want?

Thanks

planoie · November 20th, 2003, 11:42 AM

Yes, that's what he's suggesting. I recall giving you the code that you first posted here. That's useful for showing everything in a table, but if you want a specific output, you need to construct like Imar shows in his post. In short:

<table>
    <tr>
        <td>header</td>
        <td>header</td>
        <td>header</td>
        <td>header</td>
    </tr>
    <% 'do your "for each row" loop here %>
        <tr>
            <td><%=recordset("column1")%></td>
            <td><%=recordset("column2")%></td>
            <td><%=recordset("column3")%></td>
            <td><%=recordset("column4")%></td>
        </tr>
    <% 'next row %>
</table>

Peter
------------------------------------------------------
Work smarter, not harder.

Imar · November 20th, 2003, 11:49 AM

Yes, I think it's an either / or situation. Either you use the For Each Field in Fields construct, or you should create the header manually.

The For Each is useful if you don't know the column names in advance. You should use the header solution if you want to display just a few columns, or if you want control over the way the labels are displayed (StartDate looks good in a database, but on your Web page you'd want something like Start Date).

Regards,

Imar

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

jacob · November 20th, 2003, 12:01 PM

Imar...

I have been following this thread too, and I was wondering... I have never been able to extract the column names from an Access database, is that actually possible...!? I once did something like this...

Code:

Set recAtr = Server.CreateObject ("ADODB.Recordset")
recAtr.Open "SHOW COLUMNS FROM someTable", someDB

...using a MySQL databse which enabled be to make a table view; A view of the entire table where the headers where shown as in the database! Is it infact possible with Access aswell...!?

Really curious, best regards

Jacob.

Imar · November 20th, 2003, 12:13 PM

Yes, it is. When you open a recordset, you not only get the data, but also the metadata; data about data.

This way you can retrieve column names, types, etc. Not every piece of information is supported by all drivers / databases, but AFAIK, you should be able to get the data from access like this:

' Create Connection
Set objRec = myConnection.Execute("SELECT * FROM MyTableInAccessDatabase")
If Not objRec.EOF Then
    Response.Write("<table>")
    Response.Write("<tr>")
    For Each objField In objRec.Fields
        Response.Write("<td>" & objField.name & "</td>")
    Next
    Response.Write("</tr>")
    While Not objRec.EOF
        Response.Write("<tr>")
        For Each objField In objRec.Fields
            Response.Write("<td>" & objRec(objField.Name)& "</td>")
        Next
        Response.Write("</tr>")
        objRec.MoveNext
    Wend
    Response.Write("</table>")
End If

I just copied and pasted the posted example so I can't guarantee it's bug free, but it should work like this.

Is this what you mean??

Imar

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

morpheus · November 20th, 2003, 12:43 PM

Thanks guys you are a big help!!

In regards to Jacob it does work in returning all the Field names and thier data in a table, I only want a couple columns and that's what they were helping me with, but the code above returns the whole table, well at least it worked for me, lol

Thanks again