Problema super raro (SOLUCIONADO)

[Armando]

Amigos del foro, les cuento:

En una aplicación, hay un PRG que me tiene vuelto loco, me explico, con el siguiente código
agrego un registro a la tabla, el problema es que todo funciona al 100% pero solo
agrega dos registros, si agrego un tercer registro no lo graba , tengo otros PRGs
con la misma forma de trabajo y no hay problemas, solo en este PRG

Por cierto, la tabla donde se graban los registros es una tabla temporal, no hay mensajes de error
salgo del PRG y reviso la tabla y solo hay dos registros aunque adicioné mas de dos.

He puesto un COMMIT y tampoco funciona

Algun hilo?, ya copie código de otros PRGs, ya intenté con el comando INSERT y no hay éxito

Code: Select all  Expand view

   oRsWrk:AddNew()
   oRsWrk:Fields("WRK_FAC"):VALUE  := oWrk:FAC
   oRsWrk:Fields("WRK_TXT"):VALUE  := oWrk:TXT
   oRsWrk:Fields("WRK_UID"):VALUE  := oWrk:UID
   oRsWrk:Fields("WRK_RUB"):Value  := anRub[AScan(acRub,oWrk:RUB)]
   oRsWrk:Fields("WRK_CON"):Value  := anCon[AScan(acCon,oWrk:CON)]
   oRsWrk:Fields("WRK_CPT"):VALUE  := oWrk:CPT
   oRsWrk:Fields("WRK_IMP"):VALUE  := oWrk:IMP
   oRsWrk:Fields("WRK_IVA"):VALUE  := oWrk:IVA
   oRsWrk:Fields("WRK_SUB"):VALUE  := oWrk:SUB
   oRsWrk:Fields("WRK_IM2"):VALUE  := oWrk:IM2
   oRsWrk:Fields("WRK_TIP"):VALUE  := oWrk:TIP
   oRsWrk:Fields("WRK_TOT"):VALUE  := oWrk:TOT
   oRsWrk:UpDate()
   oRsWrk:ReQuery()
 

Auxilio que me estoy volviendo loco:

Mis herramientas FWH2307, Harbour y BCC7

Saludos

[nageswaragunupudi]

Code: Select all  Expand view

TRY
   oRsWrk:UpDate()
CATCH
   ? "Error"
   FW_ShowAdoError( oCn )
END
//   oRsWrk:ReQuery()
 

No need to use ReQuery() after oRs:Update().

[Armando]

Mr. Rao & Friends:

Thanks to your advice, the first problem is solved,
but now the problem is when retaking the recorded records
and displaying them in an xBrowse, it only shows 2 of 5 records

This is my code

Code: Select all  Expand view

      IF lCrealo
         DO WHILE ! oRsDet:EOF()
            oRsWrk:AddNew()
            oRsWrk:Fields("WRK_FAC"):Value   := oRsDet:Fields("DET_FAC"):Value      // Número de factura
            oRsWrk:Fields("WRK_TXT"):Value   := oRsDet:Fields("DET_TXT"):Value      // Texto del QR
            oRsWrk:Fields("WRK_UID"):Value   := oRsDet:Fields("DET_UID"):Value      // UUID
            oRsWrk:Fields("WRK_RUB"):Value   := oRsDet:Fields("DET_RUB"):Value      // Número de rubro
            oRsWrk:Fields("WRK_CON"):Value   := oRsDet:Fields("DET_CON"):Value      // Número de concepto
            oRsWrk:Fields("WRK_CPT"):Value   := oRsDet:Fields("DET_CPT"):Value      // Concepto
            oRsWrk:Fields("WRK_IMP"):Value   := oRsDet:Fields("DET_IMP"):Value      // Importe sin IVA
            oRsWrk:Fields("WRK_IVA"):Value   := oRsDet:Fields("DET_IVA"):Value      // Importe del IVA
            oRsWrk:Fields("WRK_SUB"):Value   := oRsDet:Fields("DET_SUB"):Value      // Importe con IVA
            oRsWrk:Fields("WRK_IM2"):Value   := oRsDet:Fields("DET_IM2"):Value      // Impuesto del 2%
            oRsWrk:Fields("WRK_TIP"):Value   := oRsDet:Fields("DET_TIP"):Value      // Propinas
            oRsWrk:Fields("WRK_TOT"):Value   := oRsDet:Fields("DET_TOT"):Value      // Total de la factura
            TRY
               oRsWrk:UpDate()
            CATCH
               FW_ShowAdoError(oApp:oCon)
            END

            oRsDet:MoveNext()
         ENDDO
