Asked by:
text or binary data will be truncated error

Question
-
User-1633045945 posted
Hello,
i am trying to store an image in a sql database
the column ("waste_signature") is set to varbinary(max)
the data that I have to convert to byte() is the following:
data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfQAAAD6CAYAAABXq7VOAAAZWUlEQVR4Xu3dWwh8W10H8J9dTKzoKuWB7kFpRfVSkqZFUFCB9VIQdLMeogsWCIUgXRAKIiowfIlTPXR5CMqCiCK6KtrNstB6yIoojC4W3cyg4puzcruZy56ZNbNm9nwGDv9zzn9mrb0/vz3z3Ze1135KeREgQIAAAQJ3L/CUu18DK0CAAAECBAiUQLcRECBAgACBFQgI9BUU0SoQIECAAAGBbhsgQIAAAQIrEBDoKyiiVSBAgAABAgLdNkCAAAECBFYgINBXUESrQIAAAQIEBLptgAABAgQIrEBAoK+giFaBAAECBAgIdNsAAQIECBBYgYBAX0ERrQIBAgQIEBDotgECBAgQILACAYG+giJaBQIECBAgINBtAwQIECBAYAUCAn0FRbQKBAgQIEBAoNsGCBAgQIDACgQE+gqKaBUIECBAgIBAtw0QIECAAIEVCAj0FRTRKhAgQIAAAYFuGyBAgAABAisQEOgrKKJVIECAAAECAt02QIAAAQIEViAg0FdQRKtAgAABAgQEum2AAAECBAisQECgr6CIVoEAAQIECAh02wABAgQIEFiBgEBfQRGtAgECBAgQEOi2AQIECBAgsAIBgb6CIloFAgQIECAg0G0DBAgQIEBgBQICfQVFtAoECBAgQECg2wYIECBAgMAKBAT6CopoFQgQIECAgEC3DRAgQIAAgRUICPQVFNEqECBAgAABgW4bIECAAAECKxAQ6CsoolUgQIAAAQIC3TZAgAABAgRWICDQV1BEq0CAAAECBAS6bYAAAQIECKxAQKCvoIhWgQABAgQICHTbAAECBAgQWIGAQF9BEa0CAQIECBAQ6LYBAgQIECCwAgGBvoIiWgUCBAgQICDQbQMECBAgQGAFAgJ9BUW0CgQIECBAQKDbBggQIECAwAoEBPoKimgVCBAgQICAQLcNECBAgACBFQgI9BUU0SoQIECAAAGBbhsgQIAAAQIrEBDoKyiiVSBAgAABAgLdNkCAAAECBFYgINBXUESrQIAAAQIEBLptgAABAgQIrEBAoK+giFaBAAECBAgIdNsAAQIECBBYgYBAX0ERrQIBAgQIEHjkQP+UqvqLqvonmwEBAgQIELh3gUcN9NdXVQI9r8+uql+790JafgIECBB4bIFHDPT3r6q3Tsr+qqr6osfeDKw9AQIECNy7wCMGeo7Mc4TeXjnl/gH3XkjLT4AAAQKPLfCIgf5VVfUjVfU/VdXWP4HuWvpjfxesPQECBO5a4BED/Uer6iur6u1V9dRN9T5qM0Durotp4QkQIEDgcQUeMdD/oKo+uar+uareb1N6A+Me9ztgzQkQILAKgUcL9Ax++5lN5d5UVc/a/Pu3VNUPrKKiVoIAAQIEHlLg0QK9HZ3/+qbaL9j8+YNV9c0PuQVYaQIECBBYhcAjBfqTVfXVm6rlmvmrq+qJzX+/paqeuYqKWgkCBAgQeEiBRwn0NrI9RX5ZVb28qtrReiu8ke4P+RWw0gQIEFiHwCME+mdtrptnQpmcas919NyilmvmL56U0Uj3dWzT1oIAAQIPKbD2QM8kMr9aVQnzjGpPuOfIPK/vqKpvn1TdSPeH/ApYaQIECKxDYM2B/pGbGeES5nl95ybEW+Xmgf7FVfWz6yirtSBAgACBRxNYa6AnxHN7Wo7I88qp9vbvrcYZ1f79k4L/UlV93oENIO3mnzylzYsAAQIECNyMwBoDfR7mOdXeHpU6hc8R/J/PKpFZ5NqT195WVU/b/H0+nx2C9oS2/O/f34yaf8PNVNOCECBAgMDDCqwt0BPmmad9+vS0fdfGf3HBUfm+jeM/Nre+mQf+Yb9CVpwAAQK3IbC2QG/ztDfd+XXzbepvnMwYd0pVTEpziprPECBAgEBXgTUFega0vXCi82NVlfvPl7zmOwJLPtPek+vpueXtUq+cdcjDZHLWoT3L/bVV9dJLdahdAgQIELg/gbUE+nzE+rZBcIeqk7Bs18rz78+uqv+qqvfeDILL7W65vp7T67lu/r6TBvfdw97azeNa84p5dgLSzrZT9W3gXR4gkxDftVOS5ckc9O2a/6H18/cECBAgsGKBNQR6jzA/tsQJ4vaktnz2Uyf3t2ewXc4UJIyzg9BumzvUR9pc+t7WVj6TMQLt3vpDffh7AgQIEFipwL0HekaeZ+KY9vrDzWj0Sw5Sa6e9p5tEjtA/t6q+vKqed+Vt5VWzQYBX7l53BAgQIHALAvcc6NNZ4GKZMM9R8aXvEZ/vROS2uD+uqudeuKAJ7lzrzyWAH5qdIbjnOl6YTfMECBB4DIF7DYIcJefIvN0XPp/W9ZLVm5/if3NVffSBDrN82dHIcn/EgoX776rKLXG/u5m9LgP+pjsq83nozXK3ANVbCBAgsGaBewz0hGJmeJsOFrvmPOyvq6pPm2wUCevp9fT81V9uHv6SAWu7rm+3wW/tz+llgkPXxOez3P3y5pT/rW6rWcfPqKpnbHZM2vPob3V5LRcBAgTuTuDeAj3BkNPNXzaRzjPOcyr6Wq+/njxHfVufeeDLd114YT6zqn5j0sffb8Lywt0e3XwugbRb7qYfzo5OzipccqzD0QvrAwQIELhngXsL9Pmp5iUTx/SuT7v9bFe71zhbsG3a2lt6nnuWL2dRpjP2zb1G1K73tpD1zCUUZxx6y2qPAIGjBe4p0OfXrnOk/IlXPsrLNfvXH1Ce3sJ2dEGO+MD81rlr7EgsWbyEXMY35M/p69+rKuMNUrO8svy5O2DUUXqbsCf9Z0Dlocsc03WZX/bJmIfPMSfAks1j0Xvi+ztV9bFVlbNPr6iqzMg4altZtNDeRGC0wL0E+rYgHTEQbH7tetv182uZzs8U3EKgp055yt08zDNrX+zaZDptzEFCNMs94od6XsssQwYf5p/cUbDvtW1mwTzo59DgyNHf93vp/yVV9b2zhU19cnnNI47vpYqW8+oC1wqfc1ZsPqI9beWIavrks3PaP+azCaDM4NZeOTJ7t8l/X3O55oG+b7a6Y9bx1Pfm9HpOs8/DfH5qfX7Z5NpjINr6ZVBlHuSz65Xr/Pmnzer3xGaOgYT2c3Z86JYue5xax1v43PdU1bfuWJARO/K3YGIZCBwUuIdAn59qz0qNCIFt163nwNdcrmmg5xa3px+s9mXeEJcMBNw2Re22B9fMJ+a51iWKbWufnZAsf/58QQeeWzhL0mE1hjcxn+thvkBCfXiJLMAtCtx6oM8nj4lhTnMfO0VqD/tDR3R/W1Uf2qOjhW28varec/Pe3n0n5DI6PT+s+SenO3N2Yjqf/fOr6guq6tN3LO++p9ClzYRo2rvmHQr7aLNNZZlyKn56FmZhOf7vbSN3To5Zznt47y0MPr0HJ8tI4P8Fbj3Q56e4s+CjRkfPTxVPN6MRz0WfDorrOf1r1vMbquo9zviejKrRGYv8Lh/NDk2CPQG/ZCKg9mGn3HtV4B2XO9pZkyer6kWzprP950jdw4n6mWvpzgVuOdDnj0Md/aP5pVX1U1vqnUlkcsR56Sln511Pj2B6BHqOUDOgLety6itjCHIm45gR46f2da3P5SxROyWfEfofvBnD8XNV9bLJQow6c3Qth2v3Mw307CDm+7VtzIPT79eujP5uVuAWA31fsIw+8suc7Z8wqeZ09PY1izy/xrjv9PbS5ZrvQL2pqr5+cgTUHuWavucz47V55h9pBPIXVtXPT3D/YRP2S729b7/Av1TV+2ze0samzO9MaC3k/+c74EXgoQVuLdB33faUIuVIOH8/4han6UaSZcg/Ca9Ry5JwzdF0e+W56DlVfurr2B2E9ljYHDVd+8zEqevY+3PZ2cmshe31j1X1Qb07ObO9NtZk1HZ66uJPx6vkTpKPmWxnuy59ZefqKwZ+J09dV58j0E3glgI9IfErVfWBO9bOgKN3wsxH/p87uno6VuGat95125AHNDQfJJmzN580YDl2dTmdu+Hc7ePaqzXdHrddTtoV6jlNn53ba1/yyY7TtXaaMu1zniWRCXcyQ+Gj7lBfe5u8i/5GB3q+CBmA9OIdtz21I/P8eBr88s5Nav6Dds5grPnR+b39+I/6os13qm5tR2g6+U0uDW27rXCU3b5+567brpHndyPrk3kPtr2udftovjuvrKqP3yxEdiTy3cxOSO+Azw5axhDM59/I72IuNzzS5a5b3G5vYpmuFegJ7dwKlFPFLcSXTAyTPdBcH7v2HvdNFGfPQsxnKju1jqlFprJtk8HcWijdch3mYw6yrZ4zoLD3uiZovm7TaAbvvbx3BxdoL365lNQuFRwy3TVQNYuW70iO1nsH63S1pwP3pv8/R83ZMe519Lxtcq05f3aEMsbI64EFTg2CY8nm844f+vy/bjbOH77wF/LQctzq388HB51ax/kPkhHDyys+v6Vy9IDN+ZInTNotd5kv4BeWr9qwd05/J3LXQAL+0M58dkazHW+7vTDt5Sj+Ek8/PDTRVPpOqB9a/kPYSx501No4dyzNoWXx9zcucGoQHLNa85nB2mfbPOj5s02vmS9mjnzO/RIcs3z3+N753PbH/mDnR+I1VfXMycrf02nZW6jZLc6l31zm37lzLslcy3p+xuOYSz9Z33x+12x/+Y3JkX/ek6P+Hkft8zEUf1pVH7cF65j1mH4865QZGL9xy5wQmVTqqTsK8xObeSR6rOO1aq+fTgLXCPQsak61J4QS2AlrG9v5Bfy7yW1SS8O4/UjkCH/6yo9caqQuy+qy7ejslgZtTnf4suMxfd7AsjW87rvmd22cerYjIZtr2PPbKretTQ4i8gTABONvTR4clPfme9JO+z9783dvnMyYmOcm5HLGNMCz05T+c+p72n++U+30/xLVXd/R9tlcFsuZi7xv15mJ/6yqn9zswOT9vU79L1l+7xkocK1AH7iKq+16ftr90JFAfjSzxz8fu9BjUprVIu9YsW1nnTIV7m8OgGjPZM+fqW3+zHS807MvGb/yhgHLtqTLLG/GcbQA/auq+vAlH9xTm2zr+Sdnrs6Z8XDpYkx3qHdNEZ1gT9jvul++Tbec9+x6zee9yGeys3DoOQTpu91i2v7MdNF/Mumo7cy3OkxH7s93CPLettPT/r09SbHtEO07OMgOSbbJLH/rJwd6lxhMuLSGq3ifQL/vMs7HJuTHJHvk7UuS64r50uT/bxuE+NKq+u77Jhi29H80ebZ7FiI/SBldnVeO3J42WbLpEd8pC9x+ZFsN28DSpc80yJiUnLqd/mi/rapymrj9AOfP6Y9yfnDbkWACNkeo33eBo735HRu9z3S0cM+fS47cj63PttkR01eCdlt/CceEdm47y6yDqWmc9w0SfmtVfVNV/fiOhds3LfWx63Op92d67Jw52LfNXuvuhEut4/B2BfrwEpy1AIceGLOr8VxTzBH+rTwY5SyEQR9+xeZa5aDut3aburZnuv90Vb37BRYu4ZHBVz1e87EgSy8dndp3O4vRdrDyMKUY/dsmUKcT8bSj2g+rqlzeynuzvO3BPdlJenVVfduOMT8J6dTi3J2IpbNAtsFzz62qZ5wKNPhzBuWeWQCBfibgDXz82L3z/OjnCMJ9/ecXbzo96fmtndZCjhDbYNJpTee3Np7W+vZPLQ2ZfX3Ob5nMe3Ntek3XexOyGYy35BbdudU5z0Vozx9oR/7bdipytJyzNId2ODJDZ3Zu2o7PW4542mU74/NEVWXGv9duPpsa54xW/mxnm7L+7qU/81sq0M8EvJGP5xRejrj3fTnzxcyPfHYADH7rU7heR2FLlyY1zI9gG1yaP/fV8pKhfs6p8W2TpPTYSVjqeO33ZTvJ927JY3lzHTnv7b3DvW82uzYPxb7r5tc2098JAgL9BLQb/kiOvL9kc23ubyZHO24FvFzR8mPYHlyTH8SMmn6vzWncN2+6nQ4cyv9q/z399xbM0yPUdto3f556d0g7WmsDkNJnrt1m5y9na/IEubzSb04j53rt722WMf8vf/81k8mHmmSuqef07nSHop3KbgGREfbZCWn/P2M6vraqnjcrx608p+FyW8k7Wm7byQs3HeX2sz+rqt+enGWxs33pKqy4fYG+4uJaNQKdBNotUvMjzAzsyi1f+fvnzAYCLu161OOHly6f9xG4GwGBfjelsqAEhgrMB7D1WJicZs/lIkelPTS18fACAv3hNwEABBYLzB+csviDsze+rqpesjm6P7UNnyNAYCYg0G0SBAgcI/D5VfVkVX3Ijg9l9rWnb67P5y3tWn0bzJeBeqZ2PkbcewksFBDoC6G8jQCBdxFogwHbvdumdbaBEBgsINAHF0D3BAgQIECgh4BA76GoDQIECBAgMFhAoA8ugO4JECBAgEAPAYHeQ1EbBAgQIEBgsIBAH1wA3RMgQIAAgR4CAr2HojYIECBAgMBgAYE+uAC6J0CAAAECPQQEeg9FbRAgQIAAgcECAn1wAXRPgAABAgR6CAj0HoraIECAAAECgwUE+uAC6J4AAQIECPQQEOg9FLVBgAABAgQGCwj0wQXQPQECBAgQ6CEg0HsoaoMAAQIECAwWEOiDC6B7AgQIECDQQ0Cg91DUBgECBAgQGCwg0AcXQPcECBAgQKCHgEDvoagNAgQIECAwWECgDy6A7gkQIECAQA8Bgd5DURsECBAgQGCwgEAfXADdEyBAgACBHgICvYeiNggQIECAwGABgT64ALonQIAAAQI9BAR6D0VtECBAgACBwQICfXABdE+AAAECBHoICPQeitogQIAAAQKDBQT64ALongABAgQI9BAQ6D0UtUGAAAECBAYLCPTBBdA9AQIECBDoISDQeyhqgwABAgQIDBYQ6IMLoHsCBAgQINBDQKD3UNQGAQIECBAYLCDQBxdA9wQIECBAoIeAQO+hqA0CBAgQIDBYQKAPLoDuCRAgQIBADwGB3kNRGwQIECBAYLCAQB9cAN0TIECAAIEeAgK9h6I2CBAgQIDAYAGBPrgAuidAgAABAj0EBHoPRW0QIECAAIHBAgJ9cAF0T4AAAQIEeggI9B6K2iBAgAABAoMFBPrgAuieAAECBAj0EBDoPRS1QYAAAQIEBgsI9MEF0D0BAgQIEOghINB7KGqDAAECBAgMFhDogwugewIECBAg0ENAoPdQ1AYBAgQIEBgsINAHF0D3BAgQIECgh4BA76GoDQIECBAgMFhAoA8ugO4JECBAgEAPAYHeQ1EbBAgQIEBgsIBAH1wA3RMgQIAAgR4CAr2HojYIECBAgMBgAYE+uAC6J0CAAAECPQQEeg9FbRAgQIAAgcECAn1wAXRPgAABAgR6CAj0HoraIECAAAECgwUE+uAC6J4AAQIECPQQEOg9FLVBgAABAgQGCwj0wQXQPQECBAgQ6CEg0HsoaoMAAQIECAwWEOiDC6B7AgQIECDQQ0Cg91DUBgECBAgQGCwg0AcXQPcECBAgQKCHgEDvoagNAgQIECAwWECgDy6A7gkQIECAQA8Bgd5DURsECBAgQGCwgEAfXADdEyBAgACBHgICvYeiNggQIECAwGABgT64ALonQIAAAQI9BAR6D0VtECBAgACBwQICfXABdE+AAAECBHoICPQeitogQIAAAQKDBQT64ALongABAgQI9BAQ6D0UtUGAAAECBAYLCPTBBdA9AQIECBDoISDQeyhqgwABAgQIDBYQ6IMLoHsCBAgQINBDQKD3UNQGAQIECBAYLCDQBxdA9wQIECBAoIeAQO+hqA0CBAgQIDBYQKAPLoDuCRAgQIBADwGB3kNRGwQIECBAYLCAQB9cAN0TIECAAIEeAgK9h6I2CBAgQIDAYAGBPrgAuidAgAABAj0EBHoPRW0QIECAAIHBAgJ9cAF0T4AAAQIEeggI9B6K2iBAgAABAoMFBPrgAuieAAECBAj0EBDoPRS1QYAAAQIEBgsI9MEF0D0BAgQIEOghINB7KGqDAAECBAgMFhDogwugewIECBAg0ENAoPdQ1AYBAgQIEBgsINAHF0D3BAgQIECgh4BA76GoDQIECBAgMFhAoA8ugO4JECBAgEAPAYHeQ1EbBAgQIEBgsIBAH1wA3RMgQIAAgR4CAr2HojYIECBAgMBgAYE+uAC6J0CAAAECPQQEeg9FbRAgQIAAgcECAn1wAXRPgAABAgR6CAj0HoraIECAAAECgwUE+uAC6J4AAQIECPQQEOg9FLVBgAABAgQGCwj0wQXQPQECBAgQ6CEg0HsoaoMAAQIECAwWEOiDC6B7AgQIECDQQ0Cg91DUBgECBAgQGCwg0AcXQPcECBAgQKCHgEDvoagNAgQIECAwWECgDy6A7gkQIECAQA8Bgd5DURsECBAgQGCwgEAfXADdEyBAgACBHgICvYeiNggQIECAwGABgT64ALonQIAAAQI9BAR6D0VtECBAgACBwQICfXABdE+AAAECBHoICPQeitogQIAAAQKDBQT64ALongABAgQI9BAQ6D0UtUGAAAECBAYLCPTBBdA9AQIECBDoISDQeyhqgwABAgQIDBYQ6IMLoHsCBAgQINBDQKD3UNQGAQIECBAYLCDQBxdA9wQIECBAoIeAQO+hqA0CBAgQIDBYQKAPLoDuCRAgQIBADwGB3kNRGwQIECBAYLCAQB9cAN0TIECAAIEeAgK9h6I2CBAgQIDAYAGBPrgAuidAgAABAj0EBHoPRW0QIECAAIHBAgJ9cAF0T4AAAQIEegj8L0xSnhnOSXoiAAAAAElFTkSuQmCC
I use the following code to convert to Byte()... The arrparms(22) is an element in an array that holds the base64 string above
Dim dataURI As String = arrparms(22) Dim split_1 As String() = dataURI.Split(";") Dim split_2 As String = split_1(1).Replace("base64,", "").Replace("base64", "") 'Convert base64 to byte Dim byteConvertedImage As Byte() = Convert.FromBase64String(split_2)
Then I create the parameter for the sql insert
objCommand.Parameters.Add("waste_signature", SqlDbType.VarBinary, 2147000000) objCommand.Parameters("waste_signature").Value = byteConvertedImage
The problem is when the insert is executed an error is generated that shows that the binary data or string is too big and would be truncated
and the statement has been terminated.
So I need a little direction on which datatype I should be using and/or how I should be inserting the binary data into the database table.
I have followed examples but have received the same error.Friday, November 17, 2017 5:54 AM
All replies
-
User991499041 posted
Hi mstrsftwr,
The problem is when the insert is executed an error is generated that shows that the binary data or string is too big and would be truncated and the statement has been terminated.
Please check all values when you get that exception in debugger. This error comes when you try to insert long data and database contain limited size. When there is mismatch between the passed-in column value from code and DB defined size of a column, you would generally get this issue.
Regards,
zxj
Friday, November 17, 2017 6:38 AM -
User364663285 posted
HI,
This is one column length problem and please try to increase your column length.
Monday, November 20, 2017 5:28 AM -
User347430248 posted
Hi mstrsftwr,
the cause for the error is ,"This error message appears when you try to insert a string with more characters than the column can maximal accommodate."
solution:
Errors of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You must either shorten the string to be isnerted to widen the column.
example to reproduce the issue:
USE tempdb; IF OBJECT_ID(‘tempdb.#t’) > 0 DROP TABLE #t GO CREATE TABLE #t ( c1 CHAR ); INSERT INTO #t SELECT ‘abc’ GO
In the above example we try to insert a string ‘abc’ with a length of 3 into the column c1 of the table #t. Because c1 is of the data type CHAR(1), the error is raised.
Reference:
String or binary data would be truncated.
Regards
Deepak
Monday, November 20, 2017 6:14 AM