Last post Sep 13, 2017 08:35 AM by PatriceSc
Sep 12, 2017 08:16 AM|bbxrider|LINK
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 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?
Sep 12, 2017 08:48 AM|PatriceSc|LINK
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.
Sep 13, 2017 06:49 AM|Eric Du|LINK
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":
Error: The field is too small to accept the amount of data you attempted to add:
The field is too small to accept the amount of data you attempted to add. Has anyone gotten this error from ADO.NET?
Sep 13, 2017 08:08 AM|bbxrider|LINK
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
Sep 13, 2017 08:32 AM|bbxrider|LINK
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
Sep 13, 2017 08:35 AM|PatriceSc|LINK
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.