1

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.

Mike Steigerwald
  • 21
  • 4
  • Access does not use apostrophes to delimit object names. Remove the apostrophes used in the INSERT. Shouldn't save player name into PlayerAwardsMart, just player ID. – June7 Apr 06 '23 at 17:37
  • Those apostrophes (') are supposed to be backticks (`) . I guess markdown converted them somehow when I pasted into the question. I've run with and without them (`). The query succeeds in Access itself, but I get the error running the query using Microsoft.ACE.OLEDB.12.0 – Mike Steigerwald Apr 07 '23 at 11:51
  • No, I edited your post. Placing back ticks that way in posted code makes no sense. They appear as plain text and seem to be errors. Should remove them if you really do not have them or apostrophes in your actual code. – June7 Apr 07 '23 at 17:28

1 Answers1

1

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.**

Mike Steigerwald
  • 21
  • 4