Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get Access Datatype in .net

Posted on 2004-08-30
5
Medium Priority
?
313 Views
Last Modified: 2012-05-05
I have access database, and I need to get the field datatypes of a table.  How do I do this in .net?  Note I don't mean the oledb datatype ie System.Type.Int32 etc, I mean whether its a an Autonumber,number,yes/no etc.

Cheers,
Tristan
0
Comment
Question by:tristan256
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:tristan256
ID: 11929684
Extra bonus points for anyone who tells me how to get the list of tables in an access database from .net.

Cheers,
Tristan
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 11930337
Well here's a code for getting Tables in a database its columns and its datatypes (It works with all databases however it might give some unrequired system tables which you can filter with a smart if clause)


Dim Cn As OleDbConnection
Cn = New OleDbConnection(sConnString)
Cn.Open()

Dim Restricts() As Object = {Nothing, Nothing, Nothing, "Table"}
Dim TblSchema As DataTable
Dim Cmd As OleDbCommand
Dim DR As OleDbDataReader
Dim Tbl As DataTable

TblSchema = Cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Restricts)
Dim R As DataRow
For Each R In TblSchema.Rows
      If CStr(R(3) & "").ToUpper = "TABLE" Then
              Cmd = New OleDbCommand("Select * From " & CStr(R(2)), Cn)
                DR = Cmd.ExecuteReader(CommandBehavior.SchemaOnly)
                Tbl = DR.GetSchemaTable
                Tbl.TableName = R(2).ToString
            Dim TbRow as DataRow
            Dim Col_Name as String
            Dim Col_DataType as String
            Dim Col_Size as String
            For each TbRow in Tbl.Rows
                  Col_Name = TbRow.Item(0)
                  Col_DataType = TbRow.Item(5)
                  Col_Size = TbRow.Item(2)
            Next
            Dr.Close
      End If
Next
Cn.Close

'However your first problem remains unsolved, i.e. the DataType returned is
'that of .Net framework and not that of the DataBase. I have so far not found any direct method of getting
'DataBase datatypes, So I use a little turna-round. I keep a table of dataMappings
'i.e. Access Text DataType is mapped to .Net String dataType
'Number is mapped to Integer and so on, so when I get System.String I refer the look up table
'and get corresponding access type
0
 

Author Comment

by:tristan256
ID: 11938024
" So I use a little turna-round. I keep a table of dataMappings
i.e. Access Text DataType is mapped to .Net String dataType"

Yeah, I thought of that, but what if the Access type is Autonumber, NOT number?  It will still say System.Int32, there will be no way of telling them apart.
0
 

Author Comment

by:tristan256
ID: 11938181
Ok then , it is possible to retrieve the datatypes in access, ie with vb6?
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 1000 total points
ID: 11938824
Yes it is possible to Retrieve Acees Table strucure and datatypes in VB6 also you'll have to use
ADOX i.e. ADO Ext. for DDL and Security

As for checking AutoNumber field, Primary Key info etc. all information is available in the previous code in the loop just check for Item(13)

For each TbRow in Tbl.Rows
               Col_Name = TbRow.Item(0)
               Col_DataType = TbRow.Item(5)
               Col_Size = TbRow.Item(2)

               Is_AutoNumber = TbRow.Item(13)
               'THIS IS A BOOLEAN VALUE
 Next

Actually the table TbRow has 14 Columns (0 Through 13) each column has certain information like IsKey, IsUnique etc. you can loop through all columns and check what information they hold.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Introduction to Processes

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question