locked
text or binary data will be truncated error RRS feed

  • 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