473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting column names of a table in C# using OleDB

Joe
Hi All,

I am new to using the Access DB and I need some help if someone
is able to give it to me. What I want to do is get the names of
the columns of certain tables. Not the data in the table but the
table column names.

I've seen other posts that suggest using the SQL command DESCRIBE
but I can't get it to work for some reason. Other posts have code
samples but they're written in VB which I am not familiar with. I
am looking for a piece of sample code written in C# using the
OleDbConnection classes, if that is possible. An SQL command would
be great but from what I've read there is no DESCRIBE command or
an equivalent. Can someone help me out?

Thanks in advance for any help or direction!

Sincerely,
Joe
Nov 13 '05 #1
2 23835
Joe wrote:
Hi All,

I am new to using the Access DB and I need some help if someone
is able to give it to me. What I want to do is get the names of
the columns of certain tables. Not the data in the table but the
table column names.

I've seen other posts that suggest using the SQL command DESCRIBE
but I can't get it to work for some reason. Other posts have code
samples but they're written in VB which I am not familiar with. I
am looking for a piece of sample code written in C# using the
OleDbConnection classes, if that is possible. An SQL command would
be great but from what I've read there is no DESCRIBE command or
an equivalent. Can someone help me out?

Thanks in advance for any help or direction!

Sincerely,
Joe


This link may help. I've cut some of the code out from one of the
response that may help you. I think part of it should show you the
properties in the collection you need. Not my forte tho.

http://groups.google.com/groups?hl=e...6btnG%3DSearch

Option Strict On
Module Main
'microsoft adox ext 2.7 for dll and security
Public Sub Main()
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File.Exists("C:\db1.mdb") Then
System.IO.File.Delete("C:\db1.mdb")
System.IO.File.Delete("C:\db2.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\db1.mdb")
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\db2.mdb")
catNewDB = Nothing
catNewDB = Nothing
Dim conn1 As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim conn2 As OleDb.OleDbConnection = New OleDb.OleDbConnection
conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db1.mdb;User Id=admin;Password=;"
conn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db2.mdb;User Id=admin;Password=;"
conn1.Open()
Dim cmd1 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn1)
cmd1.ExecuteNonQuery()
conn2.Open()
Dim cmd2 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn2)
cmd2.ExecuteNonQuery()
Try
For i As Integer = 1 To 9
cmd1.Parameters.Clear()
cmd1.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd1.Parameters.Add(New OleDb.OleDbParameter("@a",
OleDb.OleDbType.Integer)).Value = i
cmd1.Parameters.Add(New OleDb.OleDbParameter("@b",
OleDb.OleDbType.Char, 20)).Value = Chr(64 + i)
cmd1.ExecuteNonQuery()
Next
For i As Integer = 4 To 12 Step 2
cmd2.Parameters.Clear()
cmd2.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd2.Parameters.Add(New OleDb.OleDbParameter("@a",
OleDb.OleDbType.Integer)).Value = i
cmd2.Parameters.Add(New OleDb.OleDbParameter("@b",
OleDb.OleDbType.Char, 20)).Value = Chr(75 + i)
cmd2.ExecuteNonQuery()
Next
cmd1.CommandText = "Select * from tbl1"
Dim da1 As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(cmd1)
Dim ds1 As DataSet = New DataSet
Dim dt1 As New DataTable("tbl1")
Dim dca As New DataColumn("a", Type.GetType("System.Int32"))
Dim dcb As New DataColumn("b", Type.GetType("System.String"))
dt1.Columns.Add(dca)
dt1.Columns.Add(dcb)
ds1.Tables.Add(dt1)
cmd1.CommandText = "Select a, b from tbl1"
Dim rdr As OleDb.OleDbDataReader
rdr = cmd1.ExecuteReader()
While rdr.Read()
Dim dr As DataRow
dr = ds1.Tables(0).NewRow
dr("a") = rdr.GetInt32(0)
dr("b") = rdr.GetString(1)
ds1.Tables(0).Rows.Add(dr)
End While
rdr.Close()
ds1.AcceptChanges()
cmd2.CommandText = "Select * from tbl1"
cmd2.Connection = conn2
Dim da2 As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(cmd2)
Dim cmb2 As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da2)
Dim ds2 As DataSet = New DataSet
conn2.Close()
da2.Fill(ds2)
For i As Integer = 0 To ds1.Tables(0).Rows.Count - 1
Dim swN As Boolean = True
For y As Integer = 0 To ds2.Tables(0).Rows.Count - 1
Dim a As Integer = ds2.Tables(0).Rows.Count - 1
If CInt(ds1.Tables(0).Rows(i).Item(0)) =
CInt(ds2.Tables(0).Rows(y).Item(0)) Then
ds2.Tables(0).Rows(y).Item(1) =
ds1.Tables(0).Rows(i).Item(1)
swN = False
Exit For
End If
Next
If swN Then
Dim dr As DataRow
dr = ds2.Tables(0).NewRow
For y As Integer = 0 To
ds1.Tables(0).Rows(i).ItemArray.Length - 1
dr(y) = ds1.Tables(0).Rows(i).ItemArray(y)
Next
ds2.Tables(0).Rows.Add(dr)
End If
Next
da2.Update(ds2)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
conn1.Close()
conn2.Close()
End Sub
End Module

