Column specified more than once in SET clause

[Chris]

Seems like most of the action is taking place in this forum so I'll post this over here too.

We are using the latest version of OpenAccess. We are using a vertical inheritance strategy in our model. When trying to add an object, we get the following exception:

Telerik.OpenAccess.RT.sql.SQLException : The column name 'AssetId' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'AssetId' may appear twice in the view definition. Statement(s) could not be prepared.

Telerik.OpenAccess.Exceptions.DataStoreException: Insert of '777647279-d9b59293-819d-4835-bdf4-f2674d6f6388' failed: Telerik.OpenAccess.RT.sql.SQLException: The column name 'AssetId' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'AssetId' may appear twice in the view definition.
Statement(s) could not be prepared.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
INSERT INTO [Users] ([AssetId], [EmailAddress], [FirstName], [LastName], [OrganizationId], [AssetId]) VALUES (?, ?, ?, ?, ?, ?)
(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: The column name 'AssetId' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'AssetId' may appear twice in the view definition.
Statement(s) could not be prepared.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes) ---> Telerik.OpenAccess.RT.sql.SQLException: The column name 'AssetId' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'AssetId' may appear twice in the view definition.
Statement(s) could not be prepared.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
   --- End of inner exception stack trace ---
   at OpenAccessRuntime.ExceptionWrapper.Throw()
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x, Boolean needsRollback)
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.internalCommit(Boolean phase)
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.commit()
   at VirtualOffice.Controllers.UserController.CreateUser(Guid pOrganizationId, String pEmail, String pFirstName, String pLastName, Boolean pCreatePendingConfirmation) in C:\inetpub\wwwroot\VirtualOffice\Common Libraries\VirtualOffice.Controllers\UserController.cs:line 131
   at Controls_ConfirmTokenInvitation.CreateUser(String pEmail, String pPassword, String pFirstName, String pLastName, Boolean pIsShadowUser) in c:\inetpub\wwwroot\VirtualOffice\Web\Portal\Controls\ConfirmToken.ascx.cs:line 112
   at Controls_ConfirmTokenInvitation.OnConfirmInviteClick(Object sender, EventArgs e) in c:\inetpub\wwwroot\VirtualOffice\Web\Portal\Controls\ConfirmToken.ascx.cs:line 96

We've tried using the association editor to explicitly set all relations, but that doesn't seem to be working. Here is a link the zip containing the RLINQ file. 

https://s3.amazonaws.com/SolidCloud-misc/EntityDiagrams.zip

Any help would be appreciated. 

[Zoran]

Hi Chris,

 We reproduced the issue you reported and it looks like a bug on our side when there is a 1:1 association and Vertical inheritance. The 1:1 association between the User and PendingConfirmation objects is the reason for this exception. We are still working on the issue and I will update you later if we could find a workaround for you at the moment. Thanks a lot for reporting this issue to us.

Regards,

Zoran
the Telerik team

[Muhammad]

Hi Guys,
Same issue is appearing while creating a user from back end form or by programmatically. I created a property in user profile setting its database field name to 'xyz' then deleted the property and recreated it with a bit different name but same database column. After that can't save the user profile, getting error as shown above. Any workaround?

[Muhammad]

I deleted the duplicate field from database by performing following sterps.

1. Delete the widget from your form;
2. Go to sf_meta_types, find the id of your form and with this id, find the corresponding dynamic property in the sf_meta_fields table.
3. Then delete the property and delete the column in the form table.

For original blog post you can visit http://www.sitefinity.com/developer-network/forums/developing-with-sitefinity-/delete-form-builder-database-tables-or-columns 

[Doroteya]

Hello Muhammad,

I am glad that currently you do not experience an issue on your side.

At the time present, we could not confirm that the problem you describe is the same as the one reported by Chris. However, the bug on his side is scheduled for fixing for our next official release.

I will make sure this thread is updated once the problem is solved.

If you experience other difficulties or have additional questions, do not hesitate to get back to us.


Regards,
Doroteya
Telerik