ADO question: Access is not refresing properly

[lucasdebeltran]

Hello,

I am using an Access 2003 database via ADO in a single PC.

Sometimes, when I add the records the recordset is not refreshed, despite the call of oRs:Requery(), even twice. I have to wait 3-4 seconds and after that time it is refreshed.

I only experience such behaviour with Access, it is beeing a nighmare.

This is the connection string:

cStr := 'Provider='+"Microsoft.Jet.OLEDB.4.0"+';Data Source='+cBD



And this is how I open the recordset and the database:

LOCAL oError

DEFAULT cSQL := "SELECT * From TablaInexistente"



// open a recordset on demand with sql statement and connection string
oRsUser := TOleAuto():New( "ADODB.Recordset" )
oRsUser:CursorType := 1 // opendkeyset
oRsUser:CursorLocation := 3 // local cache
oRsUser:LockType := 3 // lockoportunistic


TRY
oRsUser:Open( cSQL, cStr )
oCn := oRsUser:ActiveConnection
oRdbms := FW_RDBMSName( oCn )

CATCH oError
ado_ErrorNoRecordSet(oError) // Mensaje de Error
oRsUser := nil
END



Maybe opportunistick lock?. But it is not used on network.

Any clue please?.

Thank you.

[Antonio Linares]

Lucas,

I am using ADO + Access on a development and I don't call oRs:Requery() when adding a record and here it works fine

I use:

oRS:MoveLast()
oRs:AddNew()
fill the right values
oRS:Update()

[Enrico Maria Giordano]

Antonio,

oRS:MoveLast()
oRs:AddNew()
fill the right values
oRS:Update()

You can safely remove oRS:MoveLast().

EMG

[lucasdebeltran]

Hello,

This is a complete test:
http://demo.ovh.es/en/9beda86cc9165fb7bae4af1cd65c05f9/

Just click on Añadir, and you will see that oRs is not updated:



Thank you.


.prg:

Code: Select all  Expand view  RUN

//
// Test oRs Not refreshing in Access
//
//---------------------


#include "FiveWin.ch"
#include "xbrowse.ch"
#include "report.ch"

#include "dbstruct.ch"
#include "adodef.ch"


STATIC oCn, cStr
STATIC oRsUser, oRdbms    // RecordSet


REQUEST HB_Lang_ES
REQUEST HB_CODEPAGE_ESWIN

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



// ---------------------------------------------------------------------------
FUNCTION MAIN()
// ---------------------------------------------------------------------------

   // Idioma español para Harbour--------------------------------------------
   HB_LangSelect("ESWIN") // Para mensajes, fechas, etc..
   HB_CDPSELECT("ESWIN") // Para ordenación, requiere CodePage.lib






   // Sets generales---------------------------------------------------------
   SetGetColorFocus()              // Cambiar el foco del GET
   SET EPOCH TO 1990               // Admite los años desde el 1990 en adelante
   SET CENTURY ON                  // 4 dígitos año
   SET DATE ITALIAN                // formato dd-mm-yyyy
   SET DELETED ON                  // Impedir ver registros marcados borrar
   SetCancel( .F. )                // Inutiliza ALT + C para abortar programa
   SetDialogEsc( .F. )             // Impide salir Diálogos con Escape
   SET( _SET_INSERT, .T. )         // Activa modo Insert

   XBrNumFormat( "E", .t. )        // Picture xBrowse formato Europeo decimales
   SetBalloon( .T. )               // Balloon shape required for tooltips



   ado_string()

   VerApuntes()


QUIT

RETURN NIL
// ---------------------------------------------------------------------------




