By Peter Jones - 1/21/2010
Hi,
We had a curious issue today. A form exceptioned because column length value (by mistake) in the database was too short - just 20 characters when it should have been 100.
The database was corrected but in testing I noted that when I entered 40 characters what is saved to the database is 22 characters. If enter 40 characters in EM then no problems so it is something in the form. So, I assume the truncation must be happening in the BO and what I didn't do is remap and compile the BO after I changed the column length.
So, a few questions:
1) Does SF trim form data to the maximum a column can accept during the Save operation.
2) If 1 is true then after changing column lengths does the associated BO need to be recompiled.
3) If 2) is true then it would appear there may be a bug in the SF code in that a nvarChar(20) column is being saved with 22 characters not the 20 as was originally mapped.
BTW we use DevExpress but I can't see how that would effect things.
Cheers, Peter
|
By Bill Cunnien - 1/22/2010
Good morning, Peter,
I have had experience with strings being too long and the save operation failed because of it. The data server will not truncate automatically and I know SF does not do this.
If you do change the length of the column, then a visit to the BOM is in order. Definitely recompile.
It is quite odd that an nvarchar data type with a max length of 20 would allow a set of characters with a length of 22.
Hope you get this one figured out!
Bill
|
By Peter Jones - 1/23/2010
Hi Guys,
I ran the BO Mapper and all ok now, i.e. I can save strings over 20 (22? Characters). I would just like clarrification though. If SF didn't do the truncating to 22 characters and all that is changed is a rebuild using the BOM then a recompile then what has changed? Sorry Bill but it seems to me the truncating (to 22 characters - not 20 as it should have been) was done within SF - what do you (or the SF team) think?
Cheers, Peter
|
By Peter Jones - 1/26/2010
Bump...
I was hoping a SF guru could through some light on why the 22 character truncation and why a BOM rebuild fixed the problem.
Cheers, Peter
|
By Edhy Rijo - 1/26/2010
Peter Jones (01/26/2010)
I was hoping a SF guru could through some light on why the 22 character truncation and why a BOM rebuild fixed the problem.
Well I am not a SF guru, but I would say that what you have seen has to do with the functionality of SF to adjust the max length for character fields (see the TextBox.AutoAdjustMaxLenght propery help).
The fact that by rebuilding the BO fixed the problem for you, it does reflect the behavior of having this property set to True.
|
By Peter Jones - 1/27/2010
Hi Edhy,
I didn't know about that property but, in my case, I'm using a DevExpress grid and I can't see that the grid would be doing anything - ith is just shuffling data to/from the bo.
BTW - this isn't an issue for me I would just like to understand where the original (wrong) truncation was being performed.
Cheers, Peter
|
By Peter Jones - 1/27/2010
Hi Edhy,
I didn't know about that property but, in my case, I'm using a DevExpress grid and I can't see that the grid would be doing anything - ith is just shuffling data to/from the bo.
BTW - this isn't an issue for me I would just like to understand where the original (wrong) truncation was being performed.
Cheers, Peter
|
By Trent L. Taylor - 1/30/2010
1) Does SF trim form data to the maximum a column can accept during the Save operation.
When you map a BO, the length of the field is actually stored off an used when building the UPDATE commands. So when you change the length of a field, it is best to rebuild the partial class to make sure that the field lengths are updated.
2) If 1 is true then after changing column lengths does the associated BO need to be recompiled.
Yes. Just rebuild the partial class and you are good to go.
3) If 2) is true then it would appear there may be a bug in the SF code in that a nvarChar(20) column is being saved with 22 characters not the 20 as was originally mapped.
Actually, no. This could be a really long explanation, but this has to do with UNICODE values. I would have to go look up in our issues history and log, but several years ago, we had an issue with data being truncated, the reason was the parm value of unicode data. I would have to go and dig it out and get my head back into it to recall exactly what the issue was, but I remember fixing this on the data source.
|
By Peter Jones - 1/30/2010
Hi Trent,
Thanks for the response. It's good to hear that SF does truncate but the unicode side of things is unclear. We are using unicode characters but SF certainly seems to truncate to a value higher than that defined in the BOM, i.e. in the case in question the original database column was nvarChar(20) which was changed to nvarChar(100) and, without a BOM rebuild, SF was truncating whatever value was entered to 22 characters. Also, keep in mind we went down this path because a user reported an exception when he entered a value greater than 20 characters.
Cheers, Peter
|
By Trent L. Taylor - 1/31/2010
Well, this value that I am talking about is actually seamless to you. If you enter 20 in the DDT then it will be 20 when truncated on the INSERT/UPDATE. If you turn on debug mode, however, you will see these extra characters. That was what I was referring to. So let's backup then. Can you give me a sample with the following so I can see what you are referring to otherwise I feel that we may go round-and-round out here on the forum?- A backup or MDF of the sample database.
- A very simple sample showing just this once piece that is breaking on you.
Thanks.
|
By Peter Jones - 1/31/2010
Hi Trent,
Well - I can't replicate the issue - everything works as advertised.
I don't think trying to revert my system back to how it was is worth the effort. The way things work now with the BO truncating is great - we didn't realise the BO actually did this and it was something on a long term "things to do" list that we can now cross off.
We log all our exceptions and following is the one that started this thread. When the user called re the error we assumed it was sometype of 'string too long' issue and the Batch Number the user had entered was about 30 characters and the field size (at that time) was 20. We asked him to try again but keep the batch number to less than 20 characters and all was ok.
Cheers, Peter
Date and Time: 22/01/2010 11:47:25 AM
(Inner Exception)
Exception Source: .Net SqlClient Data Provider
Exception Type: System.Data.SqlClient.SqlException
Exception Message: String or binary data would be truncated.
The statement has been terminated.
Exception Target Site: OnError
---- Stack Trace ----
System.Data.SqlClient.SqlConnection.OnError(exception As SqlException, breakConnection As Boolean)
TMSBatchesMaint.exe: N 1953274
System.Data.SqlClient.SqlInternalConnection.OnError(exception As SqlException, breakConnection As Boolean)
TMSBatchesMaint.exe: N 4849707
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(stateObj As TdsParserStateObject)
TMSBatchesMaint.exe: N 00194
System.Data.SqlClient.TdsParser.Run(runBehavior As RunBehavior, cmdHandler As SqlCommand, dataStream As SqlDataReader, bulkCopyHandler As BulkCopySimpleResultSet, stateObj As TdsParserStateObject)
TMSBatchesMaint.exe: N 02392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
TMSBatchesMaint.exe: N 00033
System.Data.SqlClient.SqlDataReader.get_MetaData()
TMSBatchesMaint.exe: N 00096
System.Data.SqlClient.SqlCommand.FinishExecuteReader(ds As SqlDataReader, runBehavior As RunBehavior, resetOptionsString As String)
TMSBatchesMaint.exe: N 00297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(cmdBehavior As CommandBehavior, runBehavior As RunBehavior, returnStream As Boolean, async As Boolean)
TMSBatchesMaint.exe: N 00954
System.Data.SqlClient.SqlCommand.RunExecuteReader(cmdBehavior As CommandBehavior, runBehavior As RunBehavior, returnStream As Boolean, method As String, result As DbAsyncResult)
TMSBatchesMaint.exe: N 00162
System.Data.SqlClient.SqlCommand.RunExecuteReader(cmdBehavior As CommandBehavior, runBehavior As RunBehavior, returnStream As Boolean, method As String)
TMSBatchesMaint.exe: N 00032
System.Data.SqlClient.SqlCommand.ExecuteReader(behavior As CommandBehavior, method As String)
TMSBatchesMaint.exe: N 00141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(behavior As CommandBehavior)
TMSBatchesMaint.exe: N 00012
System.Data.Common.DbCommand.ExecuteReader(behavior As CommandBehavior)
TMSBatchesMaint.exe: N 00010
MicroFour.StrataFrame.Data.DbDataSourceItem.InternalExecuteReader(Command As DbCommand, IsTransactional As Boolean, TransactionKey As String)
TMSBatchesMaint.exe: N 00244
MicroFour.StrataFrame.Data.SqlDataSourceItem.UpdateRow(QueryInfo As QueryInformation, RowToUpdate As DataRow, ConcurrencyHandler As ConcurrencyExceptionHandler, RowErrorHandler As AddRowErrorHandler, RecreateCommand As Boolean)
TMSBatchesMaint.exe: N 03833
MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInfo As QueryInformation, RowToUpdate As DataRow, ConcurrencyHandler As ConcurrencyExceptionHandler, RowErrorHandler As AddRowErrorHandler)
TMSBatchesMaint.exe: N 00053
MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(ThreadParams As Object)
TMSBatchesMaint.exe: N 00772
(Outer Exception)
Exception Source: MicroFour StrataFrame Business
Exception Type: MicroFour.StrataFrame.Data.DataLayerSavingException
Exception Message: String or binary data would be truncated.
The statement has been terminated.
Exception Target Site: UpdateDataTable
---- Stack Trace ----
MicroFour.StrataFrame.Data.DataLayer.UpdateDataTable(TableToUpdate As DataTable, Transactional As Boolean, TransactionKey As String)
TMSBatchesMaint.exe: N 01129
MicroFour.StrataFrame.Data.DataLayer.Save(TableToSave As DataTable, Transactional As Boolean, TransactionKey As String)
TMSBatchesMaint.exe: N 00479
MicroFour.StrataFrame.Business.BusinessLayer.Save(Transactional As Boolean, TransactionKey As String)
TMSBatchesMaint.exe: N 01784
MicroFour.StrataFrame.Business.BusinessLayer.Save()
TMSBatchesMaint.exe: N 00047
TMSBatchesMaint.frmBTC.CanAllocUnallocProcede(PUNIDAllocateList As String)
frmBTC.vb: line 1879, col 17, IL 0052
TMSBatchesMaint.frmBTC.btnAllocUnalloc_Click(sender As Object, e As EventArgs)
frmBTC.vb: line 1807, col 13, IL 1756
System.Windows.Forms.Control.OnClick(e As EventArgs)
TMSBatchesMaint.exe: N 00111
DevExpress.XtraEditors.BaseButton.OnClick(e As EventArgs)
TMSBatchesMaint.exe: N 00069
DevExpress.XtraEditors.BaseButton.OnMouseUp(e As MouseEventArgs)
TMSBatchesMaint.exe: N 00112
System.Windows.Forms.Control.WmMouseUp(m As Message&, button As MouseButtons, clicks As Int32)
TMSBatchesMaint.exe: N 00654
System.Windows.Forms.Control.WndProc(m As Message&)
TMSBatchesMaint.exe: N 8788613
DevExpress.Utils.Controls.ControlBase.WndProc(m As Message&)
TMSBatchesMaint.exe: N 00063
System.Windows.Forms.ControlNativewindow.OnMessage(m As Message&)
TMSBatchesMaint.exe: N 00015
System.Windows.Forms.ControlNativewindow.WndProc(m As Message&)
TMSBatchesMaint.exe: N 00048
System.Windows.Forms.Nativewindow.Callback(hWnd As IntPtr, msg As Int32, wparam As IntPtr, lparam As IntPtr)
TMSBatchesMaint.exe: N 00089
Detailed System information follows:
Machine Name: TMSAPPSERVTWM
IP Address: 10.8.0.249 192.168.50.1 192.168.51.1
Current User: TMSTWMathan
Terminal Services: YES
Terminal Name: NATHAN
Terminal IP: 10.8.0.22
Terminal Display: 1024x768, 16-bit colour
Session Name: Office Sessions#5
Command Line: "C:\Program Files\Systems Engineering\TMS\TMSBatchesMaint.exe" BatchType=4
Application Domain: TMSBatchesMaint.exe
Assembly Codebase: file:///C:/Program Files/Systems Engineering/TMS/TMSBatchesMaint.exe
Assembly Full Name: TMSBatchesMaint, Version=2.0.0.5975, Culture=neutral, PublicKeyToken=72468ceb77b783ec
Assembly Version: 2.0.0.5975
Assembly Build Date: 27/11/2009 4:54:34 PM
---- File Versions ----
bartend.exe 9.10.2617.1857
psexec.exe 1.96
TMSBackup - Copy.exe 2.0.0.5900
TMSBackup.exe 2.0.0.5975
TMSBatchesMaint.exe 2.0.0.5975
TMSControlTableMaint.exe 2.0.0.5975
TMSDatabaseConfig.exe 2.0.0.5975
TMSERPInterface.exe 2.0.0.5975
TMSERPInterfaceControls.exe 2.0.0.5975
TMSGradingStation.exe 2.0.0.5975
TMSHousekeepingService.exe 2.0.0.5975
TMSInstall.exe 2.0.0.5975
TMSLaunchTerminal.exe 2.0.0.5975
TMSLocalisationMaint.exe 2.0.0.5975
TMSLogAccess.exe 2.0.0.5975
TMSMeasuringStation.exe 2.0.0.5975
TMSMenu.exe 2.0.0.5975
TMSOrderAlloc.exe 2.0.0.5975
TMSOrderAllocService.exe 2.0.0.5975
TMSOrders - Copy.exe 2.0.0.5900
TMSOrders.exe 2.0.0.5975
TMSPartPUNSum.exe 2.0.0.5975
TMSProductionMaint.exe 2.0.0.5975
TMSRecipes.exe 2.0.0.5975
TMSReplication.exe 2.0.0.5975
TMSReport.exe 2.0.0.5975
TMSSerial.exe 2.0.0.5975
TMSSerialTest.exe 2.0.0.5975
TMSStationMaint.exe 2.0.0.5975
TMSStationManning.exe 2.0.0.5975
TMSSystemTableMaint.exe 2.0.0.5975
BOAttributes.dll 2.0.0.5975
BOBatches - Copy (2).dll 2.0.0.5900
BOBatches - Copy.dll 2.0.0.5900
BOBatches.dll 2.0.0.5975
BOERPInterface - Copy.dll 2.0.0.5900
BOERPInterface.dll 2.0.0.5975
BOInventory.dll 2.0.0.5975
BOLocalisationEditor.dll 2.0.0.5975
BOOrders.dll 2.0.0.5975
BOProcStation - Copy.dll 2.0.0.5900
BOProcStation.dll 2.0.0.5975
BORecipes.dll 2.0.0.5975
BOReport.dll 2.0.0.5975
BOTableMaint.dll 2.0.0.5975
BOTMSSerial.dll 2.0.0.5975
DevExpress.BonusSkins.v9.2.dll 9.2.4.0
DevExpress.Charts.v9.2.Core.dll 9.2.4.0
DevExpress.Data.v9.2.dll 9.2.4.0
DevExpress.Utils.v9.2.dll 9.2.4.0
DevExpress.XtraBars.v9.2.dll 9.2.4.0
DevExpress.XtraCharts.v9.2.dll 9.2.4.0
DevExpress.XtraCharts.v9.2.UI.dll 9.2.4.0
DevExpress.XtraEditors.v9.2.dll 9.2.4.0
DevExpress.XtraGrid.v9.2.dll 9.2.4.0
DevExpress.XtraLayout.v9.2.dll 9.2.4.0
DevExpress.XtraNavBar.v9.2.dll 9.2.4.0
DevExpress.XtraPivotGrid.v9.2.Core.dll 9.2.4.0
DevExpress.XtraPivotGrid.v9.2.dll 9.2.4.0
DevExpress.XtraPrinting.v9.2.dll 9.2.4.0
DevExpress.XtraReports.v9.2.dll 9.2.4.0
DevExpress.XtraRichEdit.v9.2.dll 9.2.4.0
DevExpress.XtraScheduler.v9.2.Core.dll 9.2.4.0
DevExpress.XtraScheduler.v9.2.dll 9.2.4.0
DevExpress.XtraTreeList.v9.2.dll 9.2.4.0
DevExpress.XtraVerticalGrid.v9.2.dll 9.2.4.0
EnvDTE.dll 8.0.50727.1826 (QFE.050727-1800)
Interop.BarTender.dll 9.1.0.0
Interop.VBA.dll 6.0.0.0
MicroFour StrataFrame Base.dll 1.7.0.0
MicroFour StrataFrame Business.dll 1.7.0.0
MicroFour StrataFrame Inherited UI.dll 1.7.0.0
MicroFour StrataFrame Security.dll 1.7.0.0
MicroFour StrataFrame UI.dll 1.7.0.0
Microsoft.MSXML.dll 8.0.50727.1826 (QFE.050727-1800)
Microsoft.VisualStudio.OLE.Interop.dll 7.10.6070
Microsoft.VisualStudio.Shell.Interop.8.0.dll 8.0.50727.1826 (QFE.050727-1800)
Microsoft.VisualStudio.Shell.Interop.dll 7.10.6071
Microsoft.VisualStudio.TextManager.Interop.8.0.dll 8.0.50727.1826 (QFE.050727-1800)
Microsoft.VisualStudio.TextManager.Interop.dll 7.10.6070
SerialNET.dll 2.0.0.1
UtilBatches.dll 2.0.0.5975
UtilBOBase.dll 2.0.0.5975
UtilControls.dll 2.0.0.5975
UtilDeviceIO.dll 2.0.0.5975
UtilEnums.dll 2.0.0.5975
UtilExceptions.dll 2.0.0.5975
UtilForms.dll 2.0.0.5975
UtilHelp.dll 2.0.0.5975
UtilLabelPrint.dll 2.0.0.5975
UtilLocalisation.dll 2.0.0.5975
UtilLogging.dll 2.0.0.5975
UtilResource.dll 2.0.0.5975
UtilRunTimeEnvironment.dll 2.0.0.5975
UtilSecurity.dll 2.0.0.5975
UtilSundry.dll 2.0.0.5975
|
By Trent L. Taylor - 2/1/2010
Yeah, that is a lot to dig through. If you can come up with a reproducible scenario, let me know and I will be more than happy to help. Glad you got it going forward! 
|
|