Another for MDB recorset

[Silvio]

Dear nageswaragunupudi
I made a s you sad but it make erro on Sort command
I try with :
ors:sort:="First"
or
ors:sort:field("first"):value
or
ors:sort:field("first")::name

I must create a small sample to see it ...

[nageswaragunupudi]

Mr James

Yes thats is what I suggest.

You and me being OOPS fans, we better have a Class for Connection. Make a base class for Ado Connection and then we have derived classes for oracle, mssql, etc. with their specific features,

In fact thats what I do. Like you I have one public function which returns the connection object. With my oops model, I finally program like this

oTable := MyServer():OpenTable( <csql>, ... params )

My oTable class is like your TData class. It is wrapper for recordset. Bridges behavioral gaps of dbf and recset.

Now I am sure you know what I mean and what I am doing, without futher elaboration.

[nageswaragunupudi]

Mr Silvio

Please make a very short program.
Open recordset.
Then write this line oRs:Sort := <first column name>
Post the code here.

[Silvio]

try it

Code: Select all  Expand view  RUN

#include "FiveWin.ch"
#include "Splitter.ch"


STATIC oDb, oRs


FUNCTION Customer()

LOCAL aBrowse
LOCAL nSplit :=102
LOCAL nOrder :=1
LOCAL nRecno :=1
LOCAL oDlg,oFont,oTab

    oRs = CREATEOBJECT( "ADODB.Recordset" )
    TRY
    oRs:Open( "SELECT * FROM Utenti ORDER BY COGNOME", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ecom.mdb", 1, 3 )
    CATCH oErr
        ? oErr:Description
        RETURN NIL
    END TRY

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12
   DEFINE DIALOG oDlg from 2,4 to 24,80;
      TITLE "test xbrowse with tabs with MDB" ;
      FONT oFont

   oBrw                    := TXBrowse():New( oDlg )
   WITH OBJECT oBrw
      :nTop                :=  10
      :nLeft               := 20
      :nBottom             := 150
      :nRight              := 300
   END



aBrowse   := { { { || STR( oRs:Fields( "ID" ):Value) }, i18n("Codice"), 50, 0 },;
                  { { || oRs:Fields( "Nome" ):Value }, i18n("Nome"), 150, 0 },;
                  { { || oRs:Fields( "Cognome" ):Value}, i18n("Cognome"), 150, 0 },;
                  { { || oRs:Fields( "Via" ):Value} , i18n("Indirizzo"), 150, 0 } ,;
                  { { || oRs:Fields( "Civico" ):Value} , i18n("Civico"), 150, 0 },;
                  { { || oRs:Fields( "Citta" ):Value} , i18n("Città"), 150, 0 }  ,;
                  { { || oRs:Fields( "Provincia" ):Value} , i18n("Prov"), 150, 0 }}





FOR i := 1 TO Len(aBrowse)
      oCol := oBrw:AddCol()
      oCol:bStrData := aBrowse[ i, 1 ]
      oCol:cHeader  := aBrowse[ i, 2 ]
      oCol:nWidth   := aBrowse[ i, 3 ]
      oCol:nDataStrAlign := aBrowse[ i, 4 ]
      oCol:nHeadStrAlign := aBrowse[ i, 4 ]
   NEXT

   oBrw:SetADO( oRs )
   oBrw:CreateFromCode()



   oBrw:nRowHeight  := 21
   oBrw:nHeaderHeight  := 36



@ oBrw:nBottom+1,20 TABS oTab ;
     OPTION nOrder SIZE oDlg:nWidth()-50, 12 PIXEL OF oDlg ;
     ITEMS ' First ', ' Last ' ;
     ACTION ( nOrder := oTab:nOption  ,;
              if( norder=1, oRs:Sort := 'ID' ,oRs:Sort := 'COGNOME' ),;
              oRs:MoveFirst()         ,;
              oBrw:Refresh(.t.)     )







ACTIVATE DIALOG oDlg
RETURN NIL



[nageswaragunupudi]

Please insert this line
oRs:CursorLocation := 3
just after creating the recordset object

Code: Select all  Expand view  RUN

    oRs = CREATEOBJECT( "ADODB.Recordset" )

    oRs:CursorLocation := 3    // insert this line here

    TRY



Now please try

[Silvio]

thanks now run ok

[Silvio]

THE PROBLEM IS ANOTHER :

NOW i MUST SORT ON TWO FIELD :

if( norder=1, oRs:Sort := 'ID' ,oRs:Sort := 'COGNOME' ),;


BUT IF i MUST SORT ON MANY FIELDS HOW i CAN MAKE ?

i NEED CREATE SOMETHING OF

@ oBrw:nBottom+1,20 TABS oTab ;
OPTION nOrder SIZE oDlg:nWidth()-50, 12 PIXEL OF oDlg ;
ITEMS ' CODICE ', ' COGNOME ' ;
ACTION ( nOrder := oTab:nOption ,oRs:sort:=nOrder;
oRs:MoveFirst() ,;
oBrw:Refresh(.t.) )

[nageswaragunupudi]

oRs:Sort := "ID,COGNOME"

[Silvio]

ok but I have a variable norder

and this variable I must use with Tab noption

the sort read only the field name oRs:sort:="Id,cognome,nome"

but there is not a command to sort on number of field ?

sample

oRs:sort=norder


I cannot use the fieldname because i must rewrite each program

[nageswaragunupudi]

>
but there is not a command to sort on number of field ?
>

NO

>
oRs:sort=norder
>

Instead, you create an array of sort orders. example:
aOrders := { "ID", "ONE,TWO", "ID,NAME DESC", .... }

then depending on the option, say oRs:Sort := aOrders[oTabs:nOption]

[Silvio]

ok this is a good idea i try to improved it

[Silvio]

ok this run ok

Code: Select all  Expand view  RUN

#include "FiveWin.ch"
#include "Splitter.ch"


STATIC oDb, oRs


FUNCTION Customer()

LOCAL aBrowse
LOCAL nSplit :=102
LOCAL nOrder :=1
LOCAL nRecno :=1
LOCAL oDlg,oFont,oTab

Local aOrders := { "ID", "NOME", "COGNOME", "CITTA" }

    oRs = CREATEOBJECT( "ADODB.Recordset" )
    oRs:CursorLocation := 3    // insert this line here
    TRY
    oRs:Open( "SELECT * FROM Utenti ORDER BY COGNOME", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ecom.mdb", 1, 3 )
    CATCH oErr
        ? oErr:Description
        RETURN NIL
    END TRY

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12
   DEFINE DIALOG oDlg from 2,4 to 24,80;
      TITLE "test xbrowse with tabs with MDB" ;
      FONT oFont

   oBrw                    := TXBrowse():New( oDlg )
   WITH OBJECT oBrw
      :nTop                :=  10
      :nLeft               := 20
      :nBottom             := 150
      :nRight              := 300
   END



aBrowse   := { { { || STR( oRs:Fields( "ID" ):Value) }, i18n("Codice"), 50, 0 },;
                  { { || oRs:Fields( "Nome" ):Value }, i18n("Nome"), 150, 0 },;
                  { { || oRs:Fields( "Cognome" ):Value}, i18n("Cognome"), 150, 0 },;
                  { { || oRs:Fields( "Via" ):Value} , i18n("Indirizzo"), 150, 0 } ,;
                  { { || oRs:Fields( "Civico" ):Value} , i18n("Civico"), 150, 0 },;
                  { { || oRs:Fields( "Citta" ):Value} , i18n("Città"), 150, 0 }  ,;
                  { { || oRs:Fields( "Provincia" ):Value} , i18n("Prov"), 150, 0 }}





FOR i := 1 TO Len(aBrowse)
      oCol := oBrw:AddCol()
      oCol:bStrData := aBrowse[ i, 1 ]
      oCol:cHeader  := aBrowse[ i, 2 ]
      oCol:nWidth   := aBrowse[ i, 3 ]
      oCol:nDataStrAlign := aBrowse[ i, 4 ]
      oCol:nHeadStrAlign := aBrowse[ i, 4 ]
   NEXT

   oBrw:SetADO( oRs )
   oBrw:CreateFromCode()



   oBrw:nRowHeight  := 21
   oBrw:nHeaderHeight  := 36



@ oBrw:nBottom+1,20 TABS oTab ;
     OPTION nOrder SIZE oDlg:nWidth()-50, 12 PIXEL OF oDlg ;
     ITEMS ' CODICE ', 'NOME' ,'COGNOME';
     ACTION ( nOrder := oTab:nOption  ,;
              oRs:Sort := aOrders[oTab:nOption],;
              oRs:MoveFirst()         ,;
              oBrw:Refresh(.t.)     )







ACTIVATE DIALOG oDlg
RETURN NIL