Access正常运行的INSERT查询为何被OleDb拒绝？报错字段容量不足

报错信息 #

System.Data.OleDb.OleDbException HResult=0x80040E57 Message=The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data

执行的INSERT查询 #

INSERT INTO `PlayerAwardsMart` (`tournament`, `competition`, `place`, `award`, `player_id`, `player`, `registration_group`, `team`) 

SELECT        LatestActiveTournament.label AS tournament, PlayerResultsMart.Competition AS competition, PlayerResultsMart.Rank AS place, PlayerResultsMart.Award AS award, PlayerResultsMart.ID AS player_id, 
                         PLAYER.[FIRST] + ' ' + PLAYER.[LAST] AS player, GEOCODE.CITY AS registration_group, TEAM.TEAM_NAME AS team
FROM            LatestActiveTournament, ((GEOCODE INNER JOIN
                         (PlayerResultsMart INNER JOIN
                         PLAYER ON PlayerResultsMart.ID = PLAYER.ID) ON GEOCODE.GEOCODE = PLAYER.GEOCODE) INNER JOIN
                         TEAM ON PlayerResultsMart.TeamID = TEAM.ID)
WHERE        (PlayerResultsMart.Award IS NOT NULL)
ORDER BY PlayerResultsMart.Competition, PlayerResultsMart.Rank, PlayerResultsMart.ID

连接字符串 #

<add name="DataGrids.Properties.Settings.agamesConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&quot;C:\AGAMES\AGAMES.accdb&quot;;Persist Security Info=True" providerName="System.Data.OleDb"/>

解决方法 #

• 核对目标表字段与源数据长度：报错核心是某列数据长度超过PlayerAwardsMart对应字段的定义上限。逐一检查：
  • 比如player列由FIRST+空格+LAST拼接而成，计算该拼接结果的最大长度，确认PlayerAwardsMart的player字段长度足够（若字段是Text(50)，但拼接后可能超过50字符）
  • 检查competition、award、team等字段，对比源表对应列的最大长度与目标字段的限制
• 排查Access与OLEDB的执行差异：Access运行查询时可能自动截断超长数据，而OLEDB驱动会严格校验。在Access中单独执行SELECT语句，导出结果后统计各列的最大长度，和目标表字段做对比
• 调整字段或截断数据：
  • 若字段长度不足，修改PlayerAwardsMart对应字段的属性（比如将Text改为Long Text，或增大长度值）
  • 若无法修改表结构，在SELECT语句中对超长列做截断，例如LEFT(PLAYER.[FIRST] + ' ' + PLAYER.[LAST], 50)（数值根据目标字段长度调整）
• 优化连接字符串：去掉不必要的Persist Security Info=True（无密码的Access数据库无需该参数），同时确认数据库文件路径有读写权限
• 定位具体违规数据：在程序中先执行SELECT语句，遍历结果集输出各列的长度，找到触发报错的具体行和列，针对性处理

内容的提问来源于stack exchange，提问作者Mike Steigerwald