delete

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi,

i made a form in Dexterity and i try to delete rows from two talbes but only the second delete is executed.

It looks like this:

SQL_COMMAND[1] = "DELETE FROM table1 ";
SQL_COMMAND[2] = "WHERE ITMCLSCD = 'something'";
status = SQL_Execute(SQL_connection, SQL_COMMAND[1]);

SQL_COMMAND[1] = "DELETE FROM table2 ";
SQL_COMMAND[2] = "WHERE ITMCLSCD = 'something'";
status = SQL_Execute(SQL_connection, SQL_COMMAND[1]);

i also checked the value of status and is equal to 59 after it passes through the first delete and zero after the second. I guess it should've been 0 for both.

thanks

*This post is locked for comments

All responses (5)

Answers (0)

Suggested answer

Pam Robertson 199 on at

Like (0)

Report

RE: delete

I was getting the same results on a script that contained several SQL_Execute statements in a row; every other SQL_Execute returned status 59. I found that if I did a SQL_Terminate and then SQL_Connect in between the SQL_Executes all executed properly. I hope this will help someone else.
Community Member Microsoft Employee on at

Like (0)

Report

Re: Re: delete

i re-arranged the order of sql queries and somehow it works

thank u
Rashid Farooq 1,000 on at

Like (0)

Report

Re: delete

Using following code create a Dex Procedure

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------{---------------------------------------------------------------
Auther   :  Rashid Farooq
Project   :  Haier Company
Company   :   Ovex Tech - Pakistan
Date Modified :  14 April 2009
----------------------------------------------------------------
Copyright (c) Ovex Technlogies (Pvt) Limited
----------------------------------------------------------------}
pragma(disable warning LiteralStringUsed);

in text txtQuery;
local text SQLStatement;
local long lngResult,
SQL_connection,
GPS_error_number,
SQL_error_number;

local string SQL_error_string,
ODBC_error_string;
try
lngResult = SQL_Connect(SQL_connection);     {Connection to SQL Server}
if lngResult <> 0 then exit try; end if;
SQLStatement = "use " + 'Intercompany ID' of globals;   {Crating SQL statement to change the Database}
lngResult = SQL_Execute(SQL_connection,SQLStatement);  {Executing SQL Statements}
if lngResult <> 0 then exit try; end if;
SQLStatement=txtQuery;          {Passed Query}
lngResult = SQL_Execute(SQL_connection,SQLStatement);  {Executing SQL Statements}
if lngResult <> 0 then exit try; end if;
else
error "Unable to retrieve SQL error information.";
end try;
if lngResult <> 0 and lngResult <> 31 then
lngResult = SQL_GetError(SQL_connection,GPS_error_number, SQL_error_number,SQL_error_string, ODBC_error_string);
  if lngResult = 0 then
   warning "GPS Error: " + str(GPS_error_number) + char(13) +
     "SQL Error: " + str(SQL_error_number) + " " + SQL_error_string + char(13) {+
     "ODBC Error: " + ODBC_error_string};
  else
   error "Unable to retrieve SQL error information.";
  end if;
end if;
lngResult = SQL_Terminate(SQL_connection);

pragma(enable warning LiteralStringUsed);
{---------------------------------------------------------------
End Of Script
----------------------------------------------------------------}

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now, call ExecuteQuery,"DELETE TABLE1 WHERE PERSON = "ABC";

Now, call ExecuteQuery,"DELETE TABLE2 WHERE PERSON = "ABC";
Community Member Microsoft Employee on at

Like (0)

Report

Re: Re: delete

I have used it like this many times before. It is actually running all lines.

I also tried the sql in an sql query and it works.
winthropdc on at

Like (0)

Report

Re: delete

Hi there

I am not exactly sure what error 59 means, but you can try running the code on a SQL Query window to test it.

But first, the code you have shown in the example wll run only the first SQL_COMMAND array element which will delete the entire table contents as the where clause has not been applied.

I would suggest changing your method of building the SQL command to execute.

local text SQL_Commands;

clear SQL_Commands;
SQL_Commands = SQL_Commands + "DELETE FROM table1 ";
SQL_Commands = SQL_Commands + "WHERE ITMCLSCD = 'something'";

status = SQL_Execute(SQL_connection, SQL_Commands);

Also have a look at the articles on http://blogs.msdn.com/developingfordynamicsgp/pages/dexterity-articles.aspx in the SQL Server section, in particular:

How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity (KB 910129)

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;910129

David

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David.Musgrave@online.microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.

delete

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

delete

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates