Someone has an example of the use of a recorset with xbrowse

[leandro]

Friends of the forum have good day. As says in headline, I need to know if someone has an example of the use of a recorset in a xbrowse.

In advance thank you.

Pardon for the Englishman is a translation.

Regards

[nageswaragunupudi]

Mr Leandro

It is very simple to use XBrowse with RecordSets.
Please try this quick code.

Code: Select all

DEFINE WINDOW oWnd
oBrw := TXBrowse():New( oWnd )
oBrw:SetADO( oRs )                         // oRs is your record set
oBrw:CreateFromCode()
oWnd:oClient := oBrw
ACTIVATE WINOW oWnd

You can use other features of xBrowse for advanced purposes.

[Armando]

nageswaragunupudi

Many thanks for your sample, and I have a couple questions.
What if the recordset is empty ?, Is the Txbrowse showing with no problem ?

Regards

[nageswaragunupudi]

If we want to browse empty record sets also, we need to definte columns ourselves similar to the following code.

Code: Select all

oCol := oBrw:AddCol()
oCol:bStrData := { || If( oRs:RecordCount() > 0, oRs:Field(0):Value, space(10) ) }
oCol:cHeader := "Col-1" 

// after defining all the columns
oBrw:SetADO( oRs )
 // rest of the code

In any case, recordsets' features differ from the familiar dbf behaviour. There are other issues like handling Null values, variable length fields, etc. which always need extra coding on our part.

If we go for extensive use of ADO, it is worthwhile considering making a Wrapper class for RecordSet. By experience I consider this as not only extremely useful but essential in the long run.

[Rick Lipkin]

For what it is worth .. I always check for oRs:eof and if there are no rows from your query .. ask to add or just bug out.

If you have an embedded listbox in your form .. you still should have to test for eof and wrap your 'skipper' to keep from encountering a run-time error.

Rick Lipkin

[Armando]

Mr. nageswaragunupudi (It's a long name )

Thanks to you for sample, I'll try.

Mr. Rick.

Thanks so much for your explanation.

With best regards

[leandro]

Mr. nageswaragunupudi, friend like these, which he(she) punishes to be a nuisance so much, but I have the problem that when there are no records in the table goes out for me a mistake to the moment to create the xbrowse, try as you answered him(her) to arming(assembling) but I me do not work. Of chance you do not have an example a bit clear mas since(as,like) the void recorset use.

[nageswaragunupudi]

Mr leandro

If the RecSet has atleast one Row at the beginning, later even if all the rows are deleted also, the browse works well. But if we start with an empty RecordSet ( like an empty array ), the browse fails while trying to compute the datawidths.

Try writing code in the following manner. It worked for me here, with empty recordset.

Code: Select all

   DEFINE DIALOG oDlg SIZE 640,340 PIXEL

   @ 10,10 XBROWSE oBrw ;
      FIELDS ;
         If( oRs:Eof, Space(20), oRs:Fields("First"):Value ), ;
         If( oRs:Eof, Space(30), oRs:Fields("Second"):Value ) ;
      HEADERS ;
         "First", "Second" ;
      OF oDlg SIZE 300,150 PIXEL

   oBrw:SetADO( oRs )
   oBrw:CreateFromCode()

   ACTIVATE DIALOG oDlg CENTERED

Personally I use a wrapper class for recordset, which returns a equivalent blank value on eof() condition.

I guess we can improve the adogenblock to take care of eof situations. Also we can modify TXColumn's data width method not to compute datawidth if width is already specified.

We can make an improvement to the XBrowse class

[nageswaragunupudi]

Here is a more generic code :

Code: Select all

   DEFINE DIALOG oDlg SIZE 640,340 PIXEL

   @ 10,10 XBROWSE oBrw ;
      OF oDlg SIZE 300,150 PIXEL

   for n := 0 to oRs:Fields:Count() - 1
      xbrAdoSetField( oBrw, oRs, n )
   next

   oBrw:SetADO( oRs )
   oBrw:CreateFromCode()

   ACTIVATE DIALOG oDlg CENTERED
   return nil
//-------------------------------------
function xbrAdoSetField( oBrw, oRs, cnFld )

   local oCol     := oBrw:AddCol()
   local nwidth   := oRs:Fields( cnFld ):DefinedSize

   WITH OBJECT oCol
      :cHeader    := oRs:Fields(cnFld):Name
      :bStrData   := { || If( oRs:Eof, Space( nWidth ), cValtoChar( oRs:Fields( cnFld ):Value ) ) }
   END

return oCol

This code is tested and will work

[leandro]

Friend nageswaragunupudi:

First that quite I want to be grateful to you for the help given till now has served me very much. But I have another couple of pregunticas mas to know if it(he,she) can help me.
1-> Since I do to refresh the recorset in time of ejecucción? I could not have achieved it.
2-> When I want to use the xbrowse on windows MDI, it saves a mistake that he says more or less this way:

E_FAIL: BOOKMARK

Not if one has to see with the form that I define the cursor. I am doing it of the following way:

Code: Select all

oRsPro:CursorLocation := adUseClient
oRsPro:LockType := adLockOptimistic
oRsPro:CursorType := adOpenDynamic
oRsPro:Source := "SELECT * FROM proyecto ORDER BY PRO_PRO"
oRsPro:ActiveConnection(oCnx)

Since I can solve this problem?

Thank you for the whole given help.

[nageswaragunupudi]

You shd open your recordset on clientside
Please reproduce your code for opening the recordset. I shall let you know what to do

[leandro]

Friend, gun-sight I am doing it of the following way:

Code: Select all

********************
*Catalogo de Medidas
********************
PROC pMedida()

LOCAL aCol:={0,0,0}
STATIC aArrayVars:= {"m_medida"}
#xtranslate vOrden => aArrayVars\[1\]
oLamcla:oC1vg1:=space(100)

oVar   := "SELECT * from "+oLamcla:tMedi+" ORDER BY "+alltrim(vOrden)
TRY
  oLamcla:oRsMedida := CreateObject("ADODB.Recordset")
CATCH oError
  MsgStop( "No se ha podido crear el RECORDSET !","Error de Datos" )
END

oLamcla:oRsMedida:CursorLocation  := adUseClient //adUseServer
oLamcla:oRsMedida:LockType        := adLockOptimistic
oLamcla:oRsMedida:CursorType      := adOpenKeyset
oLamcla:oRsMedida:Source          := oVar
oLamcla:oRsMedida:ActiveConnection( oLamcla:oServer )
TRY
  oLamcla:oRsMedida:Open()
CATCH oError
  MsgStop( "No se ha podido ejecutar el comando !", "Error de Datos" )
END

DEFINE BRUSH oFondo FILE ".\res\res_403.BMP"
DEFINE DIALOG oCuadro RESOURCE "orMedida" ICON "#8001" TRANSPARENT BRUSH oFondo

  REDEFINE BUTTONBMP Btn_Limp ID 4003 OF oCuadro BITMAP "Blimpi2" TOOLTIP oLamcla:mTfil //ACTION pValiMed()
  //REDEFINE BTNBMP Btn_Limp ID 4010 OF oCuadro RESOURCE "BLimpi2" TOOLTIP oLamcla:mTfil NOBORDER UPDATE //ACTION (oC1vg1:=space(100),pArMedi())
  REDEFINE GET oC1og1 VAR oLamcla:oC1vg1 ID 4002 OF oCuadro UPDATE //VALID(pBusMed(),.T.)

  oBrw := TXBrowse():New( oCuadro )
  oBrw:nMarqueeStyle       := MARQSTYLE_HIGHLROW
  oBrw:nColDividerStyle    := LINESTYLE_RAISED
  oBrw:nHeaderLines        := 1
  oBrw:lColDividerComplete := .t.
  oBrw:lRecordSelector     := .t.
  oBrw:bClrSel := {|| { CLR_BLACK,oLamcla:cClr7 } }
  oBrw:bClrStd := {|| IF( (oLamcla:oRsMedida:AbsolutePosition()%2)==0,{CLR_BLACK,oLamcla:cClr2},{CLR_BLACK,oLamcla:cClr1} ) }
  oBrw:bClrSelFocus        := {|| { CLR_WHITE,RGB(147,160,112) } }

  aCol[ 1]                   := oBrw:AddCol()
  aCol[ 1]:bStrData          := { || If( oLamcla:oRsMedida:Eof, Space( nWidth ), cValtoChar( oLamcla:oRsMedida:Fields( "m_medida" ):Value ) ) }
  aCol[ 1]:cHeader            := "Codigo"
  aCol[ 1]:nHeadStrAlign     := AL_LEFT
  aCol[ 1]:nDataStrAlign     := AL_LEFT
  aCol[ 1]:nWidth            := 80

  aCol[ 2]                   := oBrw:AddCol()
  aCol[ 2]:bStrData          := { || If( oLamcla:oRsMedida:Eof, Space( nWidth ), cValtoChar( oLamcla:oRsMedida:Fields( "m_nombre" ):Value ) ) }
  aCol[ 2]:cHeader            := "Nombre"
  aCol[ 2]:nHeadStrAlign     := AL_LEFT
  aCol[ 2]:nDataStrAlign     := AL_LEFT
  aCol[ 2]:nWidth            := 225

  aCol[ 3]                   := oBrw:AddCol()
  aCol[ 3]:bStrData          := { || If( oLamcla:oRsMedida:Eof, Space( nWidth ), cValtoChar( oLamcla:oRsMedida:Fields( "m_siglas" ):Value ) ) }
  aCol[ 3]:cHeader            := "Siglas"
  aCol[ 3]:nHeadStrAlign     := AL_LEFT
  aCol[ 3]:nDataStrAlign     := AL_LEFT
  aCol[ 3]:nWidth            := 225

  oBrw:SetADO( oLamcla:oRsMedida )
  oBrw:CreateFromResource( 102 )

  oCuadro:cTitle := "Tabla de Medidas"

ACTIVATE DIALOG oCuadro On Init (Centra( oCuadro ),oCuadro:refresh(),BarraMed( oCuadro )) CENTERED
oLamcla:oRsMedida:Close()

Abusing your confidence I am going to do another question to you. Since I do for actualiar the recorset and the xbrose in time of ejecuccion?

Thanks

[nageswaragunupudi]

I guess you are getting the error only when the RecordSet is empty.
Please set the navigation codeblocks yourself in your code, before calling SetAdo

Code: Select all

oBrw:bGoTop    := { || If( oRs:RecordCount() > 0, oRs:MoveFirst(), ) }
oBrw:bGoBottom := { || If( oRs:RecordCount() > 0, oRs:MoveLast(), )  }
oBrw:bSkip     := { | n | If( oRs:RecordCount == 0, 0, AdoSkip( oRs, If( n == nil, 1, n ) ) ) }
oBrw:bBof      := { || oRs:Bof }
oBrw:bEof      := { || oRs:Eof }
oBrw:bBookMark := { | uBm | If( If( oRs:RecordCount() == 0, 0, If( uBm == nil, oRs:BookMark, oRs:BookMark := uBm ) ) }
oBrw:bKeyNo    := { | n | If( oRs:RecordCount == 0, 0, If( n == nil, oRs:AbsolutePosition, oRs:AbsolutePosition := n ) ) }
oBrw:bKeyCount := { || oRs:RecordCount()}

Let us see if this is working well.
If with this modification it is working well even on empty recordsets, we can then request Mr Antonio to include the changes in the xBrowse.Prg

[Carlos Mora]

Hi Nagesh,

Here is a more generic code :

Code: Select all

//-------------------------------------
function xbrAdoSetField( oBrw, oRs, cnFld )

   local oCol     := oBrw:AddCol()
   local nwidth   := oRs:Fields( cnFld ):DefinedSize

   WITH OBJECT oCol
      :cHeader    := oRs:Fields(cnFld):Name
      :bStrData   := { || If( oRs:Eof, Space( nWidth ), cValtoChar( oRs:Fields( cnFld ):Value ) ) }
   END

return oCol

This code is tested and will work

This code can be polished a little bit. Using DefinedSize property is not correct in all cases, mainly in those where the type is a numeric one. As an example, for fields with Type = 3 (numeric LONG) you get a DefinedSize of 4, what is wrong because 4 is the data len, in this case a LONG INT of 4 bytes. A more exact value can be get using the Precision property, that in the example evaluates to 10, the width needed to represent the max value ( +/- 2147483648 ) this field can adopt.
Another example is field type 5, numeric DOUBLE, with a defined size of 8 but a precision of 15.

My 2 cents.

Regards,

Carlos.

[nageswaragunupudi]

Mr Carlos

I totally agree with you. And my personal code is more elaborate. We need to examine the Type first and then decide whether to use DefinedSize or Precision and Scale.

This code would be much longer and I gave the above code which is simpler for demonstrative purpose.

That is the reason, I personal use a wrapper class with methods lika TDataBase which takes care of all these issues. I advise this approach for any serious user