// ---------------------------------------------------------------------------
// Función ....: VerApuntes
// Descripción.: Browse en pantalla de los apuntes
//               Desde ‚l se llaman las opciones de edici¢n, etc
// ---------------------------------------------------------------------------
FUNCTION VerApuntes()

   LOCAL oDlg, oBrowse
   LOCAL oBtn1, oBtn2, oBtn3, oBtn4, oBtn5, oBtn6

   LOCAL oRs
   LOCAL nPos := 0



   //
   // RecordSet
   //-----------
   oRs   := ado_AbreRecordSet( "select * from APUNTES order by FECHA ASC"  )

   IF oRs = nil
      RETURN NIL
   ENDIF





   //
   // Caja de Diálogo -----------------------------------------------------
   DEFINE DIALOG oDlg RESOURCE "APUNTES" ;
                      TITLE "Mantenimiento de los APUNTES contables"


   REDEFINE XBROWSE oBrowse  ID 101 OF oDlg ;
            DATASOURCE oRs ;
            COLUMNS "FECHA", "CUENTA", "APUNTE", "NINGRESO", "NGASTO", "NOTAS" ;
            HEADERS "Fecha", "Cuenta", "Apunte", "Ingreso", "Gasto", "Observaciones" ;
            AUTOCOLS AUTOSORT CELL LINES



   // Estilo-----------
   oBrowse:nMarqueeStyle       := 5 //9 // 5 por defecto MARQSTYLE_IDESOFT
   oBrowse:nHeaderLines        := 1.5
   oBrowse:nStretchCol         := STRETCHCOL_LAST
   oBrowse:lAllowColHiding     := .F.   // Impedir tocar columnas


   oBrowse:blDblClick := { || ( oBtn3:Click() ) }
   oBrowse:bKeyDown := { | nKey, nFlags | IF (nKey==VK_RETURN, oDlg:End(), ),;
                             IF (nKey==VK_ESCAPE, oDlg:End(), ) }





   REDEFINE BUTTON oBtn1 ID 711 OF oDlg ;
                  ACTION( AltasApuntes( .T. ), ;
                          oRs:Requery(), xbrowse(oRs), oBrowse:Refresh(), oBrowse:SetFocus() )

   REDEFINE BUTTON oBtn2 ID 712 OF oDlg ;
                  ACTION( ado_Borrar( oRs ), ;
                          oBrowse:Refresh() )

   REDEFINE BUTTON oBtn3 ID 713 OF oDlg ;
                  ACTION( nPos := oRs:AbsolutePosition, AltasApuntes( .F., oRs:AbsolutePosition ), ;
                          oRs:Requery(), oRs:AbsolutePosition := nPos, oBrowse:Refresh() )

   REDEFINE BUTTON oBtn4 ID 714 OF oDlg ;
                  ACTION( oRs:Requery(), oBrowse:Refresh() )

   REDEFINE BUTTON oBtn5 ID 715 OF oDlg ;
                  ACTION( oBrowse:Report("Apuntes´s list"), oBrowse:Refresh() )

   REDEFINE BUTTON oBtn6 ID 716 OF oDlg ACTION( oDlg:End() )






   ACTIVATE DIALOG oDlg CENTERED ON INIT oBrowse:SetFocus()


   ado_CierraRecordSet( @oRs )





RETURN NIL
// ---------------------------------------------------------------------------


// ---------------------------------------------------------------------------
// Función ....: AltasApuntes
// Descripción : A¤adimos un registro a la base de datos.
// Variables ..: lLogico -> valor l¢gico, para no duplicar c¢digos.
//               nCodigo -> para comprobar c¢digos mediante b£squeda.
// Notas ......: Comprobamos que el c¢digo introducido sea uno de nuevo.
// ---------------------------------------------------------------------------
FUNCTION AltasApuntes( lAppend, nRegistro )

   LOCAL oDlg                         // Objeto Diálogo
   LOCAL oRs, oData, oError
   LOCAL lSave       := .F.            // Grabado


   DEFAULT lAppend  := .F.            // Añadir






   //
   // Seleccionamos RecordSet
   //-------------------------
   oRs   := ado_AbreRecordSet( "select * from APUNTES order by FECHA ASC"  )

   IF oRs = nil
      RETURN NIL
   ENDIF





   IF lAppend                         // Si hay que añadir
      oData := TDataRow():New( oRs, nil, .t. )

      oData:Fecha    := Date()
      oData:Apunte   := "test sample in SUNDAY "+cvalToChar(datetime())
      oData:nIngreso := nrandom(10)

   ENDIF


   oData:Save()


   alert("added new record"+CRLF+CRLF+oData:Apunte)




   ado_CierraRecordSet( @oRs )



