Data Type Mapping in ADODB

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 1 OleDbType.BigInt 2 DBTYPE_I8 3	bigint 3	8			Variant
adBinary	128	Byte[] SqlDbType.VarBinary 1 OleDbType.Binary 2 DBTYPE_BYTES 3	binary timestamp	50 8	Binary LongBinary	Raw 4	Variant
adBoolean	11	Boolean SqlDbType.Bit 1 OleDbType.Boolean 2 DBTYPE_BOOL 3	bit	1 2	Bit YesNo		Boolean
adBSTR	8	String OleDbType.BSTR 2
adChapter	136	(DataReader)
adChar	129	String SqlDbType.Char 1 OleDbType.Char 2 DBTYPE_STR [^09]	char	x		Char	String
adCurrency	6	Decimal SqlDbType.Money 1 OleDbType.Currency 2 DBTYPE_CY 3	money smallmoney	8 4	Currency		Currency
adDate	7	DateTime OleDbType.DBDate 2		0	DateTime 5		Date
adDBDate	133	DateTime OleDbType.DBDate 2
adDBFileTime	137	DBFileTime 2
adDBTime	134	DateTime OleDbType.DBTime 2
adDBTimeStamp	135	DateTime SqlDbType.DateTime 1 OleDbType.DBTimeStamp 2 DBTYPE_DBTIMESTAMP 3	datetime smalldatetime	8 4	DateTime 6	Date	Date
adDecimal	14	Decimal OleDbType.Decimal 2				Decimal 4	Variant 7
adDouble	5	Double SqlDbType.Float 1 OleDbType.Double 2 DBTYPE_R8 3	float	8	Double	Float	Double
adEmpty	0	Empty 2
adError	10	External-Exception OleDbType.Error 2
adFileTime	64	DateTime OleDbType.Filetime 2
adGUID	72	Guid SqlDbType.UniqueIdentifier 1 OleDbType.Guid 2 DBTYPE_R8 3	uniqueidentifier 8	16	Guid ReplicationID 5, 9	Variant
adIDispatch	9	Object OleDbType.IDispatch 2
adInteger	3	Int32 SqlDbType.Int 1 OleDbType.Integer 2 DBTYPE_I4 3	identity 10 int	4 4	Counter AutoNumber LongInteger	Int 4	Long
adIUnknown	13	Object OleDbType.IUnknown 2
adLongVarBinary	205	Byte[] SqlDbType.VarBinary 1 OleDbType.LongVarBinary 2 DBTYPE_BYTES 3	image	2147483647	OLEObject	Long Raw 4 Blob 11	Variant
adLongVarChar	201	String SqlDbType.VarChar 1 OleDbType.LongVarChar 2 DBTYPE_STR3	text	2147483647	Memo 6, 5 Hyperlink 6, 5	Long 4 Clob 11	String
adLongVarWChar	203	String SqlDbType.NText 1 OleDbType.VarWChar 2 DBTYPE_WSTR 3	ntext 8	1073741823	Memo 9 Hyperlink 9	NClob 11	String
adNumeric	131	Decimal SqlDbType.Decimal 1 OleDbType.Decimal 2 DBTYPE_NUMERIC 3	decimal numeric	9	Decimal 9 Decimal Integer Number SmallInt	Variant 7
adPropVariant	138	Object OleDbType.PropVariant 2
adSingle	4	Single SqlDbType.Real 1 OleDbType.Single 2 DBTYPE_R4 3	real	4	Single		Single
adSmallInt	2	Int16 SqlDbType.SmallInt 1 OleDbType.SmallInt 2 DBTYPE_I2 3	smallInt	2	Integer	Short	Integer
adTinyInt	16	Byte OleDbType.TinyInt 2
adUnsignedBigInt	21	UInt64 OleDbType.UnsignedBigInt 2
adUnsignedInt	19	UInt32 OleDbType.UnsignedInt 2
adUnsignedSmallInt	18	UInt16 OleDbType.UnsignedSmallInt 2
adUnsignedTinyInt	17	Byte SqlDbType.TinyInt 1 OleDbType.UnsignedTinyInt 2 DBTYPE_UI1 3	tinyInt	1	Byte		Byte
adUserDefined	132
adVarBinary	204	Byte[] SqlDbType.VarBinary 1 OleDbType.VarBinary 2 DBTYPE_BYTES 3	varbinary	50	ReplicationID 6	Variant
adVarChar	200	String SqlDbType.VarChar 1 OleDbType.VarChar 2 DBTYPE_STR 3	varchar	x	Text 6, 5 LongText	VarChar	String
adVariant	12	Object SqlDbType.Variant 1 OleDbType.Variant 2 DBTYPE_VARIANT 3 DBTYPE_SQLVARIANT 3	sql_variant 3	8016		VarChar2	Variant
adVarNumeric	139	OleDbType.VarNumeric 2
adVarWChar	202	String SqlDbType.NVarChar 1 OleDbType.VarWChar 2 DBTYPE_WSTR 3	nvarchar 8	x	Text 9	NVarChar2	String
adWChar	130	String SqlDbType.NChar 1 OleDbType.WChar 2 DBTYPE_WSTR 3	nchar 8	x			String

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

