Pulling database field names to a dropdown list 2

[craigward]

Hi,

I am looking in to the possibility of taking a SQL database table which has 50 fields and pulling the field names into an html dropdown list dynamically with classic ASP.

It is not the results / data in there i need. Reason being that i would have to manually populate the dropdown with the field names and this would take ages and the field list will always grow so i want it to be dynamic and automatically populate, just like if i was pulling the data from the table, which i already do with some applications i have built.

Thanks for any advise.

 

[Turkbear]

Hi,
Look at the docs for your database ( SQL is not enough of a description, since that is a language used by most databases) to see where the metadata is contained..Most databases have a table or view that lists the column names for a given table and you could use that as a data source for your list.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[gmmastros]

For many databases, it's the information schema views that you are looking for.

I know this would work for any version of Microsoft SQL Server:

Select Column_Name
From   Information_Schema.Columns
Where  Table_Name = 'YourTableNameHere'

You can also add an order by.

Select Column_Name
From   Information_Schema.Columns
Where  Table_Name = 'YourTableNameHere'
Order By ORDINAL_POSITION

Select Column_Name
From   Information_Schema.Columns
Where  Table_Name = 'YourTableNameHere'
Order By Column_Name

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vicvirk]

select one record (for speed) and then loop through the record and get the column names - the record can be empty, all you want to do is create a recordset with the field names and values (any or null) and loop through to get each field name.

<%
	sql = "select top 1 * from myTable"
	set rs  = myConn.execute(sql)
	[b][COLOR=red]strOptions = ""
	for each field in rs.fields
		strOptions = strOptions & "<option value='" & field.name & "'>" & field.name & "</option>"
	next[/color][/b]
	rs.close
	set rs = nothing
%>

<select>
<%=strOptions%>
</select>

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[craigward]

Thanks for the feed back. I have tried both options and have two different errors. I would like to get them both working if possible. I'm using SQL Express as my database.

I will keep workign through it but if i'm being stupid please point it out.

Thanks again.


vicvirk - Here is the alterations to the code i made and the error in case you can see anything silly i am doing.

<%
    sql = "select top 1 * from wce_sales"
    set rs  = connStr.execute(sql)
    strOptions = ""
    for each field in rs.fields
        strOptions = strOptions & "<option value='" & field.uniqueid & "'>" & field.uniqueid & "</option>"
    next
    rs.close
    set rs = nothing
%>

<select>
<%=strOptions%>
</select>

Error:

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'field.uniqueid'

/MyFirstChart/dynamicform_sales.asp, line 28


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


gmmastros - here is where i am at with your version. I do have a column called uniqueid and it happens on other columns i tried. The database name is correct also.

qry_fields = "SELECT stage FROM Information_Schema.Columns WHERE Table_Name = 'Test_Environment' AND uniqueid ='aa0PlapMKOva'"
Set oRs_fields = connStr.Execute(qry_fields)
test = oRs_fields("stage")


response.write(test)
response.end

Error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'uniqueid'.

/MyFirstChart/dynamicform_sales.asp, line 14

 

[gmmastros]

<%
  qry_fields = "SELECT Column_Name FROM Information_Schema.Columns WHERE Table_Name = '[!]wce_sales[/!]'
  Set oRs_fields = connStr.Execute(qry_fields)

  strOptions = ""
  While Not oRs_fields.EOF
    strOptions = strOptions & "<option value='" & [!]oRs_fields("Column_Name").Value[/!] & "'>" & [!]oRs_fields("Column_Name").Value[/!] & "</option>"

    oRs_fields.MoveNext
  Wend
  oRs_fields.close
  set oRs_fields = nothing
%>
<select>
<%=strOptions%>
</select>

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[craigward]

That worked a treat. Thanks for all your help i can use that in lot's of my scripts.

 

[vicvirk]

field.unique id should say "field.name" - use the word "name" as it is the property of the object "field"

The only thing you should replace is the myTable with your table name - which you did - "wce_sales"

<%
    sql = "select top 1 * from wce_sales"
    set rs  = connStr.execute(sql)
    strOptions = ""
    for each field in rs.fields
        strOptions = strOptions & "<option value='" & field.[s]uniqueid[/s]name & "'>" & field.[s]uniqueid[/s]name& "</option>"
    next
    rs.close
    set rs = nothing
%>

<select>
<%=strOptions%>
</select>

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc