executeUpdate Statement

(0) Share

Report

Posted on by Rabea Jalamneh

Hello Experts

I have an issue that i execute an insert into statement directly from X++ but the strange is that it inserts only 210 records while it suppose to insert more

when i copy the query to sql and run it, it inserts the correct number of records

appreciating your kind help

*This post is locked for comments

All responses (8)

Answers (0)

Rabea Jalamneh 10 on at

Like (0)

Report

RE: executeUpdate Statement

i debug the code and copy the sql statement and run it in sql it inserts the complete dates between starting date and ending date

i take the query with the values from the debugger also to make sure the variables is fine i create a stored procedure and replace the sql statement with it and also got the same result even i send the parameters as fixed values as follows

execute SP_SOH 'ItemID','DataareaId','2018-01-01','2018-05-31'
Suggested answer

nmaenpaa 101,156 on at

Like (0)

Report

RE: executeUpdate Statement

Then it's not the exact same query.

How did you verify that it inserts 210 records? Did you get count of records in SQL Server?

Are you sure your date variables have same values in both cases (not having any time zone differences)?

It seems your query should insert as many records that are between BegDate and Edate. But we don't see the values of these variables.

Also I don't see what is in other variables that you use.

Perhaps you should compare what your select statement returns in AX and SQL, maybe you notice that you have some difference in the criteria (because of the variables).

Basically the variables are the thing that can make all the difference, and we don't see the values of your variables.
Rabea Jalamneh 10 on at

Like (0)

Report

RE: executeUpdate Statement

hi Nikolaos

the problem is when i execute it from AX it inserts only 210 records but the same query in SQL inserts more than that
Suggested answer

nmaenpaa 101,156 on at

Like (0)

Report

RE: executeUpdate Statement

If there were any limits, they would be the same, no matter if you execute this T-SQL in SQL Server Management Studio or AX.
Rabea Jalamneh 10 on at

Like (0)

Report

RE: executeUpdate Statement

Dears,

Is There any limitation for insert into statements if its used inside executeupdate ??
Rabea Jalamneh 10 on at

Like (0)

Report

RE: executeUpdate Statement

hi Crispin

i will try

thank you
Rabea Jalamneh 10 on at

Like (0)

Report

RE: executeUpdate Statement

hi Crispin

The code Is below

str query,FDate,TDate;

Set permissionSet = new Set(Types::Class);

Connection connection;

Statement statement;

;

Fdate = date2str(BegDate, 321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4, DateFlags::None);

TDate = date2str(EDate, 321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4, DateFlags::None);

// Set the SQL statement

query = "DECLARE @DATE DATE";

query += " SET @DATE = '" + Fdate + "'";

query += " truncate table ItemSOHRep";

query += " WHILE @DATE <='" + TDate + "'";

query += " BEGIN";

query += " INSERT INTO ItemSOHRep ";

query += " SELECT ITEMID, @DATE, sum(iTrans.QTY) from INVENTTRANS as iTrans inner join INVENTDIM as iDim on iDim.INVENTDIMID = iTrans.INVENTDIMID";

query += " inner join INVENTLOCATION Loc On Loc.INVENTLOCATIONID = iDim.INVENTLOCATIONID";

query += " where ";

query += " iTrans.DATAAREAID IN (SELECT * FROM [dbo].[fnSplit]('" + Comps + "',',')) ";

query += " AND ";

query += " iDim.DATAAREAID IN (SELECT * FROM [dbo].[fnSplit]('" + Comps + "',',')) ";

if(Itm != "")

query += " and iTrans.ITEMID IN (SELECT * FROM [dbo].[fnSplit]('" + Itm + "',',')) ";

query += " and";

query += " loc.GOODWAREHOUSE = 1 ";

query += " AND ITEMID NOT LIKE 'ZZZ%'";

query += " and ";

query += " ((iTrans.DATEINVENT <> '' and iTrans.DATEINVENT <= @DATE ) OR";

query += " (iTrans.DATEINVENT = '' and iTrans.DATEPHYSICAL <> '' and iTrans.DATEPHYSICAL <= @DATE ))";

query += " group by iTrans.ITEMID";

query += " SET @DATE = DATEADD(dd,1,@DATE)";

query += " END";

connection = new Connection();

statement = connection.createStatement();

permissionSet.add(new SqlStatementExecutePermission(query));

CodeAccessPermission::assertMultiple(permissionSet);

statement.executeUpdate(query);

CodeAccessPermission::revertAssert();
Rabea Jalamneh 10 on at

Like (0)

Report

RE: executeUpdate Statement

Hi Crispin

The table i insert in is not AX Table i create it in SQL just to do aggregations