Search
Saturday, April 19, 2014 ..:: Technology » Data Type Mapping ::.. Register  Login
Site Navigation

 Data Type Mapping Minimize

The following table shows the ADO Data Type mapping between Visual Basic, Access, SQL Server, Oracle, and the .NET Framework.

ADO
DataType
Enum
ADO
DataType
Enum
Value
  Mapped
Data Type
SQL
Server
Size Access Oracle Visual
Basic
6.0
adBigInt 20 Int64
SqlDbType.BigInt 10
OleDbType.BigInt 11

DBTYPE_I8  9
bigint 9 8     Variant
adBinary 128 Byte[]
SqlDbType.VarBinary 10
OleDbType.Binary 11

DBTYPE_BYTES  9
binary
timestamp
50
8
Binary
LongBinary
Raw 7 Variant
adBoolean 11 Boolean
SqlDbType.Bit 10
OleDbType.Boolean 11

DBTYPE_BOOL  9
bit
1
2
 
Bit
YesNo
  Boolean
adBSTR 8 String
OleDbType.BSTR 11
         
adChapter 136 (DataReader)          
adChar 129 String
SqlDbType.Char 10
OleDbType.Char 11

DBTYPE_STR 9
char x   Char String
adCurrency 6 Decimal
SqlDbType.Money 10
OleDbType.Currency 11

DBTYPE_CY 9
money
smallmoney

8
4
 
Currency   Currency
adDate 7 DateTime
OleDbType.DBDate 11
  0 DateTime 2   Date
adDBDate 133 DateTime
OleDbType.DBDate 11
         
adDBFileTime 137 DBFileTime 11          
adDBTime 134 DateTime
OleDbType.DBTime 11
         
adDBTimeStamp 135 DateTime
SqlDbType.DateTime 10
OleDbType.DBTimeStamp 11

DBTYPE_DBTIMESTAMP 9
datetime
smalldatetime
 
8
4

DateTime 1 Date Date
adDecimal 14 Decimal
OleDbType.Decimal 11
      Decimal 7 Variant 6
adDouble 5 Double
SqlDbType.Float 10
OleDbType.Double 11

DBTYPE_R8 9
float

 
Double Float Double
adEmpty 0 Empty 11          
adError 10 External-Exception
OleDbType.Error 11
         
adFileTime 64 DateTime
OleDbType.Filetime 11
         
adGUID 72 Guid
SqlDbType.UniqueIdentifier 10
OleDbType.Guid 11

DBTYPE_R8 9
uniqueidentifier 5 16 Guid
ReplicationID 2,3
  Variant
adIDispatch 9 Object
OleDbType.IDispatch 11
         
adInteger 3 Int32
SqlDbType.Int 10
OleDbType.Integer 11

DBTYPE_I4 9

identity 4
int

4
4
Counter
AutoNumber
LongInteger
Int 7 Long
adIUnknown 13 Object
OleDbType.IUnknown 11
         
adLongVarBinary 205 Byte[]
SqlDbType.VarBinary 10
OleDbType.LongVarBinary 11

DBTYPE_BYTES 9
image 2147483647 OLEObject Long Raw 7
Blob 8
Variant
adLongVarChar 201 String
SqlDbType.VarChar 10
OleDbType.LongVarChar 11

DBTYPE_STR 9
text 2147483647 Memo 1, 2
Hyperlink 1, 2
Long 7
Clob 8
String
adLongVarWChar 203 String
SqlDbType.NText 10
OleDbType.VarWChar 11

DBTYPE_WSTR 9
ntext 5 1073741823 Memo 3
Hyperlink 3
NClob 8 String
adNumeric 131 Decimal
SqlDbType.Decimal 10
OleDbType.Decimal 11

DBTYPE_NUMERIC 9
decimal
numeric
9
Decimal 3 Decimal
Integer
Number
SmallInt
Variant 6
adPropVariant 138 Object
OleDbType.PropVariant 11
         
adSingle 4 Single
SqlDbType.Real 10
OleDbType.Single 11

DBTYPE_R4 9
real 4 Single   Single
adSmallInt 2 Int16,
SqlDbType.SmallInt 10
OleDbType.SmallInt 11

DBTYPE_I2 9
smallInt 2 Integer
Short
  Integer
adTinyInt 16 Byte
OleDbType.TinyInt 11
         
adUnsignedBigInt 21 UInt64
OleDbType.UnsignedBigInt 11
         
adUnsignedInt 19 UInt32
OleDbType.UnsignedInt 11
         
adUnsignedSmallInt 18 UInt16
OleDbType.UnsignedSmallInt 11
         
adUnsignedTinyInt 17 Byte
SqlDbType.TinyInt 10
OleDbType.UnsignedTinyInt 11

DBTYPE_UI1 9
tinyInt 1 Byte   Byte
adUserDefined 132            
adVarBinary 204 Byte[]
SqlDbType.VarBinary 10
OleDbType.VarBinary 11

DBTYPE_BYTES 9
varbinary 50 ReplicationID 1   Variant
adVarChar 200 String
SqlDbType.VarChar 10
OleDbType.VarChar 11

DBTYPE_STR 9
varchar x Text 1, 2
LongText
VarChar String
adVariant 12 Object
SqlDbType.Variant 10
OleDbType.Variant 11

DBTYPE_VARIANT 9
DBTYPE_SQLVARIANT 9
sql_variant 9 8016   VarChar2 Variant
adVarNumeric 139 OleDbType.VarNumeric 11          
adVarWChar 202 String
SqlDbType.NVarChar 10
OleDbType.VarWChar 11

DBTYPE_WSTR 9
nvarchar 5 x
Text 3 NVarChar2 String
adWChar 130 String
SqlDbType.NChar 10
OleDbType.WChar 11

DBTYPE_WSTR 9
nchar 5 x     String

Top of Page

1 - ODBC Driver (3.51.171300): Microsoft Access Driver (*.mdb), Access 97 (3.5 format)
2 - OLE DB Provider: Microsoft.Jet.OLEDB.3.51, Access 97 (3.5 format)
3 - OLE DB Provider: Microsoft.Jet.OLEDB.4.0 , Access 2000 (4.0 format)
4 - OLE DB Provider: SQLOLEDB, SQL Server 6.5
5 - OLE DB Provider: SQLOLEDB, SQL Server 7.0 +
6 - The VB Decimal data type can only be used within a Variant, that is, you cannot declare
a VB variable to be of type Decimal.
7 - Oracle 8.0.x Note: DECIMAL and INT are synonyms for NUMBER and NUMBER(10) respectively.
8 - Oracle 8.1.x
9 - OLE DB Provider: SQLOLEDB, SQL Server 2000 +
10 - SQL Server .NET Data Provider (via System.Data.SqlTypes)
11 - OLE DB .NET Data Provider (via System.Data.OleDb)

Note: "User Defined" data types (e.g. ID, TID, EmpID, SysName) are not shown on this diagram.


  

Copyright 2005 CarlProthman.NET   Terms Of Use  Privacy Statement