Nov 13 '05 #2
Joe
Salad <oi*@vinegar.com> wrote in message news:<mx*****************@newsread2.news.pas.earth link.net>...
Joe wrote:
Hi All,

I am new to using the Access DB and I need some help if someone
is able to give it to me. What I want to do is get the names of
the columns of certain tables. Not the data in the table but the
table column names.

I've seen other posts that suggest using the SQL command DESCRIBE
but I can't get it to work for some reason. Other posts have code
samples but they're written in VB which I am not familiar with. I
am looking for a piece of sample code written in C# using the
OleDbConnection classes, if that is possible. An SQL command would
be great but from what I've read there is no DESCRIBE command or
an equivalent. Can someone help me out?

Thanks in advance for any help or direction!

Sincerely,
Joe


This link may help. I've cut some of the code out from one of the
response that may help you. I think part of it should show you the
properties in the collection you need. Not my forte tho.

http://groups.google.com/groups?hl=e...6btnG%3DSearch

Option Strict On
Module Main
'microsoft adox ext 2.7 for dll and security
Public Sub Main()
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File.Exists("C:\db1.mdb") Then
System.IO.File.Delete("C:\db1.mdb")
System.IO.File.Delete("C:\db2.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\db1.mdb")
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\db2.mdb")
catNewDB = Nothing
catNewDB = Nothing
Dim conn1 As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim conn2 As OleDb.OleDbConnection = New OleDb.OleDbConnection
conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db1.mdb;User Id=admin;Password=;"
conn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db2.mdb;User Id=admin;Password=;"
conn1.Open()
Dim cmd1 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn1)
cmd1.ExecuteNonQuery()
conn2.Open()
Dim cmd2 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn2)
cmd2.ExecuteNonQuery()
Try
For i As Integer = 1 To 9
cmd1.Parameters.Clear()
cmd1.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd1.Parameters.Add(New OleDb.OleDbParameter("@a",
OleDb.OleDbType.Integer)).Value = i
cmd1.Parameters.Add(New OleDb.OleDbParameter("@b",
OleDb.OleDbType.Char, 20)).Value = Chr(64 + i)
cmd1.ExecuteNonQuery()
Next
For i As Integer = 4 To 12 Step 2
cmd2.Parameters.Clear()
cmd2.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd2.Parameters.Add(New OleDb.OleDbParameter("@a",
OleDb.OleDbType.Integer)).Value = i
cmd2.Parameters.Add(New OleDb.OleDbParameter("@b",
OleDb.OleDbType.Char, 20)).Value = Chr(75 + i)
cmd2.ExecuteNonQuery()
Next
cmd1.CommandText = "Select * from tbl1"
Dim da1 As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(cmd1)
Dim ds1 As DataSet = New DataSet
Dim dt1 As New DataTable("tbl1")
Dim dca As New DataColumn("a", Type.GetType("System.Int32"))
Dim dcb As New DataColumn("b", Type.GetType("System.String"))
dt1.Columns.Add(dca)
dt1.Columns.Add(dcb)
ds1.Tables.Add(dt1)
cmd1.CommandText = "Select a, b from tbl1"
Dim rdr As OleDb.OleDbDataReader
rdr = cmd1.ExecuteReader()
While rdr.Read()
Dim dr As DataRow
dr = ds1.Tables(0).NewRow
dr("a") = rdr.GetInt32(0)
dr("b") = rdr.GetString(1)
ds1.Tables(0).Rows.Add(dr)
End While
rdr.Close()
ds1.AcceptChanges()
cmd2.CommandText = "Select * from tbl1"
cmd2.Connection = conn2
Dim da2 As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(cmd2)
Dim cmb2 As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da2)
Dim ds2 As DataSet = New DataSet
conn2.Close()
da2.Fill(ds2)
For i As Integer = 0 To ds1.Tables(0).Rows.Count - 1
Dim swN As Boolean = True
For y As Integer = 0 To ds2.Tables(0).Rows.Count - 1
Dim a As Integer = ds2.Tables(0).Rows.Count - 1
If CInt(ds1.Tables(0).Rows(i).Item(0)) =
CInt(ds2.Tables(0).Rows(y).Item(0)) Then
ds2.Tables(0).Rows(y).Item(1) =
ds1.Tables(0).Rows(i).Item(1)
swN = False
Exit For
End If
Next
If swN Then
Dim dr As DataRow
dr = ds2.Tables(0).NewRow
For y As Integer = 0 To
ds1.Tables(0).Rows(i).ItemArray.Length - 1
dr(y) = ds1.Tables(0).Rows(i).ItemArray(y)
Next
ds2.Tables(0).Rows.Add(dr)
End If
Next
da2.Update(ds2)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
conn1.Close()
conn2.Close()
End Sub
End Module


