SQL type 'datetime' is not registered

Alexander Staroselsky asked on March 14, 2017 19:03

Hello,

When trying to save a form with the Forms application, the following error is being generated when any of the fields have a "Date" or "Date and time" Data type.

Where should this data type be "registered"? This is occurring on Kentico version v10.0.8

Thank you for any help you can provide.

Here is the full error from the Event Log:

Message: [DataTypeManager.GetFieldType]: SQL type 'datetime' is not registered, register the type with method RegisterDataTypes(...)

Exception type: System.NotSupportedException
Stack trace: 
at CMS.DataEngine.DataTypeManager.GetFieldType(String sqlType, Int32 size, Boolean throwException)
at CMS.DataEngine.FieldBase`1.LoadFromTableData(DataRow row, Boolean isPrimary, Boolean isSystem)
at CMS.FormEngine.FormFieldInfo.LoadFromTableData(DataRow row, Boolean isPrimary, Boolean isSystem)
at CMS.DataEngine.DataDefinition.LoadFromDataStructure(String tableName, TableManager manager, Boolean isSystem)
at CMS.DataEngine.TableManager.GetDatabaseFields(String tableName)
at CMS.DataEngine.TableManager.GetOldFields(String tableName, String definition, Boolean loadOldDefinition)
at CMS.DataEngine.TableManager.UpdateTableByDefinition(UpdateTableParameters parameters)
at CMS.DataEngine.DataClassInfoProvider.EnsureDatabaseStructure(DataClassInfo classInfo, Boolean updateSystemFields)
at CMS.DataEngine.DataClassInfoProvider.SetDataClassInfoInternal(DataClassInfo infoObj)
at CMS.DataEngine.DataClassInfoProviderBase`1.SetDataClassInfo(DataClassInfo infoObj)
at CMSModules_AdminControls_Controls_Class_FieldEditor_FieldEditor.UpdateDependencies(DataClassInfo dci, TableManager tm, FormFieldInfo updatedFieldInfo, Boolean& updateInheritedForms) in c:\inetpub\wwwroot\KenticoCMS10\CMS\CMSModules\AdminControls\Controls\Class\FieldEditor\FieldEditor.ascx.cs:line 3721

Recent Answers

Mariia Hrytsai answered on March 15, 2017 10:48

Please check your sql columns types.

If in Kentico for some column you select Data type 'Date and Time' with precision 7(by default), so in sql it should be datetime2(7).

If in Kentico Data type is 'Date' - sql type is date.

So looks like your column type in sql is 'datetime' and this type is not recognized by Kentico as it is not registered as Kentico type.

3 votesVote for this answer Mark as a Correct answer

Matthew Henninger answered on May 23, 2017 15:47 (last edited on May 23, 2017 16:23)

I have seen this a few times so I am going to give an example of how to fix it using SQL.

You need to drop the constraints on the fields if there are any then change the column to datetime2. Then you just add the constraints back.

Remember to back the db up before doing something like this.

Here is an example of how to change the FormInserted and FormUpdated fields in a form.

ALTER TABLE [<Table_Name>]
    DROP
        CONSTRAINT [DEFAULT_Form_<FormCodeName>_FormInserted],
        CONSTRAINT [DEFAULT_Form_<FormCodeName>_FormUpdated]

ALTER TABLE [<Table_Name>] 
    ALTER column FormInserted datetime2

ALTER TABLE [<Table_Name>] 
    ALTER column FormUpdated datetime2

ALTER TABLE [<Table_Name>] 
    ADD 
        CONSTRAINT [DEFAULT_Form_<FormCodeName>_FormInserted]  DEFAULT ('1/1/0001 12:00:00 AM') FOR [FormInserted],
        CONSTRAINT [DEFAULT_Form_<FormCodeName>_FormUpdated]  DEFAULT ('1/1/0001 12:00:00 AM') FOR [FormUpdated]

3 votesVote for this answer Mark as a Correct answer