retrieving field names?

[Splurd]

Basicly instead of hard codding Response.Write (rs("name"))Response.Write (rs("whatever"))Response.Write (rs("omg"))is there a way, where I can get the field names from a databse? then the page can loop thru the field names.Edit: Or to be more specific, to get the field names from a recordset.I am asking this because I am trying to make a page, where people can select the fields they want (probably by a drop down, or by a checkbox) and the page does its thing and displays the data.Now, I COULD something like, if "from.name isChecked then response.write(rs("name"))" but its alot of hardcoding, and its not flexible.I'm still relativly new to asp and I'm just messing around with various tutorials I found in the net, so I hope I dont screw up and break the internet or something.

[Yahweh]

Basicly instead of hard codding Response.Write (rs("name"))Response.Write (rs("whatever"))Response.Write (rs("omg"))is there a way, where I can get the field names from a databse? then the page can loop thru the field names.Edit: Or to be more specific, to get the field names from a recordset.

<{POST_SNAPBACK}>

Yes, there are actually a couple of different ways to get the fields from your table.First, lets imagine that we had the following table that stores blog entries, or something similar:

Articles--------ArticleID    Author  Content          DatePublished     Category     Rating1            Yahweh  Kittens rock...  2006-05-01        1            52            Yahweh  Vote Green...    2006-05-01        1            4.53            Bob     Hello world...   2006-05-02        3            44            Joe     My blog suc...   2006-05-03        2            55            Bob     Two cows...      2006-05-03        1            4.16            Jane    Once upon ...    2006-05-06        3            4.8

Method 1: For Each loopTo get any single record without having to type the name of your table, you can use a For Each loop:

<%Dim Conn, RS, SQL, XSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")SQL = "Select * From Articles"Conn.Open your_connection_stringRS.Open SQL, Conn 1, 1    'This  do loop goes through all of the records in your database    Do until rs.eof        'This For Each loop iterates through each field in the record        response.write "<p>"        For Each X in RS.Fields            'The variable X holds the value of your field            Response.Write X & "<br>"        Next        Response.Write "</p>"        rs.Movenext    LoopRS.CloseConn.CloseSet Conn = nothingSet RS = nothing%>

Method 2: GetRowsI prefer using the GetRows method to get the fields I need without explicitly specifying them, because I have a little more control over the way data is displayed than a For Each loop. Basically, it crams the results from my query into a 2-dimensional array, then I can loop through the array as usual to display the information I need.The array is constructed like this:(number of fields)(number of rows)Remember that arrays start at zero, so that array(3)(5) corresponds to the 4th field and 6th row of your recordset.

<%Dim Conn, RS, SQL, arrData, iRows, iRecordSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")'These numbes will become useful below:'                 0         1       2           3           4         5SQL = "Select ArticleID, Author, Content, DatePublished, Category, Rating " & _        "From Articles"Conn.Open your_connection_stringRS.Open SQL, Conn 1, 1    arrData = RS.GetRows()RS.CloseConn.CloseSet Conn = nothingSet RS = nothing'Notice that I can cram my recordset into an array and close my connection'immediately.iRows = UBound(arrData, 2)    'This gets the maximum number from the second             'subscript in the arrData array, which happens to correspond to the             'number of rows For iRecord = 0 to iRows    response.write "<p>"    response.write arrData(0, iRecord) & "<br>"   'Prints the articleID    response.write arrData(1, iRecord) & "<br>"    'Prints the author    response.write arrData(2, iRecord) & "<br>"    'Prints the content    response.write arrData(3, iRecord) & "<br>"    'Prints the datepublished    response.write arrData(4, iRecord) & "<br>"    'Prints the category    response.write arrData(5, iRecord) & "<br>"    'Prints the rating    response.write "</p>"    'Notice that each # in arrData(#, iRecord) corresponds to the numbers I marked    'above. This is because when the two dimensional array is created, it    'numbers off each field in the order that the fields are specified. If you     'added another field to your SQL statement, the numbers for first subscript    'would change correspondingly.    'The variable iRecord is the row number of records in the recordset. This loop    'iterates through all of the records as the iRecord variable increments.Next%>