Hi Salad,

Thanks you for the reply and you help. After some more research, I
was able to find a solution. I will post it here for anyone else who
may be looking into the same problem. This little snipet of code gets
the name of the table, does some filtering, and gets the column name
and its type in its ordinal position. In other words, the columns
wind up being in the order in which they were created. If you do not
end up doing this sorting the columns wind up in aphabetical order.
The columns data type is stored as an integer and I don't know what
the correct mapping it so I just store the info as such in a class
called TableInfo that I created. Anyway, here it is.
private void GetTableInfo()
{
DataRowView drvCols = null;
DataTable dtCols = null;
DataView dvColumns = null;
TableInfo tabInfoObj = null;
string sTableNm = null;
string sNamePrefix = null;
string sColName = null;
int nColType = 0;

// Get the list of tables that there are.
DataTable schemaTab =
m_oleDbSrcConn.GetOleDbSchemaTable(OleDbSchemaGuid .Tables,
new Object[] {null, null, null, "TABLE"});

for (int i = 0; i < schemaTab.Rows.Count; i++)
{
sTableNm = schemaTab.Rows[i].ItemArray[2].ToString();
sNamePrefix = sTableNm.Substring(0, 4);

if ((sTableNm != "_NEW_TABLES") && (sNamePrefix != "temp"))
{
tabInfoObj = new TableInfo(sTableNm);

// Get the column name and its type and sort them
// according to their position in the table.
dtCols = m_oleDbSrcConn.GetOleDbSchemaTable(
OleDbSchemaGuid.Columns,
new Object[]{null,null, sTableNm, null});
dvColumns = new DataView(dtCols);
dvColumns.Sort = "ORDINAL_POSITION";

// Get the column names and their types.
for (int j=0; j< dvColumns.Count; j++)
{
// Get the name of the column.
drvCols = dvColumns[j];
sColName = drvCols.Row.ItemArray[3].ToString();

// Get columns data type code and save it off.
nColType = Convert.ToInt32(drvCols.Row.ItemArray[11]);
tabInfoObj.AddColumn(sColName, nColType);
m_alTables.Add(tabInfoObj);
}
} // if

} // for
}
I hope this helps someone!

Later,
Joe
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Showjumper | last post by:
Is it possible to retrieve a table name and then all the column names in that table from an access database? And then once that is done, then build a sql insert statement using the retrieved table...
4
by: Brent | last post by:
My SQL might generate a result like this: Security 2005-06-30 2005-03-31 2004-12-31 IBM $65 $100 $123 (etc) or like this: Security 2005-06-30 2005-03-31
7
by: | last post by:
Need help finding simple way to retrieve a fields (or columns) collection from a Dataset without filling it first.
1
by: svijay | last post by:
hi I have got a strange problem. May I know any solution for this. Here is the detailed description about the problem We have got a mainframe system and also production and development...
3
by: beary | last post by:
I picked up the following code to take a mysql table to an excel file. What do I need to do to it to make the first excel row be the column names, rather than the first record? $query = "SELECT *...
5
by: dananrg | last post by:
Is there any way to retrieve column names from a cursor using the ODBC module? Or must I, in advance, create a dictionary of column position and column names for a particular table before I can...
3
by: koti688 | last post by:
how to get the column names and the values of them in select statment. I have a packaze like this named ADB.pm package ADB; use DBI; @ISA = ('Exporter');
2
by: Infog | last post by:
I have written a chunk of code that automatically writes a SQL string, using parameters. The parameters themselves are easy, but getting the column names has been a problem. Is possible to get...
1
by: ncsthbell | last post by:
Once again... I am supporting another Access database that I did not build. The previous 'owner' of this built the tables with spaces in the table names as well as the column names. I am having a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.