Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Leon 101 posts 489 karma points
    Mar 29, 2015 @ 14:18
    Leon
    0

    Umbraco forms database table

    Hi.

    I'm using the umbraco forms on "localhost". When I try and submit the form, I get a database error that it can't find certain tables in the database. I'm using sql server. Any ideas?

    Leon

  • Jan Skovgaard 11280 posts 23678 karma points MVP 10x admin c-trib
    Mar 29, 2015 @ 19:31
    Jan Skovgaard
    0

    Hi Leon

    What is the exact error message that you're getting?

    And what version of Umbraco 7 are you using? How did you install forms?

    /Jan

  • Leon 101 posts 489 karma points
    Mar 30, 2015 @ 00:36
    Leon
    0

    Hi Jan.

    Umbraco version 7.2.3

    Installed the forms by using the umbraco "admin" section and clicking on forms.

    Current error message: System.Data.SqlClient.SqlException Invalid column name 'UniqueId'. Invalid column name 'RecordData'

    Background: THe initial error said it couldn't find a certain table in the database (can't remember which one now). I found a sql script to fix the error and ran that against my database. I now get the "Invalid column name" error.

    Is there a way I can find which tables I need, drop them and then run a script to recreate them correctly.

    I think the initial problem might have been caused because of the type of user I was logged in as in my connection string. I've since then switched to using the "sa" user, but no luck.

    Hope you can help.

    Leon

  • Jan Skovgaard 11280 posts 23678 karma points MVP 10x admin c-trib
    Mar 30, 2015 @ 05:15
    Jan Skovgaard
    0

    Hi Leon

    Where did you find the script? Yeah you should be able to just use something like DROP <tablename> FROM <database> - I think that you should probably try starting all over so you should try deleting all the tables related to the forms module I guess. But make sure to keep a backup before deleting anything.

    /Jan

  • Leon 101 posts 489 karma points
    Mar 30, 2015 @ 13:36
    Leon
    0

    Hi Jan.

    I will try what you suggested. Here is the sql script I used: https://our.umbraco.org/projects/umbraco-pro/contour/documentation/Installation/resources/createsqlserver

    Do you know if umbraco forms use any stored procedures that I need to drop first?

    Leon

  • Jan Skovgaard 11280 posts 23678 karma points MVP 10x admin c-trib
    Mar 30, 2015 @ 13:40
    Jan Skovgaard
    0

    Hi Leon

    Ok, but I'm not sure you can use the Contour SQL scripts since Forms is being rebuild from scratch - Don't know if the documentation on forms has been updated with SQL scripts yet but try having a look here https://github.com/umbraco/UmbracoFormsDocumentation

    Hope this helps.

    /Jan

  • Comment author was deleted

    Mar 30, 2015 @ 14:06

    Here is the create script for the Forms tables

     

    CREATE TABLE [dbo].[UFRecords](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Form] [uniqueidentifier] NOT NULL,

    [Created] [datetime] NOT NULL,

    [Updated] [datetime] NOT NULL,

    [CurrentPage] [uniqueidentifier] NULL,

    [UmbracoPageId] [int] NULL,

    [IP] [nvarchar](255) NULL,

    [MemberKey] [nvarchar](255) NULL,

    [UniqueId] [uniqueidentifier] NOT NULL,

    [State] [nvarchar](50) NULL,

    [RecordData] [ntext] NOT NULL,

     CONSTRAINT [PK_UFRecords] PRIMARY KEY CLUSTERED 

    (

    [Id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

     

    GO

     

    CREATE TABLE [dbo].[UFRecordFields](

    [Key] [uniqueidentifier] NOT NULL,

    [FieldId] [uniqueidentifier] NOT NULL,

    [Record] [int] NOT NULL,

    [Alias] [nvarchar](255) NOT NULL,

    [DataType] [nvarchar](255) NOT NULL,

     CONSTRAINT [PK_UFRecordFields] PRIMARY KEY CLUSTERED 

    (

    [Key] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

     

    CREATE TABLE [dbo].[UFRecordDataBit](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Key] [uniqueidentifier] NOT NULL,

    [Value] [bit] NULL,

     CONSTRAINT [PK_UFRecordDataBit] PRIMARY KEY CLUSTERED 

    (

    [Id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

    ALTER TABLE [dbo].[UFRecordDataBit]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataBit_UFRecordFields_Key] FOREIGN KEY([Key])

    REFERENCES [dbo].[UFRecordFields] ([Key])

    GO

     

    ALTER TABLE [dbo].[UFRecordDataBit] CHECK CONSTRAINT [FK_UFRecordDataBit_UFRecordFields_Key]

    GO

     

     

    CREATE TABLE [dbo].[UFRecordDataDateTime](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Key] [uniqueidentifier] NOT NULL,

    [Value] [datetime] NULL,

     CONSTRAINT [PK_UFRecordDataDateTime] PRIMARY KEY CLUSTERED 

    (

    [Id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

    ALTER TABLE [dbo].[UFRecordDataDateTime]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataDateTime_UFRecordFields_Key] FOREIGN KEY([Key])

    REFERENCES [dbo].[UFRecordFields] ([Key])

    GO

     

    ALTER TABLE [dbo].[UFRecordDataDateTime] CHECK CONSTRAINT [FK_UFRecordDataDateTime_UFRecordFields_Key]

    GO

     

     

    CREATE TABLE [dbo].[UFRecordDataInteger](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Key] [uniqueidentifier] NOT NULL,

    [Value] [int] NULL,

     CONSTRAINT [PK_UFRecordDataInteger] PRIMARY KEY CLUSTERED 

    (

    [Id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

    ALTER TABLE [dbo].[UFRecordDataInteger]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataInteger_UFRecordFields_Key] FOREIGN KEY([Key])

    REFERENCES [dbo].[UFRecordFields] ([Key])

    GO

     

    ALTER TABLE [dbo].[UFRecordDataInteger] CHECK CONSTRAINT [FK_UFRecordDataInteger_UFRecordFields_Key]

    GO

     

     

    CREATE TABLE [dbo].[UFRecordDataLongString](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Key] [uniqueidentifier] NOT NULL,

    [Value] [ntext] NULL,

     CONSTRAINT [PK_UFRecordDataLongString] PRIMARY KEY CLUSTERED 

    (

    [Id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

     

    GO

     

    ALTER TABLE [dbo].[UFRecordDataLongString]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataLongString_UFRecordFields_Key] FOREIGN KEY([Key])

    REFERENCES [dbo].[UFRecordFields] ([Key])

    GO

     

    ALTER TABLE [dbo].[UFRecordDataLongString] CHECK CONSTRAINT [FK_UFRecordDataLongString_UFRecordFields_Key]

    GO

     

     

    CREATE TABLE [dbo].[UFRecordDataString](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Key] [uniqueidentifier] NOT NULL,

    [Value] [nvarchar](255) NOT NULL,

     CONSTRAINT [PK_UFRecordDataString] PRIMARY KEY CLUSTERED 

    (

    [Id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

    ALTER TABLE [dbo].[UFRecordDataString]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataString_UFRecordFields_Key] FOREIGN KEY([Key])

    REFERENCES [dbo].[UFRecordFields] ([Key])

    GO

     

    ALTER TABLE [dbo].[UFRecordDataString] CHECK CONSTRAINT [FK_UFRecordDataString_UFRecordFields_Key]

    GO

     

     

     

     

     

     

     

     

     

     

     

     

     

  • Leon 101 posts 489 karma points
    Mar 30, 2015 @ 14:18
    Leon
    0

    Thanks. You're the best!

  • Zac 223 posts 575 karma points
    Mar 30, 2015 @ 23:46
    Zac
    0

    Thanks for posting that Tim. Would you be able to give us an idea of what your migration package does?

    We ran it and the forms are working but (presumably?) the schema for the Entries is wrong and we can't submit any entries.

    It's all a bit confusing. Did something go wrong or are we supposed to work in the schema changes manually?

  • Zac 223 posts 575 karma points
    Mar 31, 2015 @ 22:46
    Zac
    4

    OK so I figured it out from reading through the script Tim posted.

    You need to remove the UFRecordXXX tables in the database, and only those tables. You can either rename them and their keys, or drop them. I chose to rename them in the hope that someone might write a migration script or plugin. Once they are removed you can run the script to install the new Umbraco Forms records schema.

    Here's the SQL:

    Rename the Contour records tables:

    EXEC sp_rename UFRecordDataBit, ContourUFRecordDataBit
    EXEC sp_rename UFRecordDataDateTime, ContourUFRecordDataDateTime
    EXEC sp_rename UFRecordDataInteger, ContourUFRecordDataInteger
    EXEC sp_rename UFRecordDataLongString, ContourUFRecordDataLongString
    EXEC sp_rename UFRecordDataString, ContourUFRecordDataString
    EXEC sp_rename UFRecordFields, ContourUFRecordFields
    EXEC sp_rename UFRecords, ContourUFRecords
    EXEC sp_rename UFRecordsXml, ContourUFRecordsXml
    EXEC sp_rename 'FK_UFRecordDataBit_UFRecordFields', 'FK_ContourUFRecordDataBit_UFRecordFields', 'OBJECT';
    EXEC sp_rename 'FK_UFRecordDataDateTime_UFRecordFields', 'FK_ContourUFRecordDataDateTime_UFRecordFields', 'OBJECT';
    EXEC sp_rename 'FK_UFRecordDataInteger_UFRecordFields', 'FK_ContourUFRecordDataInteger_UFRecordFields', 'OBJECT';
    EXEC sp_rename 'FK_UFRecordDataLongString_UFRecordFields', 'FK_ContourUFRecordDataLongString_UFRecordFields', 'OBJECT';
    EXEC sp_rename 'FK_UFRecordDataString_UFRecordFields', 'FK_ContourUFRecordDataString_UFRecordFields', 'OBJECT';
    EXEC sp_rename 'PK_UFRecordFields', 'PK_ContourUFRecordFields', 'OBJECT';
    EXEC sp_rename 'FK_UFRecordFields_UFFields', 'FK_ContourUFRecordFields_UFFields', 'OBJECT';
    EXEC sp_rename 'FK_UFRecordFields_UFRecords', 'FK_ContourUFRecordFields_UFRecords', 'OBJECT';
    EXEC sp_rename 'PK_UFRecords', 'PK_ContourUFRecords', 'OBJECT';
    EXEC sp_rename 'FK_UFRecords_UFForms', 'FK_ContourUFRecords_UFForms', 'OBJECT';
    EXEC sp_rename 'PK_UFRecordsXml', 'PK_ContourUFRecordsXml', 'OBJECT';
    EXEC sp_rename 'FK_UFRecordsXml_UFForms', 'FK_ContourUFRecordsXml_UFForms', 'OBJECT';
    

    Drop the Contour records tables (for those braver than myself):

    DROP TABLE UFRecordDataBit
    DROP TABLE UFRecordDataDateTime
    DROP TABLE UFRecordDataInteger
    DROP TABLE UFRecordDataLongString
    DROP TABLE UFRecordDataString
    DROP TABLE UFRecordFields
    DROP TABLE UFRecords
    DROP TABLE UFRecordsXml
    

    Once those are out of the way you can run Tim Geyssens' script which will create the new tables.

    The SQL to generate the new Umbraco Forms UFRecords tables (as posted by Tim): http://pastebin.com/sW4CeTCn

  • Paul 184 posts 646 karma points
    Nov 20, 2015 @ 13:42
    Paul
    1

    Excellent post Zac, incredibly useful! Many thanks !

Please Sign in or register to post replies

Write your reply to:

Draft