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.

Answer 1

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

Answer 2

User364663285 posted

HI,

This is one column length problem and please try to increase your column length.

Answer 3

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