//         oRsWrk:ReQuery()
         oRsWrk:MoveFirst()
      ENDIF
 

If I remove the oRsWrk:ReQuery() code
xBrowse is not shown, please see the images below

In the first one, only 2 of 5 records are shown, I'm sure there are 5 records, I've checked it with NaviCat


And in the second the xBrowse is not shown because the oRsWrk:Requery() code is removed


Best regards
I'm going to cry

[nageswaragunupudi]

Nothing to worry. You are not alone.
We are always here to support you in ever way.
At times we may take some time to respond.
Pls wait for my next posting

[nageswaragunupudi]

Do you want to copy all records from one table to another table and display them in XBrowse?

[Armando]

Mr. Rao:

Yes, I want to copy the records from a non-temporary table and pass them to a temporary table to modify them
and then return them to the non-temporary table

There are 5 records in the non-temporary table and only 2 are passed to the temporary table

Best regards

[nageswaragunupudi]

Ok.
This can be done in different ways.
Let us now try the 1st method.

FWH provides a cloud server for our testing. Let us use this server for our tests.

Creating temporary table with data from the permanent table:

Code: Select all  Expand view

oCn:Execute( "SELECT * INTO [TBL_TEMP] FROM [TBL_PERM]" )

Copying back modified data from temporary table to permanent table:

Code: Select all  Expand view

TEXT INTO cSql
UPDATE tbl_perm
 SET tbl_perm.code = tmp.code, tbl_perm.name = tmp.name
 FROM tbl_perm perm INNER JOIN tbl_temp tmp ON perm.id = tmp.id
ENDTEXT

   oCn:Execute( cSql )

Let us now test with a practical example:
First run this test as it is without any changes.

Code: Select all  Expand view

#include "fivewin.ch"
#include "adodef.ch"

static oCn

//----------------------------------------------------------------------------//

function Main()

   local oRs, cSql

   oCn   := FW_MSSQLDB()

   if oCn == nil; return nil; endif

   // We have 'tbl_perm' with 5 records
   oRs   := FW_OpenRecordSet( oCn, "tbl_perm" )
   XBROWSER oRs TITLE "tbl_perm : permanent table"
   oRs:Close()

   // We now copy 5 recs from tbl_perm to
   // new temporary table tbl_temp
   oCn:Execute( "DROP TABLE IF EXISTS [TBL_TEMP]" )
   oCn:Execute( "SELECT * INTO [TBL_TEMP] FROM [TBL_PERM]" )
   oRs   := FW_OpenRecordSet( oCn, "tbl_temp" )
   XBROWSER oRs TITLE "tbl_temp: for Edit" FASTEDIT ;
      SETUP ( oBrw:aCols[ 1 ]:lReadOnly := .t. )
   oRs:Close()

   // Copy back changes from tbl_tmp to tbl_perm
TEXT INTO cSql
UPDATE tbl_perm
 SET tbl_perm.code = tmp.code, tbl_perm.name = tmp.name
 FROM tbl_perm perm INNER JOIN tbl_temp tmp ON perm.id = tmp.id
ENDTEXT

   oCn:Execute( cSql )
   oCn:Execute( "DROP TABLE [TBL_TEMP]" )
   oRs   := FW_OpenRecordSet( oCn, "tbl_perm" )
   XBROWSER oRs TITLE "tbl_perm : permanent table"
   oRs:Close()

   oCn:Close()

return nil

//----------------------------------------------------------------------------//

After you run this sample as it is, then apply this logic to your tables.

[Armando]

Mr. Rao:

First of all, thank you very much for your support.

Whit this code

