C# .NET - How to get column names of a table in access database to combobox

Asked By Mahendran on 01-Jan-11 01:24 AM

hi

can anyone help me please

I need to  get column names of a table in access database to combobox  using C#.net


thanks in advance

regards
Mahendran
anil soni replied to Mahendran on 01-Jan-11 01:29 AM
Execute a false condition query and get a empty DataTable. Then traverse each DataColumn in DataTable.Columns collection and add items to the compbox. Example

OdbcDataConnection con = new OdbcConnection(connectionstring);
OdbcDataAdapater da = new OdbcDataAdapter("SELECT * FROM "+tableName+" where 1=2",con);
DataTable dtTableSchema = new DataTable();
da.Fill(dtTableSchema);
cboColumns.Items.Clear();
foreach(DataColumn dc in dtTableSchema.Columns)
{
  cboColumns.Items.Add(dc.ColumnName);
}
dt.Dispose();
da.Dispose();
con.Dispose();
dt =null;
da= null;
con=null;
Rohan Dave replied to Mahendran on 01-Jan-11 01:52 AM
you mean do you want to show your access table column name in combobox ?
Anoop S replied to Mahendran on 01-Jan-11 02:07 AM
You can use this query to find the column name of a table
SELECT column_name 'Column Name'
FROM information_schema.columns
WHERE table_name = 'Yourtablename'

You can pass this query to your c# coding and populate the combobox
code

private void Button1_Click(object sender, EventArgs e)
{
try
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<<Your Database path>>");
OleDbDataReader dr;
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT column_name 'Column Name'
FROM information_schema.columns
WHERE table_name = 'Yourtablename'", conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
//Here It Will Add Your Data To Combobox...
Combobox1.Items.Add(dr.GetValue(0).ToString());
}
conn.Close();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
Reena Jain replied to Mahendran on 01-Jan-11 02:23 AM
hi,

to get column name here is the code for you

1.  ...
 2.  ...
 3.  OdbcConnection cn=new OdbcConnection("your connection string");
 4.  OdbcCommand cmd=new OdbcCommand("select * from tablename",cn);
 5.  //open connecgtion
 6.  cn.Open();
 7.  OdbcDataReader dr;
 8.  dr=cmd.ExecuteReader()
 9.    
10. //First column name
11.  string first=dr.GetName(0);
12.
13.  //Second column name
14.  string second=dr.GetName(1);
 
17.  dr.Close();
18.  cn.Close();

or

#
...
#
...
#
OdbcConnection cn=new OdbcConnection("your connection string");
#
OdbcDataAdapter adp=new OdbcDataAdapter("select * from tablename",cn);
#
DataTable dt=new DataTable();
#
adp.Fill(dt);
#
  
#
// first column name
#
string first = dt.Columns[0].ColumnName;

hope this will help you
Mahendran replied to Rohan Dave on 02-Jan-11 11:20 PM
YEs my Friend
Mahendran replied to anil soni on 02-Jan-11 11:23 PM
Hi i got the result thanks for your help can i ask u another favour can u help me in the same that how to exclude the first column name in the combobox which is a serial number

thanks in advance

regards
M.Mahendran
anil soni replied to Mahendran on 04-Jan-11 10:48 AM
To skip the first column you can use for loop instead of using foreach statement by starting the for loop from the 1st index and not from the 0th index. See the code below

OdbcDataConnection con = new OdbcConnection(connectionstring);
OdbcDataAdapater da = new OdbcDataAdapter("SELECT * FROM "+tableName+" where 1=2",con);
DataTable dtTableSchema = new DataTable();
da.Fill(dtTableSchema);
cboColumns.Items.Clear();
for(int i=1;i<da.Tables[0].DataColumns.Count;i++) //starting from 1 st index and skipping the 0th column
{
  cboColumns.Items.Add(ds.Tables[0].DataColumns[i].ColumnName);
}
dt.Dispose();
da.Dispose();
con.Dispose();
dt =null;
da= null;
con=null;