Create New Record Access

[Colin Haig]

Hi All
I am trying to add a record to access database table but the update does not seem to work.

Regards

Colin

Code: Select all  Expand view  RUN

#define   EditCode   101
#define   BtnExit    102
#define   BtnNext    103
#define   BtnLast    104
#define   EditClient 105
#define   BtnAdd     106
#define   BtnSave    107
#include "fivewin.ch"
function fnAdd()
local oDlg,lExit := FALSE,oCon,oBtnExit,oBtnNext,oBtnLast,oBtnAdd,oBtnSave,aData := array(1),lNew := FALSE

oCon := fnConnect(oCon)
if ! empty(oCon)
   oRs  := TOleAuto():new( "ADODB.RecordSet" )
   oRs:ActiveConnection  := oCon
   oRs:Source            :=  "SELECT * FROM code"
   oRs:CursorType         := 1
   oRs:LockType           := 4            // adLockOptimistic
   oRs:CursorLocation     := 3            //adUseClient
   oRs:CacheSize          := 100
   TRY
      oRs:Open()
   CATCH
      MsgInfo('Table Open Failure')
   END


      DEFINE DIALOG oDlg RESOURCE 'ADD'
     
      REDEFINE GET oCode VAR aData[1]  ID EditCode   UPDATE picture '@!'
     
      REDEFINE BUTTON oBtnNext ID BtnNext of oDlg ;
             ACTION(oRs:MoveNext(),if(oRs:eof(),oRs:MoveLast(),),lfGetRecs(lNew,oRs,aData),oDlg:Update())
     
      REDEFINE BUTTON oBtnAdd ID BtnAdd of oDlg ;
             ACTION(lNew := TRUE,lfGetRecs(lNew,oRs,aData),oDlg:Update(),oDlg:aControls[1]:SetFocus())
     
      REDEFINE BUTTON oBtnSave ID BtnSave of oDlg ;
             ACTION(lfUpRecs(oRs,aData),oDlg:Update())
             
      REDEFINE BUTTON oBtnLast ID BtnLast of oDlg ;
             ACTION(oRs:MovePrevious(),if(oRs:bof(),oRs:MoveFirst(),),lfGetRecs(lNew,oRs,aData),oDlg:Update())

      REDEFINE BUTTON oBtnExit ID BtnExit OF oDlg ;
             ACTION(lExit := TRUE,oDlg:end())    
     
      ACTIVATE DIALOG oDlg CENTERED ;
            ON INIT(oRs:MoveFirst(),lfGetRecs(lNew,oRs,aData),oDlg:Update());
             VALID(lExit)
else            
   MsgInfo('Not Connected to Database')
endif

 
return(nil)
//---------------------------------------------------------------------------------------------------//
static function lfGetRecs(lNew,oRs,aData)
local i := 0
asize(aData,0)
for i := 1 to 1
   aadd(aData,if(lNew,space(7),oRs:Fields("code"):Value))
next
return(nil)
//---------------------------------------------------------------------------------------------------//
static function lfUpRecs(oRs,aData)
local i := 0
for i := 1 to len(aData)
   oRs:AddNew()
   oRs:Fields('code'):value := aData[1]
   oRs:Update()
next
return(nil)













 

[Colin Haig]

Hi All

Found by changing the oRs;LockType to 1 - I could enter and save the data -
after the data is entered and I try to skip forward or backwards the get is empty.

Regards

Colin

[Rick Lipkin]

Colin

Try it this way ..

Code: Select all  Expand view  RUN

oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic
 

Rick Lipkin

[Colin Haig]

Hi Rick

I have got my first little bit working - adding , deleting and updating records - I am trying to write
generic gather and scatter routines now. Any tips welcome. :- )

Cheers

Colin

[Rick Lipkin]

Colin

Might be a bit more complicated because of the fact SQL databases ( sometimes ) truncate ( empty) spaces and only store the actual data.

I have personally run into this when you assign the oRs:Field .. to a variable. Often times the length will be the length of the data and not the entire field length so I have had to pad the variable with spaces like

Code: Select all  Expand view  RUN

cVar := if(empty(oRs:Fields("whatever"):Value),space(50),;
              substr(oRs:Fields("whatever"):Value+space(50),1,50))
 

Here is some code you might want to consider .. this is a loop that goes thru a recordset and gets the names of the fields .. then you can perhaps assign it a variable name as you step thru the loop ..

Code: Select all  Expand view  RUN

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

cSql := "Select * from Customer where 1 = 2"  // always will return eof

TRY
  oRs:Open(cSQL,xCONNECT ) // xconnect is the puplic connection string
CATCH oErr
   MsgInfo( "Error Opening CUSTOMER Table")
   oDlg:End()
  Return(.f.)
END TRY

FOR i = 0 TO oRs:Fields:Count - 1
        FieldName := oRs:Fields( i ):Name
       PUBLIC z&FieldName     // use your own prefix.. i chose z
       z&FIELD_NAME := oRs:Fields(&FIELD_NAME):Value
Next    

oRs:CLose()

 

Above is un-tested .. and I would not use PUBLICs, unfortunitly, you can not use a macro expansion on local or static.

Just a few thoughts ..

Rick Lipkin

[James Bott]

Colin,

I have just sent via email a ADOBase class that is syntax compatible with TDatabase. This will allow you to save a record by just calling oRS:save(). Check it out.

I have not used it so I can't vouch for it, but the code looks good.

Regards,
James