TDataRow and ADO questions

[elvira]

Hi friends,

The new class TDataRow is very exciting. We need to move to ADO as SQLRDD is a patch and does not work fine. I have some questions please:

First of all, I would appreciate a sample of TDataRow for adding a new record, consult a record, modify a record, locate and delete, please.

Second, how do I load an array with a field from the Recordset so as to have, i.e., a combobox?. Here is how I do now:

Code: Select all  Expand view

  SELECT ("TIPO")
   DbGoTop()
   DO WHILE !Eof()
      aAdd( aTipos, Field->TIPO )
      dbSkip()
   ENDDO
   DbGoTop()


   REDEFINE COMBOBOX cTipo    ;
            ITEMS aTipos    ID 700 OF oFld:aDialogs[ 1 ]


 

Third, what is the equivalent for recordset DO WHILE !EOF() and, also, Dbskip()?.

Thank you a lot!.

[Enrico Maria Giordano]

Elvira,

Second, how do I load an array with a field from the Recordset so as to have, i.e., a combobox?

Code: Select all  Expand view

  oRs:MoveFirst()
   DO WHILE !oRs:EOF
      aAdd( aTipos, oRs:Fields( "TIPO" ):Value )
      oRs:MoveNext()
   ENDDO

EMG

[nageswaragunupudi]

The new class TDataRow is very exciting

We expect that this class will be very useful. Now it is useful for RDD, ADO, TDataBase, XBrowse with facility to build custom data. This class can be used to edit and appends.
We shall give enough examples and explanations soon about this class.

how do I load an array with a field from the Recordset so as to have, i.e., a combobox?. Here is how I do now

You have given a sample code of how you are now doing it with DBF.
Before we discuss ADO, let us see another simpler way to do it with DBF.

Code: Select all  Expand view

TIPO->( DbGoTop() )
aTipos := ArrTranspose( TIPO->( FW_DbfToArray( "TIPO" ) ) )[ 1 ]
TIPO->( DbGoTop() )
 

Now, let us come to ADO.
You open the record set as you normally do. Using FW built-in functions simplify your work.

Method-1: Same way very similar to how you are extracting the array from DBF

Code: Select all  Expand view

aTipos  := {}
oRs:MoveFirst()
do while ! oRs:Eof()
   AAdd( aTipos, oRs:Fields( "TIPO" ):Value )
   oRs:MoveNext()
enddo
oRs:MoveFirst()
 

Now let us see if we have better alternatives.

Recordset has a method GetRows() which reads entire record set into a multi-dimentional array.

aData := oRs:GetRows()

Now let us use this method to get our work done.
I shall give here a sample using STATES.DBF in c:\fwh\samples folder. I shall open this table through ADO, using FW functions.

Code: Select all  Expand view

function ReadStatesArray()

   local oCn, oRs, aStates

   if ( oCn := FW_OpenAdoConnection( "c:\fwh\samples" ) ) != nil
      if ( oRs   := FW_OpenRecordSet( oCn, "SELECT CODE FROM STATES" ) ) != nil
         oRs:MoveFirst()
         aStates := ArrTranspose( oRs:GetRows() )[ 1 ]
         oRs:MoveFirst()
         oRs:Close()
      else
         ? "Open Table Fail"
      endif
      oCn:Close()
   else
      ? "Connect Fail"
   endif

return aStates
 

what is the equivalent for recordset DO WHILE !EOF() and, also, Dbskip()?.

oRs:MoveFirst() // GoTop
oRs:MoveLast() // gobottom
oRs:MoveNext() // skip( +1 )
oRs:MovePrevious() // skip( -1 )
oRs:Move( n ) // Skip( n )
oRs:Bof() and oRs:Eof() // same as dbf

Advise you to learn all about ADO from w3schools website.
http://www.w3schools.com/ado/default.asp
You will learn almost all about ADO.

[nageswaragunupudi]

Class TDataRow
--------------
Class TDataRow simplifies the scatter/gather process of the field values of a single record of a table for purposes of editing/appending. This class can read all or specified field values from a single row of DBF table,ADO RecordSet, TDatabase object or XBrowse and save the values after editing to the same record.

After we specify the datasource (dbf/ors,etc) in the New() method, we can use the class the sameway for all kinds of datasources.

Datas and Methods:

DATAS:
aData : MultiDimentional Array of FieldName, FieldValue Read by Method New()
lReadOnly : Logical default .t.
RecNo : Record No. ( 0 in case of Blank/New )
bSave : Optional codeblock to override default Save() method
bEdit : Optional codeblock to override default Edit() method
bOnSave : Optional cpdeblock. Executes at the end of Save() method
lValidData : ReadOnly Logical.

