Microsoft KB Archive/274255

= PRB: ADOXCE Error Message: &quot;..Specified type was invalid&quot; for SQL Server CE Table =

Article ID: 274255

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition

-



This article was previously published under Q274255



SYMPTOMS
If you use Microsoft CE ActiveX Data Objects (ADOXCE) 3.1 for DDL to create a table in a SQL Server CE database, and you do not specify a data type, the following error message occurs:

Err.Number : -2147217859

Err.Description : A specified type was invalid



CAUSE
When you do not specify a data type, ADOXCE interprets the column as adVarWChar (type 202). Because the length is not specfied when you use the DefinedSize property for the column, the SQL Server CE engine reports an error.



RESOLUTION
Always specify the correct data type in the Type property. Also specify the length of data, the DefinedSize property, where appropriate.



STATUS
This behavior is by design.



MORE INFORMATION
The following code demonstrates the error. Replace the data source name in the connection string with an existing database name.

Dim cat, tbl, col, stTableName, strConn

Set cat = CreateObject(&quot;adoxce.catalog.3.1&quot;) Set col = CreateObject(&quot;adoxce.column.3.1&quot;) Set tbl = CreateObject(&quot;adoxce.table.3.1&quot;)

strConn = &quot;Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data Source=\Test.sdf;&quot;

col.Name = &quot;Col1&quot; ' default Type is 202 adVarWChar, and DefinedSize is not specified

tbl.Name = &quot;temp_table&quot; tbl.Columns.Append col cat.ActiveConnection = strConn If adox_ExistsTable(cat, tbl.Name) Then cat.Tables.Delete tbl.Name End If   On Error Resume Next cat.Tables.Append tbl '   'An error was encountered while running this program, A specified type was invalid Debug.Print Hex(Err.Number) & &quot; - &quot; & Err.Description

'Err.Number : -2147217859 'Err.Description : A specified type was invalid The same error occurs if the length was not specified, that is:

col.Type = 202 'which is adVarWChar

col.Name = &quot;Col1&quot;

tbl.Name = &quot;temp_table&quot;

tbl.Columns.Append col

The following code corrects the problem and creates the table. Replace the data source name in the connection string with an existing database name. Dim cat, tbl, col, stTableName, strConn

Set cat = CreateObject(&quot;adoxce.catalog.3.1&quot;) Set col = CreateObject(&quot;adoxce.column.3.1&quot;) Set tbl = CreateObject(&quot;adoxce.table.3.1&quot;)

strConn = &quot;Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data Source=\Test.sdf;&quot; col.Type = 202 'which is adVarWChar col.Name = &quot;Col1&quot; col.DefinedSize = 10

tbl.Name = &quot;temp_table&quot; tbl.Columns.Append col cat.ActiveConnection = strConn If adox_ExistsTable(cat, tbl.Name) Then cat.Tables.Delete tbl.Name End If   On Error Resume Next cat.Tables.Append tbl Debug.Print Hex(Err.Number) & &quot; - &quot; & Err.Description

'Err.Number : 0 'Err.Description : Empty

