Solved

Get Access Datatype in .net

Posted on 2004-08-30
5
283 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
  • 3
  • 2
5 Comments
 

Author Comment

by:tristan256
Comment Utility
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
Comment Utility
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
Comment Utility
" 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
Comment Utility
Ok then , it is possible to retrieve the datatypes in access, ie with vb6?
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 250 total points
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
countClumps  challenge 10 87
Visual xHarbour 1 73
Delphi Mdi application Child forms get behind control 7 110
word0 challenge 4 52
Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now