METHODS:
New( [cAlias|oRecSet|oDbf|oBrw( Default Alias() )], [cFieldList (Def AllFields)], [lBlank (Def .f. )] )
Edit( [lReadOnly] ) : Default Edit dialog provided.
Undo() : Restores all values to original values when first read
Save() : saves modified values to the data source
Modified( [nPos (def All) ) : Returns .t. if specified field or any field is changed
FCount() : Total number of fields read
FieldPos( cName ) : Field Number of a field name
FieldName( nPos ) : Name of field at pos
FieldType( nPos ) : Type of field at pos

Usage:

DBF:

USE CUSTOMER NEW ALIAS CUST
oRec := TDataRow():New( "CUST" )
? oRec:RecNo,oRec:Age, oRec:Salary
oRec:Salary += 2000
? oRec:Salary
? oRec:Modified()
oRec:Save()

RecordSet:

oRs := FW_OpenRecordSet( "c:\fwh\samples\xbrtest.mdb", "CUSTOMER" )
oRec := TDataRow():New( "CUST" )
? oRec:RecNo,oRec:Age, oRec:Salary
oRec:Salary += 2000
? oRec:Salary
? oRec:Modified()
oRec:Save()

Same way for oDbf, oBrw.

We can make our own GET dialog using oRec:<fldnames> as get variables. We can use oRec:Undo() to cancel changes and finally oRec:Save() to
save the changes.

TDataRow class provides a default Edit Dialog which is very simple in this version

oRec := TDataRow():New( oRs )
oRec:Edit() // displays edit dialog with Undo, Cancel and Save buttons.

Appending New Records:

Create a phantom blank record by specifying .t. as 3rd parameter.
oRec := TDataRow():New( cAlias/oRs, nil, .t. ) // 3rd param .t. indicates new blank record
oRec:Edit()

When saved the TDataRow method appends the data to a new record.
We are not conerned how TDataRow appends ( for dbf dbappend() for oRs AddNew() )
For us, the process is transparent.

Customising edit dialogs:

1. Create our own procedure defining edit dialog and when oRec:Modified() is true
call oRec:Save()
2. Assign our edit dialog function to oRec:bEdit codeblock
3. Override oRec:Edit() method in a derived class.

Using TDataRow class for adhoc data.

aData := { ;
{ "Name", Space( 30 ) }, ;
{ "Age", 0 }, ;
{ "City", Space( 20 ) } }

oData := TDataRow():New( aData )
oData:Edit()

and we have the modified data in aData now.

[elvira]

Dear Friends,

Thanks a lot for your help.

And which is the best way to do a Locate as this?:

Code: Select all  Expand view

SELECT ("CUSTOMER")
DbGoTop()

LOCATE FOR FIELD->NAME == "Elvira"
IF !FOUND()
   MsgNotFound()
ELSE
  EditCustomer( CUSTOMER->(Recno()) )
ENDIF



 

Thanks.

[Maurizio]

Hello Nages

I think that in this example there is an error

oRs := FW_OpenRecordSet( "c:\fwh\samples\xbrtest.mdb", "CUSTOMER" )
oRec := TDataRow():New( "CUST" ) -----> oRec := TDataRow():New( oRs)
? oRec:RecNo,oRec:Age, oRec:Salary
oRec:Salary += 2000
? oRec:Salary
? oRec:Modified()
oRec:Save()

is right ?

Maurizio

[nageswaragunupudi]

Mr Maurizio

oRec := TDataRow():New( "CUST" ) -----> oRec := TDataRow():New( oRs)

You are right.

Also by an oversight there is a bug in datarow.prg. This affects appending new record in ADO.
Please make this correction in datarow.prg and use.
Line 355:
Instead of

Code: Select all  Expand view

::AddNew()

Substitute

Code: Select all  Expand view

oRs:AddNew()

[elvira]

Thank you.

And what are the equivalents for found() and Alias->(Recno()), please?.

[Armando]

Elvira:

Take a glance at the following link

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

Regards

[elvira]

Armando,

Thanks, but there is nothing on that page about:
find()

recno() equivalents.

Please, any help.

[Enrico Maria Giordano]

Elvira,

Thank you.

And what are the equivalents for found() and Alias->(Recno()), please?.

Found(): after a query you can use !oRs:EOF.
Recno(): oRs:AbsolutePosition()

EMG

[nageswaragunupudi]

Code: Select all  Expand view

Found()                --> .not. oRs:Eof()
RecNo()                --> oRs:BookMark
DbGoTo( n )          --> oRs:BookMark := n
OrdKeyNo()           --> oRs:AbsolutePosition
OrdKeyGoTo( x )    --> oRs:AbsolutePosition := x
 

[Armando]

Elvira:

Yes it is, Please look at the methods of the recordset

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

Any way here is an example

Code: Select all  Expand view

        oRsHdr:Find("HDR_FAC = " + "'" + STR(nIrA,7,0) + "'" )
        IF oRsHdr:EOF
            MsgInfo("Invoice not found !","Error")
        ENDIF
 

Regards

[James Bott]

It would seem that it would make sense to have a different class for each data source. Each class should follow polymorphism so each is syntax compatible. Then you just link-in the class you need.

Making separate classes makes each one simpler, with less code, and easier to read and debug. Also they follow the concepts of OOP. The app EXE would be smaller too.

I would also suggest that the class name TRecord would more descriptive than TDataRow. A record is more generic than a row--it could be used as a row in a browse, but it could also be used in a dialog, or just passed to another class or function.

My 2 cents.

James

[nageswaragunupudi]

Mr James

I understand from your several postings in the forum that your library has two classes TData and TRecord.

I chose a different name TDataRow to avoid duplicate symbol name problem for users of your TData library.

The approach FW adopted for xbrowse and datarow is that programmers should be able to write portable code. That is the same code without changes should work for any datasource.

Please see the example I posted here
viewtopic.php?f=3&t=26537