locked
yet another The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. RRS feed

  • Question

  • User-146799434 posted

    I have checked, double checked, double checked squared the size of the text boxes, listitem content, etc updating to an access .mdb that I have been editing with access 2016.

    Is is possible there is some incompatibility here?

    here is the db open with my provider parameter

    strDbConnect = "provider=microsoft.jet.oledb.4.0;" & "data source=" & aPath & "\someName.mdb; mode=3"

    I have 42 fields to update with a query framework

    Using command As New System.Data.OleDb.OleDbCommand("donateAppsInsert1", db)

    here is the error messaging I'm trapping

    *** ERROR UPDATING DATABASE - SEE ERROR MESSAGE BELOW
    System.Data.OleDb.OleDbException: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
    at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
    at ASP.cardonateapp_aspx.updatedb() in C:\Users\me\Documents\Visual Studio 2008\WebSites\Donate\DonateApp.aspx:line 392
    ERROR UPDATING DATABASE = 5 - The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

    any ideas on ways to trouble shoot beside going  thru access field by field character size and in my case comparing to  max size parameter i have  for each text box and counting characters for the fields that are listitems?

    Tuesday, September 12, 2017 8:16 AM

Answers

  • User753101303 posted

    Hi,

    It shouldn't be that long to dump the length for each value just before ExecuteNonQuery and then to check that against the table definition. Not working with Access but my guess is that this message is rather for text values.

    Or if each parameter already includes the max length you could have some debug code that does a quick check to find which value is involved.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 12, 2017 8:48 AM

All replies

  • User753101303 posted

    Hi,

    It shouldn't be that long to dump the length for each value just before ExecuteNonQuery and then to check that against the table definition. Not working with Access but my guess is that this message is rather for text values.

    Or if each parameter already includes the max length you could have some debug code that does a quick check to find which value is involved.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 12, 2017 8:48 AM
  • User-1838255255 posted

    Hi bbxrider,

    According to your description, I found someone who meet the similar issue as you, please check:

    Error message when you run a query in Access: "The field is too small to accept the amount of data you attempted to add":

    https://support.microsoft.com/en-sg/help/896950/error-message-when-you-run-a-query-in-access-the-field-is-too-small-to

    Error: The field is too small to accept the amount of data you attempted to add:

    http://support.esri.com/en/technical-article/000004151 

    The field is too small to accept the amount of data you attempted to add. Has anyone gotten this error from ADO.NET?

    https://stackoverflow.com/questions/5984312/the-field-is-too-small-to-accept-the-amount-of-data-you-attempted-to-add-has-an 

    Best Regards,

    Eric Du

    Wednesday, September 13, 2017 6:49 AM
  • User-146799434 posted

    thanks for all the thougtful replies this is an insert invoking a query

    INSERT INTO donateApps ( appDate, firstName, lastName, addr1, addr2, city, state, zip, email, altContact, altContactRelation, addr1Diff, addr2Diff, cityDiff, stateDiff, zipDiff, phone1Type, phone1AC, phone1EX, phone1No, phone2Type, phone2AC, phone2EX, phone2No, caryear, make, model, bodyStyle, preference, title, driveable, regCurrent, amtDue, vehAttribAuto, vehAttribMan, vehAttrib2door, vehAttrib4door, licPlate, mileage, vin, condition, anythingElse )
    SELECT ? AS Expr1, ? AS Expr2, ? AS Expr3, ? AS Expr4, ? AS Expr5, ? AS Expr6, ? AS Expr7, ? AS Expr8, ? AS Expr9, ? AS Expr10, ? AS Expr11, ? AS Expr12, ? AS Expr13, ? AS Expr14, ? AS Expr15, ? AS Expr16, ? AS Expr17, ? AS Expr18, ? AS Expr19, ? AS Expr20, ? AS Expr21, ? AS Expr22, ? AS Expr23, ? AS Expr24, ? AS Expr25, ? AS Expr26, ? AS Expr27, ? AS Expr28, ? AS Expr29, ? AS Expr30, ? AS Expr31, ? AS Expr32, ? AS Expr33, ? AS Expr34, ? AS Expr35, ? AS Expr36, ? AS Expr37, ? AS Expr38, ? AS Expr39, ? AS Expr40, ? AS Expr41, ? AS Expr42;

    here is a listing of the fields and their sizes, they are all small text except the last field is a memo and for some reason its size lists out as zero but it is supposed to be 64k, to the right is the actual lengths of the variables from a form submittal. this is a web page

    appDate 25 datetime = 16
    firstName 50 firstName = 20
    lastName 50 lastName = 13
    addr1 50 addr1 = 25
    addr2 50 addr2 = 25
    city 25 city = 25
    state 2 state = 2
    zip 5 zip = 5
    email 50 email = 19
    altContact 30 altContact = 30
    altContactRelation 20 altContactRelation = 20
    addr1Diff 50 addr1Diff = 25
    addr2Diff 50 addr2Diff = 25
    cityDiff 20 cityDiff = 25
    stateDiff 2 stateDiff = 2
    zipDiff 5 zipDiff = 5
    phone1Type 4 phone1Type = 4
    phone1AC 3 phone1AC = 3
    phone1EX 3 phone1EX = 3
    phone1No 4 phone1No = 4
    phone2Type 4 phone2Type = 4
    phone2AC 3 phone2AC = 3
    phone2EX 3 phone2EX = 3
    phone2No 4 phone2No = 4
    caryear 4 year = 4
    make 15 make = 15
    model 20 model = 20
    bodyStyle 20 bodyStyle = 20
    preference 30 preference = 18
    title 8 title = 3
    driveable 15 driveable = 3
    regCurrent 8 regCurrent = 3
    amtDue 6 amtDue = 6
    vehAttrib2door 5 vehAttrib2door = 6
    vehAttrib4door 5 vehAttrib4door = 6
    vehAttribAuto 9 vehAttribAuto = 9
    vehAttribMan 6 vehAttribMan = 6
    licPlate 10 licPlate = 10
    mileage 6 mileage = 6
    vin 20 vin = 20
    condition 100 condition = 75
    anythingElse 0 anythingElse = 750

     as can be seen all the actual sizes are equal to or less than the table field size execept for the memo field

    my guess is the problem is somehow with the memo field since everything else is straight forward

    I've used this insert  technique successfully before with larger fields/text boxes going into memo fields 

    I even created a new query and pasted in the insert statement thinking somehow the original query got messed up internally, after all  this is ms :-)

    thanks again but it seems to be all those suggested posts don't really apply here, and actually I've seen a lot more than that!

    I know this is a lot to look at so thanks for any help

    Wednesday, September 13, 2017 8:08 AM
  • User-146799434 posted

    yikes how embarrassing, finally after posting and checking the post, did I find I have the rookie mistake fields too small

    sorry folks for wasting your time, hopefully this exercise will help somebody else not to make the same mistake

    there's not substitute for taking the time to get a complete db listing and displaying actual insert size values side by side

    btw there's no built in way to get the field info in access itself, I got a quick vba routine for listing out field info which could be helpful in future 

    and a bleery eyed good nite

    Wednesday, September 13, 2017 8:32 AM
  • User753101303 posted

    More likely it was cityDiff 20 cityDiff = 25

    The basic idea is that the input form should check the max input length according to the allowed size on the database side.

    Wednesday, September 13, 2017 8:35 AM