I took the liberty of writing this for you, considering I already had it all put together in different libraries. I am assuming that you are using MS SQL 2000 for your database server, if not, then this server-side script is not gonna work for you. However, the client side will, if you can manage to populate the JS data.
ok, here goes. feel free to email me if you have questions... I have tested this code and it does work. I tried to be as descriptive as I could with the comments, mind you, I wrote this in about 20 minutes.
real quick, what it does is gather all the table names in the specified database (specified via the dsn connection string) and their columns for use in the two select boxes.... 'nuff said. on with the show.
Code:
<!--
Author ......... create.vibe
Date Created ... Saturday, March 04, 2006
Position ....... create.vibe Founder
Website......... http://www.createvibe.com
eMail........... contact@createvibe.com
CONCEPTS:
---------------
- Dynamic SQL
- ASP -> JavaScript
- Understaing Select Boxes
-->
<%
'' asp code to get all table names and their columns (working with MS SQL 2k)
on error resume next
dim conn, rsTables, rsColumns, dsnConnection
dsnConnection = "Provider=sqloledb;Data Source=DATA_SOURCE_NAME;Initial Catalog=DATABASE_NAME;User Id=USER_NAME;Password=PASSWORD;"
set conn = server.createobject("adodb.connection")
if err.number <> 0 then
'' error while opening connection, redirect to error.asp and display message
set conn = nothing
response.redirect "error.asp?error="& escape(err.description)
end if
conn.cursorLocation = 3 ' so we have access to connection.recordCount
conn.Open dsnConnection ' store your connection string in a variable (see devguru.com or connectionstrings.com for more info on connection.open with ado)
%>
<html>
<head>
<title>dynamic sql, asp -> javsacript, and understanding select boxes (jam packed! woah.. slow down now, ya movin too quick)</title>
</head>
<script language="javascript">
// array of tables (objects) with an array of columns
var col_indice;
var aTables = [];
<%
'' get all table names
set rsTables = conn.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME ASC")
do while not rsTables.eof
response.write "aTables[aTables.length] = {name:"""& rsTables("TABLE_NAME") &""", columns:[]};"& vbcrlf '"
'' get all columns from table
set rsColumns = conn.execute("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '"& rsTables("TABLE_NAME") &"' ORDER BY ORDINAL_POSITION ASC")
do while not rsColumns.eof
response.write "col_indice = aTables[aTables.length - 1].columns.length;"& vbcrlf
response.write "aTables[aTables.length - 1].columns[col_indice] = """& rsColumns("COLUMN_NAME") &""";"& vbcrlf & vbcrlf '"
rsColumns.moveNext
loop
rsTables.moveNext
loop
%>
/*
checkSelect(oSelect) - Returns a boolean value
- if the selected option has the value of '-1' then return false,
- alert a message, and select the 1st option in the select box.
- if the selected option has any other value, return true
'oSelect' needs to be a reference to a valid SELECT Node (HTML Element)
we use the 'selectedIndex' property of the select box. this data member is
read and write, so if we set it to 0, JS will select the very first option.
if we were to set it to 1, it would select the 2nd option, and so on.
when you select an option on the browser, JS sets the selectedIndex of that
select box to the index key of the option you selected. what I mean is, the
options are stored like an array. and like an array, they are indexed. you
have window.document.forms.myForm.mySelectBox. the options are stored in an
array named 'options'. window.document.forms.myForm.mySelectBox.options[],
and each option has the data members 'text,' 'value,' and more.
*/
function checkSelect(oSelect) {
var checkVal = oSelect.options[oSelect.selectedIndex].value;
if (checkVal == "" || checkVal == "-1") {
alert("Invalid Option");
oSelect.selectedIndex = 0;
return false;
}
return true;
}
/*
displayColumns(oSelect, oForm) - Called via the 'onChange' event of the 'boxes' select box
'oSelect' must be a valid reference to the 'tableName' select box
'oForm' must be a valid reference to the form that both select boxes belong to
- how it works -
1) store the value of the selected [table] in a variable 'table_name'
2) create a reference to the columns's select box 'tableColumn'
3) make sure the table_name is valid
4) set the amount of options in the column's box to 0
5) create the instructional options as the first to explain the contents
6) loop through the tables array 'aTables' to find all the matching table
when we find that table, populate the options of the tableColumn select box
with all the column names found in that object's columns array
-
*/
function displayColumns(oSelect, oForm) {
var i, x; // iteration variables
var table_name = oSelect.options[oSelect.selectedIndex].value; // value of the selected option in 'tableName'
var tableColumn = oForm.tableColumn; // reference to the column's select box 'tableColumn'
if (table_name != "" && table_name != "-1") {
tableColumn.options.length = 0; // remove all contents
// instructional options (indexes 0 and 1, values of '-1')
tableColumn.options[0] = new Option("Columns in table ["+ oSelect.options[oSelect.selectedIndex].text +"]", "-1");
tableColumn.options[1] = new Option("-", "-1");
for (i = 0; i < aTables.length; i++) { // loop through all tables
if (aTables[i].name == table_name) { // find columns that match the variable 'table_name'
for (x = 0; x < aTables[i].columns.length; x++)
tableColumn.options[tableColumn.options.length] = new Option(aTables[i].columns[x], aTables[i].columns[x]); // create the new option
break; // break out of the loop
}
}
}
}
</script>
<body bgcolor="#FFFFFF">
<table cellspacing="0" cellpadding="0" border="0">
<form name="frm_selectBoxes">
<tr>
<th>Available Tables:</th>
<td width="15"></td>
<th>Columns:</th>
</tr>
<tr>
<td>
<select name="tableName" onChange="if (checkSelect(this)) displayColumns(this, this.form)">
<option value="-1">Select a Table to View Its Columns</option>
<option value="-1">-</option>
<%
'' populate the tableName options
rsTables.moveFirst
do while not rsTables.eof
response.write "<option value="""& rsTables("TABLE_NAME") &""">"& rsTables("TABLE_NAME") &"</option>"& vbcrlf
rsTables.moveNext
loop
%>
</select>
</td>
<td width="15"></td>
<td>
<select name="tableColumn" onChange="if (checkSelect(this)) alert('You have selected '+ this.options[this.selectedIndex].text)">
<option value="-1">Select a Table Name First</option>
<option value="-1">-</option>
</select>
</td>
</tr>
</form>
</table>
</body>
</html>
<%
'' clean up
conn.close
set conn = nothing
set rsTables = nothing
set rsColumns = nothing
%>
Hmm..Dynamic drop downs eh? I will surely look into that. 
