Odd Umbraco Forms Error Truncating on Update Field, but unable to know which field is doing it!
Hi
When updating forms, I am getting a funny error here, but I don't know which field is doing it! It doesn't happen to every form submission, but it is happening. Is there a way of checking which field is having the problem?
Using Umbraco forms 8.2.
Exception
System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) in D:\a\1\s\src\Umbraco.Core\Persistence\FaultHandling\RetryPolicy.cs:line 172
at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
at NPoco.Database.Execute(String sql, CommandType commandType, Object[] args)
at Umbraco.Forms.Core.Data.Storage.RecordFieldValueStorage.InsertRecordFieldValues(RecordField recordFieldInForm)
at Umbraco.Forms.Core.Data.Storage.RecordFieldStorage.UpdateRecordField(RecordField recordField)
at Umbraco.Forms.Core.Data.Storage.RecordStorage.UpdateRecord(Record record, Form form)
at LGO.Controllers.FormsController.SaveForm(Form form, FormViewModel model, Dictionary`2 state, ControllerContext context, Boolean IsLast)
at LGO.Controllers.FormsController.HandleFormSubmission(FormViewModel model)
ClientConnectionId:7dce564d-0e67-45b7-8dee-6c93e5a76c02
Error Number:8152,State:30,Class:16
Just to add to this, i have installed ExpressProfiler on the server to see if i can see what the actual SQL statement is that fails....but it happens very rarely.
UFRecordDataString has nvarchar(255)
UFRecordDataLongString has NTEXT.
Bit, DateTime, Integer have their correct types.
i think i might have a clue now. so i have upload fields, and these appear to have a limit of 255 characters within the path.....? if a user posts something larger than 255, would it be truncated? i dont quite get it, cos there doesnt seem to be an entry for the records in the database, but within the json of the RecordFields. It it looks like upon retrieval/resumption, if the path of the file is longer than 255, it will truncate.
if i changed the datatype to i dont know, nvarchar(1000) i wonder if that would be sufficient.
Odd Umbraco Forms Error Truncating on Update Field, but unable to know which field is doing it!
Hi
When updating forms, I am getting a funny error here, but I don't know which field is doing it! It doesn't happen to every form submission, but it is happening. Is there a way of checking which field is having the problem?
Using Umbraco forms 8.2.
Just to add to this, i have installed ExpressProfiler on the server to see if i can see what the actual SQL statement is that fails....but it happens very rarely.
Satpal
Comment author was deleted
it is just the column in the db that needs to be updated... not sure which but look at the structure... it isn't linked to a specific field...
yeah, this is where im coming unstuck. i dont know which column it is.
hopefully the profiler will find something. most columns seem to have the correct datatype/length.
Comment author was deleted
can you share screenshot of table and column structure of the UFDATA* tables?
You mean the UFRecordData* Tables?
i'll give it a go. bare with me.
UFRecordDataString has nvarchar(255) UFRecordDataLongString has NTEXT.
Bit, DateTime, Integer have their correct types.
i think i might have a clue now. so i have upload fields, and these appear to have a limit of 255 characters within the path.....? if a user posts something larger than 255, would it be truncated? i dont quite get it, cos there doesnt seem to be an entry for the records in the database, but within the json of the RecordFields. It it looks like upon retrieval/resumption, if the path of the file is longer than 255, it will truncate.
if i changed the datatype to i dont know, nvarchar(1000) i wonder if that would be sufficient.
Comment author was deleted
yes update nvarchar(255) so it can store more data
Comment author was deleted
the error happens on form submissions, so the data entered is bigger then 255... the insert statement fails...
is working on a reply...