populating a ddl with field names from a database

Hi all,
I like to be able to populate a drop down list with field names from a database table. I can get the values in the fields no problem, but am unable to get the actual field names. To get the values I use a simple SQL line 'Select * from tbTest1', but does anyone know how to the get the names of each of each of the fields??

Thanks in advance.
0
shannon_rider
9/26/2005 3:45:05 PM
asp.net.web-forms 93655 articles. 6 followers. Follow

3 Replies
441 Views

Similar Articles

[PageSpeed] 32

SELECT * will return all the fields in the table. IF you only need some fields you need to list out the columns you want in the SELECT statement. 
sample:

SELECT
   column1,
   column2
FROM
   table


***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
9/26/2005 4:07:20 PM

Try something like this:

SqlDataReader dataReader = // Create a DataReader here

// Get the schema DataTable from the DataReader...   
DataTable dataTable = dataReader.GetSchemaTable();

// Done with the DataReader, so close it...
dataReader.Close();

DataColumn columnName = dataTable.Columns["ColumnName"];
DataColumn columnSize = dataTable.Columns["ColumnSize"];
DataColumn columnType = dataTable.Columns["DataType"];

foreach (DataRow dataRow in dataTable.Rows)
{
 string currentColumnName = dataRow[columnName].ToString();
 string currentColumnType = dataRow[columnType].ToString();
 string currentColumnSize dataRow[columnSize].ToString();
}


For your direct problem:
DropDownList1.Clear();

// Using data binding:
DropDownList1.DataValueField = "ColumnName";
DropDownList1.DataTextField = "ColumnName";
DropDownList1.DataSource = dataTable;
DropDownList1.DataBind();

// Or do it by hand:
DataColumn columnName = dataTable.Columns["ColumnName"];
foreach (DataRow dataRow in dataTable.Rows)
{
 string currentColumnName = dataRow[columnName].ToString();
 DropDownList1.Items.Add(new ListItem(currentColumnName, currentColumnName));  
}

NC...

0
NC01
9/26/2005 4:38:22 PM

Hi Shannon,

   Are you just talking about one table where you've selected the data, or would you like to be able to pull information for *all* tables?  If you just want the field names for one table that you've already selected, try filling a DataTable with your query and then you can access myDataTable.Columns(columnIndex).ColumnName.  There is a Caption property as well, but I think ColumnName will give you what you want.

   If you just want all the field names for any given table, you'll need to use the sysobjects and syscolumns tables.  Here is a query that will give you all fields by table:

select sysobjects.name, syscolumns.name
from sysobjects, syscolumns
where sysobjects.id = syscolumns.id
and sysobjects.name = 'myTableName'

Hope that helps!


Peter Brunone
MS MVP, ASP.NET
Founder, EasyListBox.com
Do the impossible, and go home early.
0
PeterBrunone
9/26/2005 6:24:36 PM
Reply: