Setup the Database and Provider type. Default DbType is ACCESS and OleDb. All the difference between database  will be resolved in this setting. Date, format, Top Records formats and SQL syntax are the most common items which is different in each database. In Ado.Net, provider is separated in OleDb, SqlClient and ODBC.dbType=CUSTOM will allow you to define your own data type.

 

Custom Data Type

• dbType = "Type=CUSTOM| LeftDateWrap='| RightDateWrap='| LeftOdd=[| RightOdd=]"
• dbLongDate = pDateTime = T/(F) = suppress hh:mm:ss for edit and filter input
• dbSQLDateFormat = Format required to build the correct date fields for SQL edit
• dbEditDateFomat = "Format displayed in the edit box as a guide for user preferred date entry format"

This format will enable user controlled DataSoruce formats. The above 3 more properties must be specified in order to implement their own data type.

dbDBType = "Type=CUSTOM| LeftDateWrap='| RightDateWrap='| LeftOdd=[| RightOdd=]"

• Left and Right wrap is the date wrapper for the datasource. For Access it is [#] and for SQL it is ['] and for Informix it is [{d '] and ['}] as Informix date format is {d 'YYYY-MM-DD'}.
• The Left and Right Odd wrapper are the wrappers when the field name contain space(s) or other non-conventional characters permitted by the datasource. For Access and SQL it is [ and ]. Oracle, for example, does not permit of such 'Odd' rules. To make sure this is clear, this pair of Odd wrapper must be valid when the field is wrapped like - SELECT [My Field] From Table.

dbLongDate =  "Ture/False"

This property is to set up the format of the Edit and Filter input in suppressing the hh:mm:ss. If this is not done correctly, if you input "12/1/2003". The standard SQL date format will include the hh:mm:ss in the input as "12/1/2003 12:00:00". If you record contains the time part then it'll cause a mismatch. If there is no DateTime field and  Date and Time is separated, set this to False.

dbSQLDateFormat = Format of Date field required to build the correct date fields for SQL edit

 

This is the format in the actual SQL edit string. This is the format that counts as far as modifying record(s) in the database. All external display formats are for visual and cultural  purpose only. For example, the Access format is -

[M/d/yyyy H:mm:ss] or [M/d/yyyy] depends on whether the dbLongDate is True or false. So if you set dbLongDate to true, use [M/d/yyyy H:mm:ss] otherwise, use [M/d/yyyy]. For Informix, use {d '[YYYY-MM-DD]'} if there is no DateTime format in the version. The [..] square brackets are mandatory as it wraps the input date format string. For example, ORACLE has the format -> TO_DATE('[d-MMM-yyyy H:mm:ss]','DD-MON-YYYY')

 

dbEditDateFomat = "Format displayed in the edit box as a guide for user preferred date entry format"

If you want to guide the user to input a certain date format like an in update box. You can display the date in this format. After user enter the date in this format it'll be converted back to the SQLDateFormat to build the Edit SQL string. Not recommend to mix with different cultures. Normally, this format is the same as the dbSQLDateFormat. For ORACLE, this format is -> TO_DATE('[d-MMM-yyyy H:mm:ss]','DD-MON-YYYY').  For Informix -> [YYYY-MM-DD].

 

The following Data Type is primary used in the dbStoredProcCmdParams property. The (3) standard verbs Date, Integer and String of different providers are highlighted as Blue.

 

SqlClient Data Type

 

Member name	Description
BigInt	Int64 - A 64-bit signed integer.
Binary	Array of type Byte - A fixed-length stream of binary data ranging between 1 and 8,000 bytes.
Bit	Boolean  -An unsigned numeric value that can be 0, 1, or a null reference (Nothing in Visual Basic).
Char	String - A fixed-length stream of non-Unicode characters ranging between 1 and 8,000 characters.
DateTime (Date)	DateTime - Date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds.
Decimal	Decimal - A fixed precision and scale numeric value between -1038 -1 and 10 38 -1.
Float	Double - A floating point number within the range of -1.79E +308 through 1.79E +308.
Image	Array of type Byte - A variable-length stream of binary data ranging from 0 to 231 -1 (or 2,147,483,647) bytes.
Int (Integer)	Int32 - A 32-bit signed integer.
Money	Decimal - A currency value ranging from -263 (or -922,337,203,685,477.5808) to 2 63 -1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit.
NChar	String - A fixed-length stream of Unicode characters ranging between 1 and 4,000 characters.
NText	String - A variable-length stream of Unicode data with a maximum length of 230 - 1 (or 1,073,741,823) characters.
NVarChar	String - A variable-length stream of Unicode characters ranging between 1 and 4,000 characters. Note   Implicit conversion fails if the string is greater than 4,000 characters. Explicitly set the object when working with strings longer than 4,000 characters.
Real	Single - A floating point number within the range of -3.40E +38 through 3.40E +38.
SmallDateTime	DateTime - Date and time data ranging in value from January 1, 1900 to June 6, 2079 to an accuracy of one minute.
SmallInt	Int16 - A 16-bit signed integer.
SmallMoney	Decimal - A currency value ranging from -214,748.3648 to +214,748.3647 with an accuracy to a ten-thousandth of a currency unit.
Text	String - A variable-length stream of non-Unicode data with a maximum length of 231 -1 (or 2,147,483,647) characters.
Timestamp	Array of type Byte  - Automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
TinyInt	Byte - An 8-bit unsigned integer.
UniqueIdentifier	Guid - A globally unique identifier (or GUID).
VarBinary	Array of type Byte - A variable-length stream of binary data ranging between 1 and 8,000 bytes. Note   Implicit conversion fails if the byte array is greater than 8,000 bytes. Explicitly set the object when working with byte arrays larger than 8,000 bytes.
VarChar (String)	String - A variable-length stream of non-Unicode characters ranging between 1 and 8,000 characters.
Variant	Object - A special data type that can contain numeric, string, binary, or date data as well as the SQL Server values Empty and Null, which is assumed if no other type is declared.

 

OleDb Data Type

 

Member name	Description
BigInt	A 64-bit signed integer (DBTYPE_I8). This maps to Int64.
Binary	A stream of binary data (DBTYPE_BYTES). This maps to an Array of type Byte.
Boolean	A Boolean value (DBTYPE_BOOL). This maps to Boolean.
BSTR	A null-terminated character string of Unicode characters (DBTYPE_BSTR). This maps to String.
Char	A character string (DBTYPE_STR). This maps to String.
Currency	A currency value ranging from -263 (or -922,337,203,685,477.5808) to 2 63 -1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit (DBTYPE_CY). This maps to Decimal.
Date (Date)	Date data, stored as a double (DBTYPE_DATE). The whole portion is the number of days since December 30, 1899, while the fractional portion is a fraction of a day. This maps to DateTime.
DBDate	Date data in the format yyyymmdd (DBTYPE_DBDATE). This maps to DateTime.
DBTime	Time data in the format hhmmss (DBTYPE_DBTIME). This maps to TimeSpan.
DBTimeStamp	Data and time data in the format yyyymmddhhmmss (DBTYPE_DBTIMESTAMP). This maps to DateTime.
Decimal	A fixed precision and scale numeric value between -1038 -1 and 10 38 -1 (DBTYPE_DECIMAL). This maps to Decimal.
Double	A floating point number within the range of -1.79E +308 through 1.79E +308 (DBTYPE_R8). This maps to Double.
Empty	No value (DBTYPE_EMPTY).
Error	A 32-bit error code (DBTYPE_ERROR). This maps to Exception.
Filetime	A 64-bit unsigned integer representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME). This maps to DateTime.
Guid	A globally unique identifier (or GUID) (DBTYPE_GUID). This maps to Guid.
IDispatch	A pointer to an IDispatch interface (DBTYPE_IDISPATCH). This maps to Object. Note   This data type is not currently supported by ADO.NET. Usage may cause unpredictable results.
Integer (Integer)	A 32-bit signed integer (DBTYPE_I4). This maps to Int32.
IUnknown	A pointer to an IUnknown interface (DBTYPE_UNKNOWN). This maps to Object. Note   This data type is not currently supported by ADO.NET. Usage may cause unpredictable results.
LongVarBinary	A long binary value (OleDbParameter only). This maps to an Array of type Byte.
LongVarChar	A long string value (OleDbParameter only). This maps to String.
LongVarWChar	A long null-terminated Unicode string value (OleDbParameter only). This maps to String.
Numeric	An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC). This maps to Decimal.
PropVariant	An automation PROPVARIANT (DBTYPE_PROP_VARIANT). This maps to Object.
Single	A floating point number within the range of -3.40E +38 through 3.40E +38 (DBTYPE_R4). This maps to Single.
SmallInt	A 16-bit signed integer (DBTYPE_I2). This maps to Int16.
TinyInt	A 8-bit signed integer (DBTYPE_I1). This maps to SByte.
UnsignedBigInt	A 64-bit unsigned integer (DBTYPE_UI8). This maps to UInt64.
UnsignedInt	A 32-bit unsigned integer (DBTYPE_UI4). This maps to UInt32.
UnsignedSmallInt	A 16-bit unsigned integer (DBTYPE_UI2). This maps to UInt16.
UnsignedTinyInt	A 8-bit unsigned integer (DBTYPE_UI1). This maps to Byte.
VarBinary	A variable-length stream of binary data (OleDbParameter only). This maps to an Array of type Byte.
VarChar (String)	A variable-length stream of non-Unicode characters (OleDbParameter only). This maps to String.
Variant	A special data type that can contain numeric, string, binary, or date data, as well as the special values Empty and Null (DBTYPE_VARIANT). This type is assumed if no other is specified. This maps to Object.
VarNumeric	A variable-length numeric value (OleDbParameter only). This maps to Decimal.
VarWChar	A variable-length, null-terminated stream of Unicode characters (OleDbParameter only). This maps to String.
WChar	A null-terminated stream of Unicode characters (DBTYPE_WSTR). This maps to String.

 

