[RESOLVED] The field is too small to accept the amount of data you attempted to add.-VBForums
Results 1 to 16 of 16

Thread: [RESOLVED] The field is too small to accept the amount of data you attempted to add.

  1. #1

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Resolved [RESOLVED] The field is too small to accept the amount of data you attempted to add.

    I don't understand this problem, or rather why I am getting it!

    I use field values:
    • Text
    • Memo
    • Date/Time
    • Autonumber


    All the text fields are set to 50 chars, and the longest string I am inputting is 14 chars!
    The memo field is less than 200 chars.
    The date/time fields are set to ShortDate (Format - 00/00/0000;0;#)

    The Execute code works with other records, so I cannot see what is wrong with this record.

    This is only a test DB with made up details, but should still work.
    The last record added is inot the memo field.

    Quote Originally Posted by debug.Print
    Code:
    strsql2
    INSERT INTO tbl_EX_Membership Values  (16,
    'Paul',
    'Earl',
    'Limit',
    '16 Forever Ave',
    'Filton',
    'Wiltshire',
    '33W E4',
    '36562326',
    '3542365',
    'Help@please',
    #09/09/1984#,
    #03/06/1966#,
    #12/12/2007#,
    '2ND DAN',
    #23/06/2004#,
    '6788',
    'manager',
    'Male',
    'We have now amended our guidance to reflect the increase in the holiday entitlement: 
    ')
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

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

    Which of the fields is it giving the error on?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

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

    I'm not sure Hack.
    That's why I showed the Debug.Print.

    I did read a post of yours when searching, and you asked the same question, but the whole of the strSQL2 is highlighted in yellow when stepping through the code and when it reaches the Execute line, it goes to the errhandler.

    My code (Which has worked with every other record I have used it for)
    Code:
    strSQL = "SELECT * FROM tbl_Membership"
                strSQL = strSQL & " WHERE Memb_ID = " & lngRecToDelete
                
                rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
                
                Dim strDOB As String
                Dim strDate_Joined As String
                Dim strInsurance_Due As String
                Dim strLast_Graded As String
                
                strDOB = "NULL"
                strDate_Joined = "NULL"
                strInsurance_Due = "NULL"
                strLast_Graded = "NULL"
                
                If Not IsNull(rs!DOB) Then
                   strDOB = "#" & rs!DOB.Value & "#"
                End If
                If Not IsNull(rs!Date_Joined) Then
                   strDate_Joined = "#" & rs!Date_Joined.Value & "#"
                End If
                If Not IsNull(rs!Insurance_Due) Then
                   strInsurance_Due = "#" & rs!Insurance_Due.Value & "#"
                End If
                If Not IsNull(rs!Last_Graded) Then
                   strLast_Graded = "#" & rs!Last_Graded.Value & "#"
                End If
                
                Dim strSQL2 As String
                strSQL2 = "INSERT INTO tbl_EX_Membership"
                strSQL2 = strSQL2 & " Values  (" & rs!Memb_ID.Value & "," & vbCrLf & _
                                             "'" & rs!First_Name.Value & "'," & vbCrLf & _
                                             "'" & rs!Middle_Name.Value & "'," & vbCrLf & _
                                             "'" & rs!Surname.Value & "'," & vbCrLf & _
                                             "'" & rs!Address.Value & "'," & vbCrLf & _
                                             "'" & rs!Town.Value & "'," & vbCrLf & _
                                             "'" & rs!County.Value & "'," & vbCrLf & _
                                             "'" & rs!Post_Code.Value & "'," & vbCrLf & _
                                             "'" & rs!Home_Tel.Value & "'," & vbCrLf & _
                                             "'" & rs!Mobile_Tel.Value & "'," & vbCrLf & _
                                             "'" & rs!Email.Value & "'," & vbCrLf & _
                                             "" & strDOB & "," & vbCrLf & _
                                             "" & strDate_Joined & "," & vbCrLf & _
                                             "" & strInsurance_Due & "," & vbCrLf & _
                                             "'" & rs!Grade.Value & "'," & vbCrLf & _
                                             "" & strLast_Graded & "," & vbCrLf & _
                                             "'" & rs!Membership_Number.Value & "'," & vbCrLf & _
                                             "'" & rs!Occupation.Value & "'," & vbCrLf & _
                                             "'" & rs!Gender.Value & "'," & vbCrLf & _
                                             "'" & rs!Notes.Value & "')"
                
                cn.Execute strSQL2
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  4. #4
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

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

    If you aren't sure, and this is just a test record, then do an Insert with just one text field. If that works, try another one. You should be able to find out which one it is by a simple (although potentially time consuming) process of eliminiation.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  5. #5

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

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

    Good idea I will give it a go.

    Just another question, remind me how to adapt this to just insert one record

    This code is in a menu item to remove a record, which is a record selected from a listview.
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  6. #6
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

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

    Code:
    sSQL = "INSERT INTO tablename (field1, field2, field3) VALUES ('Hack', 'aikidokid', 'si_the_geek') "
    cn.Execute sSQL
    Is this what you mean?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  7. #7

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

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

    Hmm

    I have added all fields individually and it adds all of them.
    I tried this twice and I also used this code to remove a different record and it worked!!!!
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  8. #8

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

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

    Is trying to put too many characters into a string field the only reason for this type of error?

    I just cannot see what is causing it!!
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  9. #9
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

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

    Maybe it is the date/time field. What are you adding to it?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  10. #10

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

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

    I did check these as well.

    They are all validated before they are added to strings for the Insert, as in my code above.

    Code:
                If Not IsNull(rs!Date_Joined) Then
                   strDate_Joined = "#" & rs!Date_Joined.Value & "#"
                End If
    Insert statement
    Code:
    "" & strDate_Joined & "," & vbCrLf & _
    The format is UK format - 05/11/2007 - but I'm not having any problems with ALL the other test records (approx 25)
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,192

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

    One thing that is missing from your original code is a field list - I suspect that is causing the problem, as the fields are not quite in the order your code expects (so the wrong fields are being used).

    Try adding the field list like this:
    Code:
    INSERT INTO tbl_EX_Membership (Field1, Field2, ...) Values
    Quote Originally Posted by aikidokid
    The format is UK format - 05/11/2007 - but ...

    But nothing - do not ever, under any circumstances, use UK format dates in SQL statements - always use US or ISO formats.

    Using a UK format will damage your data and/or give you the wrong query results at some point.

    It's annoying, but something that is very important for you to remember and stick to.

  12. #12

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

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

    Quote Originally Posted by si_the_geek
    One thing that is missing from your original code is a field list - I suspect that is causing the problem, as the fields are not quite in the order your code expects (so the wrong fields are being used).
    Perhaps you could clarify this point for me please.
    I was told, on this forum, some time ago now, that as the two tables have identical fields (one being for current members details and the other being for lapsed members details) I could use the above code to copy from one table to the other.
    Is this not correct?

    Quote Originally Posted by si_the_geek
    Using a UK format will damage your data and/or give you the wrong query results at some point.
    Hmm, I did know this - probably you that told me along with many others.
    This one must have got past me

    I will have to change this.
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,192

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

    Quote Originally Posted by aikidokid
    Perhaps you could clarify this point for me please.
    I was told, on this forum, some time ago now, that as the two tables have identical fields (one being for current members details and the other being for lapsed members details) I could use the above code to copy from one table to the other.
    Is this not correct?
    If that was what you were doing, you could do it like that - but I would not recommend it, as it will fail if the design of either of the tables change (such as the order of columns changes).

    The "if" is because you are not doing that - you are simply running a single insert statement (you happen to be creating it based on data from a table, but that is irrelevant - as they aren't part of the same SQL statement, they are separate things).

  14. #14

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

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

    Is this what you mean?
    Code:
                strSQL2 = "INSERT INTO tbl_EX_Membership"
                strSQL2 = strSQL2 & "(Memb_ID, First_Name, Middle_Name, Surname, Address, Town, County, " & _
                                     "Post_Code, Home_Tel, Mobile_Tel, Email, DOB, Date_Joined, Insurance_Due, " & _
                                     "Grade, Last_Graded, Membership_Number, Occupation, Gender, Notes)"
                strSQL2 = strSQL2 & " Values  (" & rs!Memb_ID.Value & "," & vbCrLf & _
                                             "'" & rs!First_Name.Value & "'," & vbCrLf & _
                                             "'" & rs!Middle_Name.Value & "'," & vbCrLf & _
                                             "'" & rs!Surname.Value & "'," & vbCrLf & _
                                             "'" & rs!Address.Value & "'," & vbCrLf & _
                                             "'" & rs!Town.Value & "'," & vbCrLf & _
                                             "'" & rs!County.Value & "'," & vbCrLf & _
                                             "'" & rs!Post_Code.Value & "'," & vbCrLf & _
                                             "'" & rs!Home_Tel.Value & "'," & vbCrLf & _
                                             "'" & rs!Mobile_Tel.Value & "'," & vbCrLf & _
                                             "'" & rs!Email.Value & "'," & vbCrLf & _
                                             "" & strDOB & "," & vbCrLf & _
                                             "" & strDate_Joined & "," & vbCrLf & _
                                             "" & strInsurance_Due & "," & vbCrLf & _
                                             "'" & rs!Grade.Value & "'," & vbCrLf & _
                                             "" & strLast_Graded & "," & vbCrLf & _
                                             "'" & rs!Membership_Number.Value & "'," & vbCrLf & _
                                             "'" & rs!Occupation.Value & "'," & vbCrLf & _
                                             "'" & rs!Gender.Value & "'," & vbCrLf & _
                                             "'" & rs!Notes.Value & "')"
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,192

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

    That's it.

    Does it solve the error?

  16. #16

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

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

    Quote Originally Posted by si_the_geek
    That's it.

    Does it solve the error?
    YES!!!! Thanks I was begining to think this one was going to beat me!
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.