Tuesday, October 20, 2015

SQL Error on insert_recordset

Today, I found myself working on the LedgerTransListDate report.  Basically, it's a list of voucher transactions grouped by date.

My customer needed two fields added from the GeneralJournalEntry table; createdBy and createdDateTime.

In looking at the code I found I need to add some assignments in the 'while (queryRun.next())' section AND in the insert_recordset section.

When I tested it, I recieved the following SQL error:

SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

This didn't make any sense because I made sure to add my 2 fields on the field list AND on the select statement (in this case the 'join' of GeneralJournalEntry to GeneralJournalAccountEntry)

Included in the info log from AX, I found that the insert field list contained 3 custom fields instead of the 2 I had specified:


The last one was added somewhere, I'm just not sure where yet...

Stay tuned...

This is a 'flaw' (possibly) with using insert_recordset.  You can't use UTCDateTime fields with this functionality.

No comments:

Post a Comment