Attribution to Carl Prothman (carl @ prothman.org) - Original Url

Footnotes

1. SQL Server .NET Data Provider (via System.Data.SqlTypes) ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶ ↩⁷ ↩⁸ ↩⁹ ↩¹⁰ ↩¹¹ ↩¹² ↩¹³ ↩¹⁴ ↩¹⁵ ↩¹⁶ ↩¹⁷ ↩¹⁸ ↩¹⁹ ↩²⁰ ↩²¹

2. OLE DB .NET Data Provider (via System.Data.OleDb) ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶ ↩⁷ ↩⁸ ↩⁹ ↩¹⁰ ↩¹¹ ↩¹² ↩¹³ ↩¹⁴ ↩¹⁵ ↩¹⁶ ↩¹⁷ ↩¹⁸ ↩¹⁹ ↩²⁰ ↩²¹ ↩²² ↩²³ ↩²⁴ ↩²⁵ ↩²⁶ ↩²⁷ ↩²⁸ ↩²⁹ ↩³⁰ ↩³¹ ↩³² ↩³³ ↩³⁴ ↩³⁵ ↩³⁶ ↩³⁷ ↩³⁸

3. OLE DB Provider: SQLOLEDB, SQL Server 2000 + ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶ ↩⁷ ↩⁸ ↩⁹ ↩¹⁰ ↩¹¹ ↩¹² ↩¹³ ↩¹⁴ ↩¹⁵ ↩¹⁶ ↩¹⁷ ↩¹⁸ ↩¹⁹ ↩²⁰ ↩²¹ ↩²² ↩²³

4. Oracle 8.0.x Note: DECIMAL and INT are synonyms for NUMBER and NUMBER(10) respectively. ↩ ↩² ↩³ ↩⁴ ↩⁵

5. OLE DB Provider: Microsoft.Jet.OLEDB.3.51, Access 97 (3.5 format) ↩ ↩² ↩³ ↩⁴ ↩⁵

6. ODBC Driver (3.51.171300): Microsoft Access Driver (*.mdb), Access 97 (3.5 format) ↩ ↩² ↩³ ↩⁴ ↩⁵

7. 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. ↩ ↩²

8. OLE DB Provider: SQLOLEDB, SQL Server 7.0 + ↩ ↩² ↩³ ↩⁴

9. OLE DB Provider: Microsoft.Jet.OLEDB.4.0 , Access 2000 (4.0 format) ↩ ↩² ↩³ ↩⁴ ↩⁵

10. OLE DB Provider: SQLOLEDB, SQL Server 6.5 ↩

11. Oracle 8.1.x ↩ ↩² ↩³