Switches on/off auto typing mode or gets the current setting. See the remarks section for more information about the auto typing mode.

Syntax:

object.AutoType = value
b = object.autoType

Parameters:

The property is Boolean. True - auto typing mode is on, False (default) auto typing mode is off.

Examples:

Assume we have the following table declaration:

CREATE TABLE T (
  ID INTEGER PRIMARY KEY,
  A NUMERIC,
  B DATE,
  C INTEGER,
  D TEXT
);

and some data in it inserted for example this way::

... db open and other code ...
Set su = Server.CreateObject("newObjects.utilctls.StringUtilities")
db.Execute(su.Sprintf("INSERT INTO T (A,B,C,D) VALUES (%G,%M,%d,%q);", _
                     Request("A"),Request("B"),Request("C"),Request("D"))
... etc ...

By default a call to the Execute method like this:

Set r = db.Execute("SELECT * FROM T)

Will return result where the fields in each row are strings (vbString type). E.g. VarType(r(n)(m)) for each valid row number n and field index m will return 8 (which is vbString). If the auto type mode is on (this property set to True) the result will be different. For example:

Response.Write VarType(r(n)("A")) ' Will print 5 = vbDouble
Response.Write VarType(r(n)("ID")) ' Will print 3 = vbLong
Response.Write VarType(r(n)("B")) ' Will print 5 = vbDouble
Response.Write VarType(r(n)("C")) ' Will print 3 = vbLong
Response.Write VarType(r(n)("D")) ' Will print 8 = vbString

Remarks:

Auto typing mode instructs the SQLite COM object to try to distinguish the numeric from the textual values when the results of a query are packed into collections. The conversion is enforced and if it succeeds the value for the corresponding field in the collection is of the appropriate type. If the conversion fails the field's value is returned as string (like in auto typing mode off).

This is extremely important for applications that work in environments in which the locale settings conflict with the SQL language syntax. For example many locales (German, Russian, Bulgarian etc.) use comma "," for decimals separator symbol. In such case the VBScript's CDbl function, for example, will fail to convert such a field to number if it is returned as string.

Thus the auto type mode allows you to instruct the DB engine to convert columns and results of certain types as numeric values packed in VARIANT-s instead of returning plain strings that you need to convert further. This simplifies the work with these values and allows you use them in expressions in your application without the need to implement difficult locale independent conversions. 

Note that this will work fine only if you take care to to put into the database correct values according to the column types you defined. As SQLite is type-less it will permit you put any value in a field declared as NUMERIC or DOUBLE for example. When read back in auto typing mode the SQLite COM will try to convert it to a numeric value but if you inserted something that is not a number it will still be returned as string. This is relatively simple - you just need to take care how the SQL statements that insert or update data are constructed. Using the Sprintf method from the StringUtilities object allows it to be done in a single line without need of any additional code - i.e. the effect of just using Sprintf with format string appropriate for the table where you insert/update values is almost the same as the effect of the restrictions applied in data base engines with  strict typing. An error will occur if certain argument cannot be converted to the format specified and the statement will not be executed. Thus in the both cases you need to do the same if you want to implement certain prevention facilities that show the user error and force him/her to correct the entered data.

See SQLite types for more information and the default list of types that are treated as numeric. 

Applies to: SQLite COM object

See also: AddNumericType, RemoveNumericType, NumericType, CountNumericTypes

Supported on:

Windows 95/NT and later
Windows CE 3.0 and later
Pocket PC/Windows Mobile 2003 and later
Windows CE.NET 4 and later