Modifying the query of a recordset and xBrowse

[Rick Lipkin]

To All

I have defined an xBrowse with an empty recordset ( oRsInv .. where Repair number = 0 ) on a sql query due to the huge number of records in the table.

Code: Select all | Expand

REDEFINE xBROWSE oLbxB1           ;
         RECORDSET oRsInv            ;
         COLUMNS "REPAIR NUMBER",    ;
                 "TRANSACTION TYPE", ;
                 "INVOICED DATE",    ;
                 "TOTAL",            ;
                 "BALANCE"           ;
         COLSIZES 70,80,80,70,70     ;
         HEADERS "Invoice #",        ;
                 "Type",             ;
                 "Date",             ;
                 "Total",            ;
                 "Balance"           ;
         ID 139 of oCust2            ;
         AUTOCOLS LINES CELL
 

Is there a way to keep the record set object and browse active ( without killing the listbox ) if I want to go back in and close() the current recordset ( oRsInv:CLose() ) and re-issue the same recordset object ( same columns, new query ) with a different Repair Number = 12345 and possibly just refresh the xBrowse Object ( oLbxB1:ReFresh() ) with the new ( same object ) recordset ?

Thanks
Rick Lipkin

[Rick Lipkin]

To All

FYI .. I have answered my own question .. basically I trapped a double click from my invoice listbox, then passed the recordset and listbox object of the xBrowse I am updating .. and to my surprise, this code worked.

Rick Lipkin

Code: Select all | Expand

// invoices

   REDEFINE xBROWSE oLbxB1           ;
         RECORDSET oRsInv            ;
         COLUMNS "REPAIR NUMBER",    ;
                 "TRANSACTION TYPE", ;
                 "INVOICED DATE",    ;
                 "TOTAL",            ;
                 "BALANCE"           ;
         COLSIZES 70,80,80,70,70     ;
         HEADERS "Invoice #",        ;
                 "Type",             ;
                 "Date",             ;
                 "Total",            ;
                 "Balance"           ;
         ID 139 of oCust2            ;
         AUTOCOLS LINES CELL

   oLbxB1:nMarqueeStyle := MARQSTYLE_HIGHLROW
   
   // trap double click on Invoice line
   oLbxB1:bLDblClick := { |nRow,nCol | _PayUpDt( oRsInv, oRsPay, oLbxB2) }

  // payments
   REDEFINE xBROWSE oLbxB2           ;
         RECORDSET oRsPay            ;
         COLUMNS "INVOICE NUMBER",   ;
                 "DATE COLLECTED",   ;
                 "PAYMENT METHOD",   ;
                 "PAYMENT AMOUNT"    ;
         COLSIZES 80,80,80,80        ;
         HEADERS "Invoice",          ;
                 "Date",             ;
                 "Pymt Meth",        ;
                 "Amount"            ;
         ID 140 of oCust2            ;
         AUTOCOLS LINES CELL

   oLbxB2:nMarqueeStyle := MARQSTYLE_HIGHLROW
...
...
...
//-----------------------
Static Func _PayUpDt( oRsInv, oRsPay, oLbxB2)

Local cSql,oErr,nRepairNumber

nRepairNumber := oRsInv:Fields("Repair Number"):Value

oRsPay:Close()

*oLbxB2:bKeyCount := { || 0 } // kills off the listbox 
*oLbxB2:bKeyNo    := { || 0 }  // do not use

// payments

cSql := "SELECT "
cSql += "p.[Payment Number] AS pn, "
cSql += "d.Counter, "
cSql += "d.[Payment Method], "
cSql += "d.[Payment Amount], "
cSql += "d.Reference, "
cSql += "d.[Customer ID], "
cSql += "d.[Date Collected], "
cSql += "d.[Card Number], "
cSql += "d.[Card Name], "
cSql += "d.[Check Number], "
cSql += "d.Deposit, "
cSql += "d.Depositted, "
cSql += "d.User, "
cSql += "d.Computer, "
cSql += "d.Void, "
cSql += "p.[Invoice Number], "
cSql += "p.Amount "
cSql += "FROM [Payment Details] AS p "
cSql += "LEFT JOIN Payments AS d ON p.[Payment Number] = d.Counter "
cSql += "Where p.[Invoice Number] = "+ltrim(str(nRepairNumber))

// do not have to re-create the recordset .. just re-use

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

TRY
   oRsPay:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening PAYMENTS table" )
   RETURN(.t.)
END TRY

oLbxB2:ReFresh()

Return(nil)



 

[nageswaragunupudi]

XBrowse safely works
(1) ReQuery the same recordset
OR
(2) Create new Recset ( ofcourse with exactly the same fields ) say oNewRs
and then :-
oOldRs := oBrw:oRs
oBrw:oRs := oNewRs
oOldRs:Close()
oBrw:GoTop()
oBrw:Refresh()

[Rick Lipkin]

Rao

Do you see any potential problems with the way I re-used the same recordset object ?? All I did was close the recordset oRs:Close() .. so the object still retained it's creation parameters, so instead of starting and re-creating the same object .. I just re-issued oRs:Open(...) command with the same fields, just different command parameters ?

Thanks
Rick Lipkin

[Armando]

Rick:

My five cents, that's the same way I do that, my app is working since 2007 and not problem at all.

Code: Select all | Expand

/*
* --------------------------------------------------------------------------*
* --------------------------------------------------------------------------*
*/
STATIC FUNCTION FilHdr(nAmo,oBrw,aGets)
oRsHdr:Close()

oRsHdr:Source :=    "SELECT " +;
                            "hdrndc.*," +;
                            "cliente.cli_nom " +;
                        "FROM " +;
                            "hdrndc," +;
                            "cliente " +;
                        "WHERE " +;
                            "hdrndc.hdr_cli = cliente.cli_cli " +;
                        "AND " +;
                            "YEAR(HDR_FDE) = '" + STR(nAmo,4,0) + "' " +;
                        "ORDER BY " +;
                            "hdr_ndc"

oRsHdr:Open()
oRsHdr:Refresh()
oBrw:Refresh()
aGets[1]:oJump  := oBrw
RETURN(.T.)
 

Regards

[Rick Lipkin]

Armando

Thanks .. I appreciate your response.

Rick

[Enrico Maria Giordano]

Rao

Do you see any potential problems with the way I re-used the same recordset object ?? All I did was close the recordset oRs:Close() .. so the object still retained it's creation parameters, so instead of starting and re-creating the same object .. I just re-issued oRs:Open(...) command with the same fields, just different command parameters ?

Thanks
Rick Lipkin

No, no problems in reusing oRs as many times as you need.

EMG

[Massimo Linossi]

Hi.
Very interesting topic.
I want to ask if there is a possibility to make the same with a normal listbox, or TsBrowse.
I have a lot of places in a big procedure and to change all of them with xbrowse would be really hard.
Thanks a lot.
Massimo.

[Enrico Maria Giordano]

Yes, you can reuse the same variable after the object has been destroyed. But I don't think you will have any real benefits.

EMG

[leandro]

Armando Muchas Gracias....

Funciono Perfecto