● Page updated 2010-08-10 |
● Unique visits since 2006-10-15:
|
● Contact
|
|
|
.Net
| Excel Data Import | ||||
| OleDb | {format} |
Microsoft Enterprise Library post (link)
| Specifier | Format of Return Value |
|---|---|
| N | 32 digits: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| D | 32 digits separated by hyphens: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |
| B | 32 digits separated by hyphens, enclosed in brackets:
{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx} |
| P | 32 digits separated by hyphens, enclosed in parentheses:
(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx) |
Keyword equivalents in dotNet languages C# and VB.Net
| Keyword | VB | C# |
| Overridable | Virtual | |
| NotInheritable | Sealed |
To import data from an Excel spreadsheet into a dotNet dataset use Microsoft Jet 4.0 OLE DB Provider. FExcelImport in alice.VAT.Common imports data from Excel spreadsheet into an SQL table. This procedure has some problems with numeric data formatting as it automatically assigns a numeric data type to the column.
To have a reliable import proceudure, use MS Excel Libraries to open a Workbook and then read Cells from Sheets. Also see Excel link in Developer section.
'Microsoft Ado.Net transaction/transactions example. Dim s_con As String = Alice.Application.Application.ConnectionStringTransactions Dim con As New OleDb.OleDbConnection(s_con) Dim tx As OleDb.OleDbTransaction tx = con.BeginTransaction() Dim da As New OleDb.OleDbDataAdapter(s_sql, con) Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter1) With cb '.UpdateCommand.Transaction = tx .GetUpdateCommand.Transaction = tx '.InsertCommand.Transaction = tx .GetInsertCommand.Transaction = tx '.DeleteCommand.Transaction = tx .GetDeleteCommand.Transaction = tx '.Update(DsReconciliation1.BANKFILESPAYMENTS) End With da.Update(DsReconciliation1.BANKFILESPAYMENTS) tx.Commit() 'tx.Rollback() con.Close()
C1 - ComponentOne component tips:
Set C1 TrueDBDataGrid column width:
dg_bankfilespayments.Splits(0).DisplayColumns("Select").Width = 30
Display the Group By area:
dg.DataView = C1.Win.C1TrueDBGrid.DataViewEnum.GroupBy
Set column autosize:
dg.Splits(0).DisplayColumns(0).AutoSize()
OleDb
OleDb does not support named parameters. "?" must be used for parameter name instead of "@parameter_name".
ToString() formats: dot Net standard numeric formats
| Format specifier | Name | Description |
|---|---|---|
| C or c | Currency | The number is converted to a string that represents a
currency amount. The conversion is controlled by the currency format
information of the
|
| D or d | Decimal | This format is supported for integral types only. The number is converted to a string of decimal digits (0-9), prefixed by a minus sign if the number is negative. The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier. |
| E or e | Scientific (exponential) | The number is converted to a string of the form "-d.ddd...E+ddd" or "-d.ddd...e+ddd", where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. One digit always precedes the decimal point. The precision specifier indicates the desired number of digits after the decimal point. If the precision specifier is omitted, a default of six digits after the decimal point is used. The case of the format specifier indicates whether to prefix the exponent with an 'E' or an 'e'. The exponent always consists of a plus or minus sign and a minimum of three digits. The exponent is padded with zeros to meet this minimum, if required. |
| F or f | Fixed-point | The number is converted to a string of the form "-ddd.ddd..." where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by the NumberFormatInfo is used. |
| G or g | General | The number is converted to the most compact of either
fixed-point or scientific notation, depending on the type of the number
and whether a precision specifier is present. If the precision specifier
is omitted or zero, the type of the number determines the default
precision, as indicated by the following list.
Fixed-point notation is used if the exponent that would result from expressing the number in scientific notation is greater than -5 and less than the precision specifier; otherwise, scientific notation is used. The result contains a decimal point if required and trailing zeroes are omitted. If the precision specifier is present and the number of significant digits in the result exceeds the specified precision, then the excess trailing digits are removed by rounding. If scientific notation is used, the exponent in the result is prefixed with 'E' if the format specifier is 'G', or 'e' if the format specifier is 'g'. The exception to the preceding rule is if the number is a Decimal and the precision specifier is omitted. In that case, fixed-point notation is always used and trailing zeroes are preserved. |
| N or n | Number | The number is converted to a string of the form "-d,ddd,ddd.ddd...", where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. Thousand separators are inserted between each group of three digits to the left of the decimal point. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by the NumberFormatInfo is used. |
| P or p | Percent | The number is converted to a string that represents a
percent as defined by the
|
| R or r | Round-trip | The round-trip specifier guarantees that a numeric value converted to a string will be parsed back into the same numeric value. When a numeric value is formatted using this specifier, it is first tested using the general format, with 15 spaces of precision for a Double and 7 spaces of precision for a Single. If the value is successfully parsed back to the same numeric value, it is formatted using the general format specifier. However, if the value is not successfully parsed back to the same numeric value, then the value is formatted using 17 digits of precision for a Double and 9 digits of precision for a Single. Although a precision specifier can be appended to the round-trip format specifier, it is ignored. Round trips are given precedence over precision when using this specifier. This format is supported by floating-point types only. |
| X or x | Hexadecimal | The number is converted to a string of hexadecimal digits. The case of the format specifier indicates whether to use uppercase or lowercase characters for the hexadecimal digits greater than 9. For example, use 'X' to produce "ABCDEF", and 'x' to produce "abcdef". The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier. This format is supported for integral types only. |
| Format specifier | Description |
|---|---|
| d | Displays the current day of the month, measured as a
number between 1 and 31, inclusive. If the day is a single digit only
(1-9), then it is displayed as a single digit.
Note that if the 'd' format specifier is used alone, without other custom format strings, it is interpreted as the standard short date pattern format specifier. If the 'd' format specifier is passed with other custom format specifiers or the '%' character, it is interpreted as a custom format specifier. |
| dd | Displays the current day of the month, measured as a number between 1 and 31, inclusive. If the day is a single digit only (1-9), it is formatted with a preceding 0 (01-09). |
| ddd | Displays the abbreviated name of the day for the
specified DateTime. If a specific valid
format
provider (a non-null object that implements
|
| dddd (plus any number of additional "d" characters) | Displays the full name of the day for the specified
DateTime. If a specific valid
format
provider (a non-null object that implements IFormatProvider with
the expected property) is not supplied, then the
|
| f | Displays seconds fractions represented in one digit.
Note that if the 'f' format specifier is used alone, without other custom format strings, it is interpreted as the full (long date + short time) format specifier. If the 'f' format specifier is passed with other custom format specifiers or the '%' character, it is interpreted as a custom format specifier. |
| ff | Displays seconds fractions represented in two digits. |
| fff | Displays seconds fractions represented in three digits. |
| ffff | Displays seconds fractions represented in four digits. |
| fffff | Displays seconds fractions represented in five digits. |
| ffffff | Displays seconds fractions represented in six digits. |
| fffffff | Displays seconds fractions represented in seven digits. |
| g or gg (plus any number of additional "g" characters) | Displays the era (A.D. for example) for the specified
DateTime. If a specific valid
format
provider (a non-null object that implements IFormatProvider with
the expected property) is not supplied, then the era is determined from
the calendar associated with the DateTimeFormat and its current
culture associated with the current thread.
Note that if the 'g' format specifier is used alone, without other custom format strings, it is interpreted as the standard general format specifier. If the 'g' format specifier is passed with other custom format specifiers or the '%' character, it is interpreted as a custom format specifier. |
| h | Displays the hour for the specified DateTime in the range 1-12. The hour represents whole hours passed since either midnight (displayed as 12) or noon (also displayed as 12). If this format is used alone, then the same hour before or after noon is indistinguishable. If the hour is a single digit (1-9), it is displayed as a single digit. No rounding occurs when displaying the hour. For example, a DateTime of 5:43 returns 5. |
| hh, hh (plus any number of additional "h" characters) | Displays the hour for the specified DateTime in the range 1-12. The hour represents whole hours passed since either midnight (displayed as 12) or noon (also displayed as 12). If this format is used alone, then the same hour before or after noon is indistinguishable. If the hour is a single digit (1-9), it is formatted with a preceding 0 (01-09). |
| H | Displays the hour for the specified DateTime in the range 0-23. The hour represents whole hours passed since midnight (displayed as 0). If the hour is a single digit (0-9), it is displayed as a single digit. |
| HH, HH (plus any number of additional "H" characters) | Displays the hour for the specified DateTime in the range 0-23. The hour represents whole hours passed since midnight (displayed as 0). If the hour is a single digit (0-9), it is formatted with a preceding 0 (01-09). |
| m | Displays the minute for the specified DateTime in
the range 0-59. The minute represents whole minutes passed since the
last hour. If the minute is a single digit (0-9), it is displayed as a
single digit.
Note that if the 'm' format specifier is used alone, without other custom format strings, it is interpreted as the standard month day pattern format specifier. If the 'm' format specifier is passed with other custom format specifiers or the '%' character, it is interpreted as a custom format specifier. |
| mm, mm (plus any number of additional "m" characters) | Displays the minute for the specified DateTime in the range 0-59. The minute represents whole minutes passed since the last hour. If the minute is a single digit (0-9), it is formatted with a preceding 0 (01-09). |
| M | Displays the month, measured as a number between 1 and
12, inclusive. If the month is a single digit (1-9), it is displayed as
a single digit.
Note that if the 'M' format specifier is used alone, without other custom format strings, it is interpreted as the standard month day pattern format specifier. If the 'M' format specifier is passed with other custom format specifiers or the '%' character, it is interpreted as a custom format specifier. |
| MM | Displays the month, measured as a number between 1 and 12, inclusive. If the month is a single digit (1-9), it is formatted with a preceding 0 (01-09). |
| MMM | Displays the abbreviated name of the month for the
specified DateTime. If a specific valid
format
provider (a non-null object that implements IFormatProvider with
the expected property) is not supplied, the
|
| MMMM | Displays the full name of the month for the specified
DateTime. If a specific valid
format
provider (a non-null object that implements IFormatProvider with
the expected property) is not supplied, then the
|
| s | Displays the seconds for the specified DateTime
in the range 0-59. The second represents whole seconds passed since the
last minute. If the second is a single digit (0-9), it is displayed as a
single digit only.
Note that if the 's' format specifier is used alone, without other custom format strings, it is interpreted as the standard sortable date/time pattern format specifier. If the 's' format specifier is passed with other custom format specifiers or the '%' character, it is interpreted as a custom format specifier. |
| ss, ss (plus any number of additional "s" characters) | Displays the seconds for the specified DateTime in the range 0-59. The second represents whole seconds passed since the last minute. If the second is a single digit (0-9), it is formatted with a preceding 0 (01-09). |
| t | Displays the first character of the A.M./P.M. designator
for the specified DateTime. If a specific valid
format
provider (a non-null object that implements IFormatProvider with
the expected property) is not supplied, then the
Note that if the 't' format specifier is used alone, without other custom format strings, it is interpreted as the standard long time pattern format specifier. If the 't' format specifier is passed with other custom format specifiers or the '%' character, it is interpreted as a custom format specifier. |
| tt, tt (plus any number of additional "t" characters) | Displays the A.M./P.M. designator for the specified DateTime. If a specific valid format provider (a non-null object that implements IFormatProvider with the expected property) is not supplied, then the AMDesignator (or PMDesignator) property of the DateTimeFormat and its current culture associated with the current thread is used. Otherwise, the AMDesignator (or PMDesignator) property from the specified IFormatProvider is used. If the total number of whole hours passed for the specified DateTime is less than 12, then the AMDesignator is used. Otherwise, the PMDesignator is used. |
| y | Displays the year for the specified DateTime as a
maximum two-digit number. The first two digits of the year are omitted.
If the year is a single digit (1-9), it is displayed as a single digit.
Note that if the 'y' format specifier is used alone, without other custom format strings, it is interpreted as the standard short date pattern format specifier. If the 'y' format specifier is passed with other custom format specifiers or the '%' character, it is interpreted as a custom format specifier. |
| yy | Displays the year for the specified DateTime as a maximum two-digit number. The first two digits of the year are omitted. If the year is a single digit (1-9), it is formatted with a preceding 0 (01-09). |
| yyyy | Displays the year for the specified DateTime, including the century. If the year is less than four digits in length, then preceding zeros are appended as necessary to make the displayed year four digits long. |
| z | Displays the time zone offset for the system's current time zone in whole hours only. The offset is always displayed with a leading sign (zero is displayed as "+0"), indicating hours ahead of Greenwich mean time (+) or hours behind Greenwich mean time (-). The range of values is –12 to +13. If the offset is a single digit (0-9), it is displayed as a single digit with the appropriate leading sign. The setting for the time zone is specified as +X or –X where X is the offset in hours from GMT. The displayed offset is affected by daylight savings time. |
| zz | Displays the time zone offset for the system's current time zone in whole hours only. The offset is always displayed with a leading or trailing sign (zero is displayed as "+00"), indicating hours ahead of Greenwich mean time (+) or hours behind Greenwich mean time (-). The range of values is –12 to +13. If the offset is a single digit (0-9), it is formatted with a preceding 0 (01-09) with the appropriate leading sign. The setting for the time zone is specified as +X or –X where X is the offset in hours from GMT. The displayed offset is affected by daylight savings time. |
| zzz, zzz (plus any number of additional "z" characters) | Displays the time zone offset for the system's current time zone in hours and minutes. The offset is always displayed with a leading or trailing sign (zero is displayed as "+00:00"), indicating hours ahead of Greenwich mean time (+) or hours behind Greenwich mean time (-). The range of values is –12:00 to +13:00. If the offset is a single digit (0-9), it is formatted with a preceding 0 (01-09) with the appropriate leading sign. The setting for the time zone is specified as +X or –X where X is the offset in hours from GMT. The displayed offset is affected by daylight savings time. |
| : | Time separator. |
| / | Date separator. |
| " | Quoted string. Displays the literal value of any string between two quotation marks preceded by the escape character (/). |
| ' | Quoted string. Displays the literal value of any string between two " ' " characters. |
| %c | Where c is both a standard format specifier and a
custom format specifier, displays the custom format pattern associated
with the format specifier.
Note that if a format specifier is used alone as a single character, it is interpreted as a standard format specifier. Only format specifiers consisting of two or more characters are interpreted as custom format specifiers. In order to display the custom format for a specifier defined as both a standard and a custom format specifier, precede the specifier with a % symbol. |
| \c | Where c is any character, the escape character displays the next character as a literal. The escape character cannot be used to create an escape sequence (like "\n" for new line) in this context. |
| Any other character | Other characters are written directly to the result string as literals. |
Miliseconds
use 0 for parameter and for 'format' put any of the letters in the numeric or date category (format pattern). Examples: {0:d} = short date, {0:N} = numeric.
| Format Pattern | Name | Example |
|---|---|---|
d | Short date format | 8/27/1989 |
D | Long date format | Sunday, August 27, 1989 |
t | Short time format | 3:32 PM |
T | Long time format | 3:32:00 PM |
f | Full date/time format (short time) | Sunday, August 27, 1989 3:32 PM |
f | Full date/time format (long time) | Sunday, August 27, 1989 3:32:00 PM |
g | General date/time format (short time) | 8/27/1989 3:32 PM |
G | General date/time format (long time) | 8/27/1989 3:32:00 PM |
m or M | Month day format | August 27 |
r or R | RFC 1123 format | Sun, 27 Aug 1989 8:32:00 GMT |
s | Sortable date/time format | 1989-08-27T15:32:00 |
u | Universable sortable date/time format | 1989-08-27 15:32:00z |
U | Universable sortable date/time format | Sunday, August 27, 1989 11:32:00 PM |
y or Y | Year month format | August, 1989 |
| Format Name | Description |
|---|---|
| General Date, or G | Displays a date and/or time. For example, 4/3/93 05:34 PM.
Date display is determined by your system's LocaleID value. |
| Long Date, Medium Date, or D | Displays a date according to your locale's long date format. |
| Short Date, or d | Displays a date using your locale's short date format. |
| Long Time, Medium Time, or T | Displays a time using your locale's long time format; typically includes hours, minutes, seconds. |
| Short Time, or t | Displays a time using your locale's short time format. |
| f | Displays the long date and short time according to your locale's format. |
| F | Displays the long date and long time according to your locale's format. |
| g | Displays the short date and short time according to your locale's format. |
| M, m | Displays the month and the day of a date. |
| R, r | Formats the date and time as Greenwich Mean Time (GMT). |
| s | Formats the date and time as a sortable index. |
| u | Formats the date and time as a GMT sortable index. |
| U | Formats the date and time with the long date and long time as GMT. |
| Y, y | Formats the date as the year and month. |