RETURN NIL
// ---------------------------------------------------------------------------







//----------------------------------------------------------------------------//
FUNCTION ado_String()
//----------------------------------------------------------------------------//

 LOCAL cMotor := "MSACCESS"

 LOCAL cBD    := HB_DIRBASE()+"ACCESS.MDB"



 DO CASE
    CASE cMotor == "MSACCESS"
         cStr  := 'Provider='+"Microsoft.Jet.OLEDB.4.0"+';Data Source='+cBD


    CASE cMotor == "MYSQL"
         cStr  := "Driver={MySQL ODBC 3.51 Driver};Server=dolphintest.sitasoft.net;" + ;
                  "Database=dolphin_man;User=test_dolphin;Password=123456;Option=3;"


 ENDCASE



RETURN cStr
//----------------------------------------------------------------------------//







//----------------------------------------------------------------------------//
//
// FUNCIONES PARA EL MANEJO DE RECORDSET
// -------------------------------------
//
//
//----------------------------------------------------------------------------//

//----------------------------------------------------------------------------//
FUNCTION ado_AbreRecordSet( cSQL )
//----------------------------------------------------------------------------//

    LOCAL oError

    DEFAULT cSQL := "SELECT * From TablaInexistente"



    // open a recordset on demand with sql statement and connection string
    oRsUser := TOleAuto():New( "ADODB.Recordset" )
    oRsUser:CursorType     := 1        // opendkeyset
    oRsUser:CursorLocation := 3        // local cache
    oRsUser:LockType       := 3        // lockoportunistic


    TRY
      oRsUser:Open( cSQL, cStr )
      oCn    := oRsUser:ActiveConnection
      oRdbms := FW_RDBMSName( oCn )

    CATCH oError
      ado_ErrorNoRecordSet(oError)   // Mensaje de Error
      oRsUser := nil
    END


RETURN oRsUser
//----------------------------------------------------------------------------//



//----------------------------------------------------------------------------//
FUNCTION ado_CierraRecordSet( oRs )
//----------------------------------------------------------------------------//
    local oErr


    TRY
       oRs:Close()
       oRs := Nil
    CATCH oErr
       ado_ErrorNoRecordSet(oErr)
    END



    oRs := Nil


RETURN NIL
//----------------------------------------------------------------------------//




//----------------------------------------------------------------------------//
FUNCTION ado_Borrar( oRs )
//----------------------------------------------------------------------------//
    LOCAL n
    LOCAL oErr1, oErr2, oErr3



    if MsgYesNo( "¿ Desea BORRAR este Registro ?."+CRLF+CRLF+"Si tiene dudas, seleccione No.", " B O R R A R   R E G I S T R O" )

       if oRs:RecordCount() = 0
          MsgAlert("ERROR: No hay ningún registro en la tabla."+CRLF+CRLF+"No hay nada que BORRAR.", " E R R O R ")
          RETURN NIL
       endif

       n := oRs:AbsolutePosition


       try
          oRs:Delete()
       catch oErr1
          MsgStop("ERROR: No se ha podido ejecutar la operación de borrado."+CRLF+CRLF+oErr1:Description, " E R R O R ")
          return(.f.)
       end


       ///oRs:Update()


       If !oRs:RecordCount() = 0
          oRs:AbsolutePosition := Min( n, oRs:RecordCount() )
       endif


       MsgInfo("El Registro ha sido BORRADO correctamente.", " A V I S O ")


    else
       MsgInfo("El USUARIO ha cancelado la operación de Borrar.", " A V I S O ")

    endif



RETURN NIL
//----------------------------------------------------------------------------//



