-
Nov 5th, 2007, 08:09 AM
#1
Thread Starter
Frenzied Member
[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.
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:
')
-
Nov 5th, 2007, 08:10 AM
#2
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?
-
Nov 5th, 2007, 08:15 AM
#3
Thread Starter
Frenzied Member
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
-
Nov 5th, 2007, 08:18 AM
#4
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.
-
Nov 5th, 2007, 08:22 AM
#5
Thread Starter
Frenzied Member
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.
-
Nov 5th, 2007, 08:30 AM
#6
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?
-
Nov 5th, 2007, 09:54 AM
#7
Thread Starter
Frenzied Member
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!!!!
-
Nov 5th, 2007, 10:51 AM
#8
Thread Starter
Frenzied Member
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!!
-
Nov 5th, 2007, 11:01 AM
#9
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?
-
Nov 5th, 2007, 11:08 AM
#10
Thread Starter
Frenzied Member
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)
-
Nov 5th, 2007, 12:41 PM
#11
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
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.
-
Nov 5th, 2007, 12:54 PM
#12
Thread Starter
Frenzied Member
Re: The field is too small to accept the amount of data you attempted to add.
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?
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.
-
Nov 5th, 2007, 01:02 PM
#13
Re: The field is too small to accept the amount of data you attempted to add.
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).
-
Nov 5th, 2007, 01:36 PM
#14
Thread Starter
Frenzied Member
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 & "')"
-
Nov 5th, 2007, 01:40 PM
#15
Re: The field is too small to accept the amount of data you attempted to add.
That's it.
Does it solve the error?
-
Nov 6th, 2007, 09:42 AM
#16
Thread Starter
Frenzied Member
Re: The field is too small to accept the amount of data you attempted to add.
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|