Code: Select all  Expand view

      oApp:oCon:Execute( "SELECT * INTO oRsWrk FROM oRsDet" )
 

I get this error

Error description: (DOS Error -2147352567) WINOLE/1007 [MySQL][ODBC 5.1 Driver][mysqld-5.1.53-community]Undeclared variable: oRsWrk (0x80004005): Microsoft OLE DB Provider for ODBC Drivers
Args:
[ 1] = C SELECT * INTO oRsWrk FROM oRsDet


with this code

Code: Select all  Expand view

         DO WHILE ! oRsDet:EOF()
            cCmdSql  := "INSERT INTO " +;
                           cTabNam + " " +;
                        "SET " +;
                           "WRK_FAC = '" + oRsDet:Fields("DET_FAC"):Value + "'," +;
                           "WRK_TXT = '" + oRsDet:Fields("DET_TXT"):Value + "'," +;
                           "WRK_UID = '" + oRsDet:Fields("DET_UID"):Value + "'," +;
                           "WRK_RUB = " + Str(oRsDet:Fields("DET_RUB"):Value,02,0) + "," +;
                           "WRK_CON = " + Str(oRsDet:Fields("DET_CON"):Value,03,0) + "," +;
                           "WRK_CPT = '" + oRsDet:Fields("DET_CPT"):Value + "'," +;
                           "WRK_IMP = " + Str(oRsDet:Fields("DET_IMP"):Value,11,2) + "," +;
                           "WRK_IVA = " + Str(oRsDet:Fields("DET_IVA"):Value,09,2) + "," +;
                           "WRK_SUB = " + Str(oRsDet:Fields("DET_SUB"):Value,11,2) + "," +;
                           "WRK_IM2 = " + Str(oRsDet:Fields("DET_IM2"):Value,09,2) + "," +;
                           "WRK_TIP = " + Str(oRsDet:Fields("DET_TIP"):Value,09,2) + "," +;
                           "WRK_TOT = " + Str(oRsDet:Fields("DET_TOT"):Value,11,2)

            TRY
               oApp:oCon:Execute(cCmdSql)
            CATCH oError
               MsgInfo("No pude ejecutar el comando " + cCmdSql,oApp:cAplicacion)
               FW_ShowAdoError(oApp:oCon)
            END

            oRsDet:MoveNext()
         ENDDO
         oRsWrk:ReQuery()
         oRsWrk:MoveFirst()
 

The temporary table has 5 records, checked with navicat, but the xBrowse only shows 2 records

Best regards

[nageswaragunupudi]

First please let me know if you built my sample program as it is (without any changes) and tested it? Did it work or not?

[nageswaragunupudi]

I get this error

Error description: (DOS Error -2147352567) WINOLE/1007 [MySQL][ODBC 5.1 Driver][mysqld-5.1.53-community]Undeclared variable: oRsWrk (0x80004005): Microsoft OLE DB Provider for ODBC Drivers
Args:
[ 1] = C SELECT * INTO oRsWrk FROM oRsDet

oRsWrk and oRsDet are RecordSets.
You should not use recordsets.
Use table names.

if the table names are "original" and "work", then the sql is

Code: Select all  Expand view

SELECT * INTO [WORK] FROM [ORIGINAL]

[Armando]

Mr. Rao and friends

I apologize, after several nightmare nights, I have finally solved the problem,
the problem was in the last line of this code.

[code]
oRsWrk := FW_OpenRecordSet(oApp:oCon,"SELECT " +;
"*," +;
"RUB_DES," +;
"CON_DES " +;
"FROM " +;
cTabNam + " " +;
"LEFT JOIN " +;
"Rubros " +;
"ON " +;
"WRK_RUB = Rubros.RUB_NUM " +;
"LEFT JOIN " +;
"Conceptos " +;
"ON " +;
"WRK_RUB = Conceptos.CON_RUB " +;
"AND " +;
"WRK_CON = Conceptos.CON_NUM " +;
"ORDER BY " +;
"WRK_ROW",cTabNam,adLockOptimistic,adOpenDynamic,0) <============
[code]

It seems silly but it was stupid.

I apologize, again

Best regards