//----------------------------------------------------------------------------//
FUNCTION ado_ErrorNoRecordSet(oErr)
//----------------------------------------------------------------------------//

   //local nErr, oErr, cErr
   local cErr
   local oConexion    := oCn
   local cInstruccion := "" //oErr:Args[1]


   //
   // fix oErr:Args[1], a veces está vacío
   //--------------------------------------
   if HB_IsNil( oErr:Args )
      cInstruccion := space(1)
   else
      cInstruccion := oErr:Args[1]
   endif



   //if ( nErr := oConexion:Errors:Count ) > 0
   //   oErr  := oConexion:Errors( nErr - 1 )
      WITH OBJECT oErr
         cErr     := "No se puede ejecutar la instrucción " + cValToChar( cInstruccion )
         cErr     += CRLF+CRLF+oErr:Description
         cErr     += CRLF+CRLF + 'Operación : ' + cValToChar( oErr:Operation )
         cErr     += CRLF+CRLF + 'Fuente del Error : ' + cValToChar( oErr:Filename )

         MsgAlert( cErr, "ADO ERROR RecordSet" )
      END
   //else
   //   MsgAlert( "ADO ERROR desconocido." )
   //endif


RETURN NIL
//----------------------------------------------------------------------------//




*-------------------------------------------------------------------------------
FUNCTION ADO_RecCount(oRs)
*-------------------------------------------------------------------------------

   local nRecord := 0

   nRecord := oRs:AbsolutePosition()
   nRecord := iif(nRecord=nil,-1,nRecord)

   if nRecord < 1
      return 0
   else
      return oRs:RecordCount()
   endif

return 0
*-------------------------------------------------------------------------------



 

.rc file:

Code: Select all  Expand view  RUN

#include <windows.h>
#include <commctrl.h>



APUNTES DIALOG -3, 1, 516, 289
STYLE 0x4L
CAPTION "BROWSE"
FONT 8, "MS Sans Serif"
{
 PUSHBUTTON "&Añadir", 711, 30, 260, 54, 14
 PUSHBUTTON "&Borrar", 712, 92, 260, 54, 14
 PUSHBUTTON "&Modificar", 713, 154, 260, 54, 14
 PUSHBUTTON "&Filtrar", 714, 216, 260, 54, 14
 PUSHBUTTON "&Imprimir", 715, 278, 260, 54, 14
 PUSHBUTTON "&Salir", 716, 444, 260, 54, 14
 CONTROL "", 101, "TXBrowse", 0 | WS_CHILD | WS_VISIBLE | WS_BORDER | WS_VSCROLL | WS_HSCROLL | WS_TABSTOP, 18, 18, 480, 228
}


 

[Enrico Maria Giordano]

Lucas,

Just click on Añadir, and you will see that oRs is not updated:

Try using adOpenDynamic:

http://www.w3schools.com/ado/met_rs_open.asp

EMG

[Rick Lipkin]

Lucas

You define oRs as your recordset for your xBrowse .. when you create a record .. pass oRs, and oBrowse to your add routine and DO NOT redefine oRs as local in that add routine.

oRs Represents the data that can be passed as a parameter .. in your add routine just oRs:AddNew() ( modify your fields ) .. and oRs:Update() then oBrowse:ReFresh() ..

oRs:ReQuery() is only helpful when you have a COMPLEX join in your recordset and adding or deleting a record to that complex oRs will cause un-intended consequences to those joined tables. IN that situation .. you can create a new recordset .. add your record and then oRs:Requery() the original recordset to make your new row visible.

Rick Lipkin

[lucasdebeltran]

Enrico,

Thank you, I am going to test it.


Rick,

I need to have an independant oRs in Add function -Añadir()-, as the add function can also be called out of the xBrowse.

So the strange think is that, despite the two Recordsets, Access do not refresh them sometimes at instant.

Have you tested my sample?.

Thank you. best regards

[lucasdebeltran]

Try using adOpenDynamic:

http://www.w3schools.com/ado/met_rs_open.asp

EMG

No effect, the same problem .

Thanks.

[Enrico Maria Giordano]

Lucas,

Try using adOpenDynamic:

http://www.w3schools.com/ado/met_rs_open.asp

EMG

No effect, the same problem .

Thanks.

Did you read the article? This is expected behavior for adOpenKeySet:

you can't see records that other users add

EMG

[lucasdebeltran]

Yes, I did and I tested it with no luck.

But when you add another record, the second, I see both after oRs:Requery().

With other RDDS I don´t have this problem.

Quite strange.

[nageswaragunupudi]

Mr Lucas

I am sending you a sample program to your personal e-mail. This is a modified version of the program you sent me. ReQuery is working perfectly here for me.
Please test it at your end and let me know the results.