1

SharePoint 2013

I have an External Content Type (ECT) defined for a table in a SQL Server database. I also have the usual CRUD methods defined for the ECT. The table has over 50 fields, but there is an ID field (an IDENTITY column) and a computed column called RefNumber which uses the ID field to generate a unique alphanumeric reference number. The RefNumber field is the Primary Key on the table.

In the Create method, I have omitted the ID and RefNumber fields as Input parameters. In the Return parameters page, there is a warning saying that the RefNo field is a Nullable type - I don't know if this is significant or not to the issue.

Users complete an InfoPath form bound to an external list associated with the ECT. Users can enter data for all other fields in the table, except the two fields mentioned above. On submitting the form, this error appears:

Screenshot of error message

The data is still submitted to the SQL database, and the ID and RefNo values are generated fine.

What do I need to do to make this error to go away?

  • Please provide definition of field with just ID and RefNo field and constraints. – Alok Kumar Gupta Apr 3 '17 at 10:07
  • [ID] int IDENTITY (1,1) NOT NULL, [RefNo] ('12345'+RIGHT('00000'+CONVERT([varchar](6),[ID],(0)),(6))) PERSISTED NOT NULL – Jazza Apr 4 '17 at 8:20
1

Use Business connectivity service in the SPD. Its straightforward

  • Can you be a bit more specific? What in BCS exactly needs to be changed? – Jazza Apr 6 '17 at 19:20
0

Please try changing field definition for "RefNo" to

[RefNo] IsNull(('12345'+RIGHT('00000'+CONVERT(varchar,[ID],(0)),(6))),'1234500000000000') PERSISTED NOT NULL

and try creating BCS from it. Just added IsNull() on top of calculation.

  • Unfortunately this did not work, but thanks anyway. – Jazza Apr 4 '17 at 11:19
  • One thing I noticed in SQL script, on SQL Server 2012, I was not able to specify "NOT NULL" as part of column definition, pls confirm Database's of SQL Server Compatibility version no. – Alok Kumar Gupta Apr 6 '17 at 4:17
  • This is with SQL 2014 database. I may have put the NOT NULL by mistake. – Jazza Apr 6 '17 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.