Odbc Data Type

 

Member name	Description
BigInt	Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0 (signed: 2[63] <= n <= 2[63] 1, unsigned:0 <= n <= 2[64] 1) (SQL_BIGINT). This maps to Int64.
Binary	A stream of binary data (SQL_BINARY). This maps to an Array of type Byte.
Bit	Single bit binary data (SQL_BIT). This maps to Boolean.
Char	A fixed-length character string (SQL_CHAR). This maps to String.
Date (Date)	Date data in the format yyyymmdd (SQL_TYPE_DATE). This maps to DateTime.
DateTime	Date data in the format yyyymmddhhmmss (SQL_TYPE_TIMESTAMP). This maps to DateTime.
Decimal	Signed, exact, numeric value with a precision of at least p and scale s, where 1 <= p <= 15 and s <= p. The maximum precision is driver-specific (SQL_DECIMAL). This maps to Decimal.
Double	Signed, approximate, numeric value with a binary precision 53 (zero or absolute value 10[308] to 10[308]) (SQL_DOUBLE). This maps to Double.
Image	Variable length binary data. Maximum length is data sourcedependent (SQL_LONGVARBINARY). This maps to an Array of type Byte.
Int (Integer)	Exact numeric value with precision 10 and scale 0 (signed: 2[31] <= n <= 2[31] 1, unsigned:0 <= n <= 2[32] 1) (SQL_INTEGER). This maps to Int32.
NChar	Unicode character string of fixed string length (SQL_WCHAR). This maps to String.
NText	Unicode variable-length character data. Maximum length is data sourcedependent. (SQL_WLONGVARCHAR). This maps to String.
Numeric	Signed, exact, numeric value with a precision p and scale s, where 1 <= p <= 15, and s <= p (SQL_NUMERIC). This maps to Decimal.
NVarChar	A variable-length stream of Unicode characters (SQL_WVARCHAR). This maps to String.
Real	Signed, approximate, numeric value with a binary precision 24 (zero or absolute value 10[38] to 10[38]).(SQL_REAL). This maps to Single.
SmallDateTime	Data and time data in the format yyyymmddhhmmss (SQL_TYPE_TIMESTAMP). This maps to DateTime.
SmallInt	Exact numeric value with precision 5 and scale 0 (signed: 32,768 <= n <= 32,767, unsigned: 0 <= n <= 65,535) (SQL_SMALLINT). This maps to Int16.
Text	Variable length character data. Maximum length is data sourcedependent (SQL_LONGVARCHAR). This maps to String.
Time	Date data in the format hhmmss (SQL_TYPE_TIMES). This maps to DateTime.
Timestamp	A stream of binary data (SQL_BINARY). This maps to an Array of type Byte.
TinyInt	Exact numeric value with precision 3 and scale 0 (signed: 128 <= n <= 127, unsigned:0 <= n <= 255)(SQL_TINYINT). This maps to Byte.
UniqueIdentifier	A fixed-length, globally unique identifier (GUID) (SQL_GUID). This maps to Guid.
VarBinary	Variable length binary. The maximum is set by the user (SQL_VARBINARY). This maps to an Array of type Byte.
VarChar (String)	A variable-length stream character string (SQL_CHAR). This maps to String.

 