If you didn't want to print arrData(#, iRecord) a dozen times, you could use this code:

Dim iRows, iRecord, iFields, iFieldNumberiFields = UBound(arrData, 1) 'get the number of fieldsiRows = UBound(arrData, 2) 'get the number of rowsFor iRecord = 0 to iRows    response.write "<p>"        For iFieldNumber = 0 to iFields            response.write arrData(iFieldNumber, iRecord) & "<br>"        Next    response.write "</p>"Next

Using the GetRows method does have its advantages over the RS(fieldname) method in terms of speed.

I am asking this because I am trying to make a page, where people can select the fields they want (probably by a drop down, or by a checkbox) and the page does its thing and displays the data.Now, I COULD something like, if "from.name isChecked then response.write(rs("name"))" but its alot of hardcoding, and its not flexible.

Either method specified above will work just fine. I personally prefer the GetRows method, because its faster and more logical. First, create your page with form fields you want to send, some like this:display.asp:

<html><body>Note that you are submitting this page to itself.<form action="display.asp" method="post">   <select name="fields" multiple="true">   <option value="articleID">ArticleID</option>   <option value="author">ArticleID</option>   <option value="content">ArticleID</option>   <option value="datepublished">datepublished</option>   <option value="rating">Rating</option>   </select>   <input type="submit"></form><%Dim myFieldsDim Conn, RS, SQL, arrData, iRows, iRecord, iFields, iFieldCountermyFields = cstr(request("fields"))if myFields <> "" then    'Your myFields variable is automatically formatted as a comma-seperated    'list. You don't need to do any parsing to it.        'Getting table data and putting it into an array    Set Conn = Server.CreateObject("ADODB.Connection")    Set RS = Server.CreateObject("ADODB.RecordSet")    SQL = "Select " & myFields & " from Articles"    Conn.Open your_connection_string    RS.Open SQL, Conn, 1, 1        arrData = RS.GetRows()    RS.Close    Conn.Close        Set Conn = nothing    Set RS = nothing        'Getting dimensions of array    iRows = UBound(arrData, 2)    iFields = UBound(arrData, 1)        'Now generating the table    response.write "<table>"    'Going to be doing some clever formatting to the myFields variable.    'I'll be replacing all of the commas in the list with header tags.    myFields = Replace(myFields, ",", "</th><th>", 1, -1, vbBinaryCompare)    response.write "<tr><th>" & myFields & "</th></tr>"    For iRecord = 0 to iRows        response.write "<tr>"        For iFieldCounter = 0 to iFields            response.write "<td>"            resopnse.write arrData(iFieldCounter, iRecord)            response.write "</td>"        Next        'Add any extra cells to the table here.        response.write "</tr>"    Next    response.write "</table>"End if%></body></html>

Now you have completely customized and auto-formatted table.

[Splurd]

ah no, you sorta mis read me.What I am looking for are the field names, the headers, or whatever the correct term is.using your example, it would be "ArticleID Author Content DatePublished Category Rating"

[Yahweh]

ah no, you sorta mis read me.What I am looking for are the field names, the headers, or whatever the correct term is.using your example, it would be "ArticleID    Author  Content          DatePublished    Category    Rating"

<{POST_SNAPBACK}>

Ah, gotcha. Its virtually identical code to what I wrote above. Try this:

<%Dim Conn, RS, SQL, XSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")'Set X = Server.CreateObject("ADODB.Field")'You may or may not need to create and ADO Field object. I don't think you do,'but if the code below doesn't work, try uncommenting it and see what'happens.SQL = "Select * From Articles"Conn.Open your_connection_stringRS.Open SQL, Conn 1, 1   'This  do loop goes through all of the records in your database    If Not RS.Eof then        For Each X in RS.Fields            Response.write X.Name & "<br>"        Next    End ifRS.CloseConn.CloseSet Conn = nothingSet RS = nothing%>

That should print out all of the field names without having to specify them.

[Splurd]

Awesome man, many thanks.One more question, is there anyway to get the number of fields? Although I dont really need it, its just a good number to get (when your making arrays or looping)