asked on

SQL update of Microsoft Dynamics GP table using FoxPro 9

I am having a problem updating integer and numeric fields in a Microsoft Dynamics GP table using a stored variable. Here is an example of the code.

**** Update Purchase ORder in Great Plains after edit
** PONUX listed below is public stored variable returned from a form
STORE SQLCONNECT('MCC_GP','userid', 'password') TO MCC_conn
select podisp
SCAN
STORE quantity TO upquant
STORE item_no TO upitem
STORE (quantity*unitcost) TO uptot
STORE poline TO upline
SQLEXEC(mcc_conn,"update two.dbo.pop10110;
            set qtyorder = &upquant, extdcost = &uptot;
            where ponumber = '&ponux' and linenumber = &upline")
ENDSCAN

******* For testing purposes I have passed explicit values in the integer and number fields as listed below when the code is passed with explicit values the update is successful
**** Update Purchase ORder in Great Plains after edit
STORE SQLCONNECT('MCC_GP','userid', 'password') TO MCC_conn
select podisp
SCAN
STORE quantity TO upquant
STORE item_no TO upitem
STORE (quantity*unitcost) TO uptot
STORE poline TO upline
SQLEXEC(mcc_conn,"update two.dbo.pop10110;
            set qtyorder = 13, extdcost = 1300;
            where ponumber = '&ponux' and linenumber = 1")
ENDSCAN

This only appears to be occuring on the integer and numeric fields in the GP table. I am able to update character fields using macro substitution and the variable. I have updated hundreds of Oracle, MYSql and other SQL server tables using this type of code. Normally in all other ODBC compliant databases I pass integer and number values as &variable (without quotes) and character values as '&variable' (with quotes).

I have made sure the fields from the FoxPro cursor are defined identical to the GP fields but yet I am having absolutely no luck trying to update numeric or integer fields in GP using a variable. I am declaring the variables in the form load event before calling the above program from the form. The only way I can update the integer or numeric fields in the GP table is by passing explicit numbers. Does anybody have any suggestions? Please help I am completely frustrated at this point!

Thanks in advance.

Pavel Celba

Your biggest problem is no check for errors in your code. SQLEXEC() is returning a value and if this value means "NO SUCCESS" you have to call AERROR() function for error description... Maybe you are even not connected because you are not checking SQLCONNECT() result.

Let suppose your connection and SQL commands are OK. Then the problem is in & character used for
macro substitution.

Macro substitution requires character variable. If you try to use numeric variables then the result is not macro substitution but &uptot remains in the SQL command...

SQL pass-through allows parameters of almost any data type. You cannot use &macro but you have to use ? prior to memory variable. Then you even don't need apostrophs around the character values:

STORE SQLCONNECT('MCC_GP','userid', 'password') TO MCC_conn

IF MCC_conn < 0
  *-- Connection not successful
  RETURN
ENDIF

LOCAL lnRetVal, laErr[1]

select podisp
SCAN
  STORE quantity TO upquant
  STORE item_no TO upitem
  STORE (quantity*unitcost) TO uptot
  STORE poline TO upline
  
  lnRetVal = SQLEXEC(mcc_conn, "update two.dbo.pop10110 ;
            set qtyorder = ?upquant, extdcost = ?uptot ;
            where ponumber = ?ponux and linenumber = ?upline")
  
  IF lnRetVal < 0
    = AERROR(laErr)
    *-- Now you may process the error info.
    *-- This command just displays it which is not good approach...
    DISPLAY MEMORY LIKE laErr
  ENDIF
ENDSCAN

Open in new window

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patmac951

ASKER

pcelba,
Thanks so much I appreciate it. That did the trick! I have not written any FoxPro code for quite awhile and obviously I am a bit rusty.
Thanks again

Pavel Celba

You are welcome!