OracleClient Data Type

 

Member name	Description
BFile	An Oracle BFILE data type that contains a reference to binary data with a maximum size of 4 gigabytes that is stored in an external file. Use the OracleClient OracleBFile data type in OracleParameter.Value.
Blob	An Oracle BLOB data type that contains binary data with a maximum size of 4 gigabytes. Use the OracleClient OracleLob data type in OracleParameter.Value.
Byte	An integral type representing unsigned 8-bit integers with values between 0 and 255. This is not a native Oracle data type, but is provided to improve performance when binding input parameters. Use the .NET Framework Byte data type in OracleParameter.Value.
Char	An Oracle CHAR data type that contains a fixed-length character string with a maximum size of 2,000 bytes. Use the .NET Framework String or OracleClient OracleStringdata type in OracleParameter.Value.
Clob	An Oracle CLOB data type that contains character data, based on the default character set on the server, with a maximum size of 4 gigabytes. Use the OracleClient OracleLobdata type in OracleParameter.Value.
Cursor	An Oracle REF CURSOR. The OracleDataReaderobject is not available.
DateTime (Date)	An Oracle DATE data type that contains a fixed-length representation of a date and time, ranging from January 1, 4712 BC. to December, 31, 4712 AD., with the default format dd-mmm-yy. For AD. dates, this maps to DateTime. To bind BC. dates, use a String parameter and the Oracle TO_DATE or TO_CHAR conversion functions for input and output parameters respectively. Use the .NET Framework DateTime or OracleClient OracleDateTimedata type in OracleParameter.Value.
Double	A double-precision floating-point value. This is not a native Oracle data type, but is provided to improve performance when binding input parameters. For information about conversion of Oracle numeric values to common language runtime data types, see OracleNumber. Use the .NET Framework Doubleor OracleClient OracleNumber data type in OracleParameter.Value.
Float	A single-precision floating-point value. This is not a native Oracle data type, but is provided to improve performance when binding input parameters. For information about conversion of Oracle numeric values to common language runtime data types, see OracleNumber. Use the .NET Framework Singleor OracleClient OracleNumber data type in OracleParameter.Value.
Int16	An integral type representing signed 16-bit integers with values between -32768 and 32767. This is not a native Oracle data type, but is provided to improve performance when binding input parameters. For information about conversion of Oracle numeric values to common language runtime data types, see OracleNumber. Use the .NET Framework Int16or OracleClient OracleNumber data type in OracleParameter.Value.
Int32	An integral type representing signed 32-bit integers with values between -2147483648 and 2147483647. This is not a native Oracle data type, but is provided for performance when binding input parameters. For information about conversion of Oracle numeric values to common language runtime data types, see OracleNumber. Use the .NET Framework Int32or OracleClient OracleNumber data type in OracleParameter.Value.
IntervalDayToSecond	An Oracle INTERVAL DAY TO SECOND data type (Oracle 9i or later) that contains an interval of time in days, hours, minutes, and seconds, and has a fixed size of 11 bytes. Use the .NET Framework TimeSpan or OracleClient OracleTimeSpandata type in OracleParameter.Value.
IntervalYearToMonth	An Oracle INTERVAL YEAR TO MONTH data type (Oracle 9i or later) that contains an interval of time in years and months, and has a fixed size of 5 bytes. Use the .NET Framework Int32or OracleClient OracleMonthSpandata type in OracleParameter.Value.
LongRaw	An Oracle LONGRAW data type that contains variable-length binary data with a maximum size of 2 gigabytes. Use the .NET Framework Byte[] or OracleClient OracleBinarydata type in OracleParameter.Value.
LongVarChar	An Oracle LONG data type that contains a variable-length character string with a maximum size of 2 gigabytes. Use the .NET Framework String or OracleClient OracleStringdata type in OracleParameter.Value.
NChar	An Oracle NCHAR data type that contains fixed-length character string to be stored in the national character set of the database, with a maximum size of 2,000 bytes (not characters) when stored in the database. Note: The size of the value is dependent on the national character set of the database. See your Oracle documentation for more information. Use the .NET Framework String or OracleClient OracleStringdata type in OracleParameter.Value.
NClob	An Oracle NCLOB data type that contains character data to be stored in the national character set of the database, with a maximum size of 4 gigabytes (not characters) when stored in the database. Note: The size of the value is dependent on the national character set of the database. See your Oracle documentation for more information. Use the OracleLobdata type in OracleParameter.Value.
Number (Integer)	An Oracle NUMBER data type that contains variable-length numeric data with a maximum precision and scale of 38. This maps to Decimal. To bind an Oracle NUMBER that exceeds what Decimal.MaxValuecan contain, either use an OracleNumberdata type, or use a String parameter and the Oracle TO_NUMBER or TO_CHAR conversion functions for input and output parameters respectively. Use the .NET Framework Decimal or OracleClient OracleNumber data type in OracleParameter.Value.
NVarChar	An Oracle NVARCHAR2 data type that contains a variable-length character string stored in the national character set of the database, with a maximum size of 4,000 bytes (not characters) when stored in the database. Note: The size of the value is dependent on the national character set of the database. See your Oracle documentation for more information. Use the .NET Framework Stringor OracleClient OracleStringdata type in OracleParameter.Value.
Raw	An Oracle RAW data type that contains variable-length binary data with a maximum size of 2,000 bytes. Use the .NET Framework Byte[] or OracleClient OracleBinarydata type in OracleParameter.Value.
RowId	The base64 string representation of an Oracle ROWID data type. Use the .NET Framework String or OracleClient OracleStringdata type in OracleParameter.Value.
SByte	An integral type representing signed 8 bit integers with values between -128 and 127. This is not a native Oracle data type, but is provided to improve performance when binding input parameters. Use the .NET Framework SByteor OracleClient y data type in OracleParameter.Value.
Timestamp	An Oracle TIMESTAMP (Oracle 9i or later) that contains date and time (including seconds), and ranges in size from 7 to 11 bytes. Use the .NET Framework DateTimeor OracleClient OracleDateTimedata type in OracleParameter.Value.
TimestampLocal	An Oracle TIMESTAMP WITH LOCAL TIMEZONE (Oracle 9i or later) that contains date, time, and a reference to the original time zone, and ranges in size from 7 to 11 bytes. Use the .NET Framework DateTime or OracleClient OracleDateTimedata type in OracleParameter.Value.
TimestampWithTZ	An Oracle TIMESTAMP WITH TIMEZONE (Oracle 9i or later) that contains date, time, and a specified time zone, and has a fixed size of 13 bytes. Use the .NET Framework DateTimeor OracleClient OracleDateTimedata type in OracleParameter.Value.
UInt16	An integral type representing unsigned 16-bit integers with values between 0 and 65535. This is not a native Oracle data type, but is provided to improve performance when binding input parameters. For information about conversion of Oracle numeric values to common language runtime data types, see OracleNumber. Use the .NET Framework UInt16or OracleClient OracleNumber data type in OracleParameter.Value.
UInt32	An integral type representing unsigned 32-bit integers with values between 0 and 4294967295. This is not a native Oracle data type, but is provided to improve performance when binding input parameters. For information about conversion of Oracle numeric values to common language runtime data types, see OracleNumber. Use the .NET Framework UInt32or OracleClient OracleNumber data type in OracleParameter.Value.
VarChar (String)	An Oracle VARCHAR2 data type that contains a variable-length character string with a maximum size of 4,000 bytes. Use the .NET Framework String or OracleClient OracleStringdata type in OracleParameter.Value.

 

MySql Data Type

 

Member name
Blob
Byte
Date (Date)
Datetime
Decimal
Double
Enum
Float
Geometry
Int16
Int24
Int32 (Integer)
Int64
LongBlob
MediumBlob
Newdate
Set
String (String)
Time
Timestamp
TinyBlob
VarChar
Year
VarChar

 

---

Send comments on this topic.
Copyright 1998-2006 ASP-db