英文:
Why does OleDb reject a query that works in MS Access?
问题
我试图使用 C# 程序和 Microsoft.ACE.OLEDB.12.0 连接到 Access 2019 数据库，执行以下查询。当我在 Access 中运行该查询时，它可以正常工作。如何让它在我的程序中正常运行？但它在程序中失败，错误消息如下：
错误消息：
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 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="C:\AGAMES\AGAMES.accdb";Persist Security Info=True" providerName="System.Data.OleDb"/>
我在 Visual Studio 2022 版本 17.5 中调试了我的程序，并找到了引发异常的查询。然后，我在实际数据库中运行了该查询，它正常工作。但我需要在我的程序中自动化这个过程。
英文:
I am trying to execute the query below with a C# program using Microsoft.ACE.OLEDB.12.0 to connect to an Access 2019 database. The query works when I run it in Access. How can I get it to work in my program? It fails with
Error Message:
>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
Query:
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
Connection string:
<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"/>
I debugged my program in Visual Studio 2022 v 17.5 . I found the query that caused the exception. So, I ran that query in the actual database. It worked fine. I need to automate that in my program, though.
答案1
得分: 1
我想我应该更认真地看待错误消息。它绝对是正确的。我的一个字段太小了。
访问截断了它，所以查询在那里成功了。我猜Microsoft.ACE.OLEDB.12.0实际上帮了我一把，没有截断它。
英文:
I guess I should have taken the error message more seriously. It was absolutely correct. One of my fields was too small.
Access truncated it, so the query succeeded there. I guess Microsoft.ACE.OLEDB.12.0 actually did me a favor by not truncating it.**
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论