Professional Web Applications Themes

Getting column data without column names - Coldfusion Database Access

Hello all, I'm trying to write a coldfusion program to display account data such as name, address, phone #, etc. I retrieve this data from an SQL query. The thing is, I want to make the code more robust by allowing a variable number of columns. Namely, if someone adds another column to the SQL table such as hobbies, I don't want to have to change the code. I found an SQL query to get the colum names in the order they appear in the table. I search for the record in the table by account number. My question is, ...

  1. #1

    Default Getting column data without column names

    Hello all,

    I'm trying to write a coldfusion program to display account data such as
    name, address, phone #, etc. I retrieve this data from an SQL query. The
    thing is, I want to make the code more robust by allowing a variable number of
    columns. Namely, if someone adds another column to the SQL table such as
    hobbies, I don't want to have to change the code. I found an SQL query to get
    the colum names in the order they appear in the table. I search for the record
    in the table by account number. My question is, when I get the one row back
    from the query is there a way to loop through that row without knowing the
    column names, thereby allowing the code to automatically pick up on new
    columns? Thanks in advance for your time and help. This is a great forum for
    getting good answers.

    -Jared

    Jared@Itron Guest

  2. #2

    Default Re: Getting column data without column names

    This is a simple example of dynamically displaying your column names and data
    in a table. They will, however, be listed in alphabetical order.

    <cfquery name="Q1" datasource="your_dsn">
    SELECT *
    FROM your_table
    WHERE whatever
    </cfquery>

    <cfif IsDefined("Q1.ColumnList")>
    <table border="1">
    <tr><cfloop list="#Q1.ColumnList#" index="col" delimiters=",">
    <th align="left" nowrap><cfoutput>#col#</cfoutput></th>
    </cfloop></tr>
    <cfoutput query="Q1">
    <tr><cfloop list="#Q1.ColumnList#" index="col">
    <td align="left" nowrap>#Q1[col][CurrentRow]#</td></cfloop></tr>
    </cfoutput>
    </table>
    </cfif>

    Phil

    paross1 Guest

  3. #3

    Default Re: Getting column data without column names

    Originally posted by: JaredItron
    Hello all,

    I'm trying to write a coldfusion program to display account data such as
    name, address, phone #, etc. I retrieve this data from an SQL query. The
    thing is, I want to make the code more robust by allowing a variable number of
    columns. Namely, if someone adds another column to the SQL table such as
    hobbies, I don't want to have to change the code. I found an SQL query to get
    the colum names in the order they appear in the table. I search for the record
    in the table by account number. My question is, when I get the one row back
    from the query is there a way to loop through that row without knowing the
    column names, thereby allowing the code to automatically pick up on new
    columns? Thanks in advance for your time and help. This is a great forum for
    getting good answers.

    -Jared

    You had better hope the new field is not a date field.

    Dan Bracuk Guest

  4. #4

    Default Re: Getting column data without column names

    Why is that? Also, once I have this dynamic list of column names and text
    input fields with the current data already in them, how do I Update the SQL
    table? Do I have to dynamically reference each column in that row to put the
    new data in them? I have to name the text inputs dynamically to give them
    different names as in name="param#count#". I can't use that within two other #
    signs to update the table as in name=#param#count##.

    Jared@Itron Guest

  5. #5

    Default Re: Getting column data without column names

    Do a simple test.
    Create a table add a record, and run paross1's code against it. Then add a date field, set it to something and rerun paross1's code.
    Dan Bracuk Guest

Similar Threads

  1. how to get data grid column names
    By swapnil Daingade in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: January 24th, 07:50 PM
  2. Replies: 0
    Last Post: October 11th, 03:19 AM
  3. counting column and getting column names
    By dduck1934 in forum Coldfusion Database Access
    Replies: 0
    Last Post: May 24th, 02:05 PM
  4. How to get column names using SQL?
    By Gert van der Kooij in forum IBM DB2
    Replies: 7
    Last Post: July 11th, 04:36 PM
  5. No column names
    By Vesna Martinovic in forum IBM DB2
    Replies: 0
    Last Post: July 3rd, 07:45 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139