Refresh SQL Select in Listbox

[cdmmaui]

Hello,

Can someone tell me how to refresh the data in a listbox after the listbox has already been displayed. I have a background process updating a table and I am calling a function that uses the SELECT statement to pull the data, I get the correct recordcount however I cannot get the listbox to refresh with the updated data.

Here is my listbox code and function being to refresh data:

// Listbox...
@110,000 LISTBOX oLbx FIELDS ;
oSql:Fields( "MRNO" ):Value , ;
oSql:Fields( "CUSTID" ):Value , ;
UPPER( oSql:Fields( "PO" ):Value ) , ;
oSql:Fields( "RECV" ):Value , ;
oSql:Fields( "PKG_RCV" ):Value , ;
oSql:Fields( "CHECK_BY" ):Value , ;
oSql:Fields( "MRSERIAL" ):Value , ;
IF( oSql:Fields( "RFIDCOMPLETE" ):Value=1, "Yes", "No") , ;
oSql:Fields( "RFIDCOMMISSIONED" ):Value ;
HEADERS "M/R No.", "Cust ID", "P.O. No.", "Received", "Qty", "Received By", "Internal", "M/R Complete", "RFID Commissioned" ;
SIZES 100 , 100 , 100 , 100 , 85 , 100 , 125 , 100 , 200 ;
FONT oFont2 ;
OF oWnd SIZE nLbxW, nLbxH PIXEL
oLbx:bLogicLen = { || oSql:RecordCount }
oLbx:bGoTop = { || oSql:MoveFirst() }
oLbx:bGoBottom = { || oSql:MoveLast() }
oLbx:bSkip = { | nSkip | Skipper( oSql, nSkip ) }
oLbx:cAlias = "ARRAY1"

// Refresh routine...
//----------------------------------------------------------------------------//
static function SeekRfid( cSearch, cSearchK, oLbx )

local cSeek := ALLTRIM( cSearchK ) , ;
lSearch := .F. , ;
lFnd := .F.

IF lActive
RETURN (.T.)
ENDIF
lActive := .T.

TRY
oSql:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait("It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
RETURN (.F.)
END

// Set...
oSql:CursorType := 1 // opendkeyset
oSql:CursorLocation := 3 // local cache
oSql:LockType := 3 // lock opportunistic

// Set SQL string...
cSql := "SELECT * FROM mr"

TRY
oSql:Open( cSql, 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xDATABASE+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oError
MsgWait("Failed to Connect to the Database")
RETURN .F.
END
MsgWait( LTRIM(STR(oSql:RecordCount,9)) )

oLbx:Disable()
Sysrefresh()
oLbx:Enable()
Sysrefresh()

//oLbx:MoveFirst()
oLbx:Refresh()
Sysrefresh()

lActive := .F.

RETURN (.T.)

[Rick Lipkin]

Darrell

If all you are doing is refreshing the data looking for potential changes and updates .. just issue oRs:ReQuery(), oRs:MoveFirst() and oLbx:ReFresh().

I think you will find xBrowse much easier to use than Listbox .. no need for a skipper function. You can easily set footers with your record Count .. If you need some sample code I will be glad to share.

Rick Lipkin

[cdmmaui]

Hi Rick,

Thank you so much. I would like to mover to XBROWSE, could you send me a sample. You can e-mail if you'd like at Darrell.Ortiz@cdmsoft.com.

I owe you lunch or dinner if you are in Houston or Chicago.

Thank you,

[Rick Lipkin]

Darrell

Here is some untested code .. I may have a punctuation compile error without testing ..

1) What I do is set the connection string to xConnect as Public at the top of my starting program.. much easier to pass that to your Open() recordset method than the entire string. In my example I have it set to connect to Ms Access .. but you will see the MS Sql server attributes there as well

2) The xBrowse example has has LOTS of code blocks that you can use to customize your browse .. I have a few of them rem'd out. I also included a record counter and the ability to add a column on the fly ( column 8 ) to allow you to substitute a Yes or No based on the field.

Notice also this example used a .rc form and Redefine .. if you do not use a form adjust your code accordingly. Hopefully you will be able to modify the code to fit your situation and fix any untested syntactical errors I may have. Please note there are other ways of coding this to achieve the same result ..

If you need any more examples or explanations .. let me know.

Rick Lipkin

Code: Select all  Expand view  RUN

// set this in your main program

Func Main()

Local cFile,nStart,cDefa,oBtn1,oBtn2
Public xDatabase,xCatalog,xProvider,xSource,xPassword,xConnect,xUserId

cFILE := GetModuleFileName( GetInstance() )

// where .exe started from is default directory //
nSTART := RAT( "\", cFILE )
cDEFA  := SUBSTR(cFILE,1,nSTART-1)

SET DEFA to ( cDEFA )

// use A for MS Access, S for Sql Server
// this setup is for Ms Access

xDATABASE := "A"      // access
// xDATABASE := "S"   // sql server
// xCATALOG  := "SERVICE"
// xUSERID   := "serviceuser"

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Service.mdb"
xPASSWORD := "dec2011"

IF xDATABASE = "A"
   xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
ELSE
   xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
ENDIF

//-- end main snipit



// xbrowse example

#Include "FiveWin.ch"
#Include "xBrowse.ch"

//-------------------------
Func xBtest()

Local oSql,cSql,oErr,oDlg,oLbx,oCol

oSql:=TOleAuto():New("ADODB.Recordset")
oSql:CursorType     := 1     // opendkeyset
oSql:CursorLocation := 3     // local cache
oSql:LockType       := 3     // lock opportunistic

// Set SQL string...
cSql := "SELECT * FROM mr"

TRY
   oSql:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening MR table" )
   RETURN(.F.)
END TRY


// may want to test for oSql:eof here ??


DEFINE DIALOG oDlg RESOURCE "YOURFORM"

     
       REDEFINE xBROWSE oLBX            ;
       RECORDSET oSql                   ;
       COLUMNS "MRNO",                  ;
               "CUSTID",                ;
               "PO",                    ;
               "RECV",                  ;
               "PKG_RCV",               ;
               "CHECK_BY",              ;
               "MRSERIAL",              ;
               "RFIDCOMMISSIONED"       ;
       COLSIZES 100 , 100 , 100 , 100 , 85 , 100 , 125 , 200 ;
       HEADERS "M/R No.",   ;
               "Cust ID",         ;
               "P.O. No.",        ;
               "Received",       ;
               "Qty",              ;
               "Received By",   ;
               "Internal",         ;
               "RFID Commissioned" ;
       ID 111 of oDlg              ;
       AUTOSORT AUTOCOLS LINES CELL

       // insert Yes or No for column 8

       ADD oCol to oLbx AT 8 DATA {|x| x :=  If(oSql:Fields("RfidComplete"):Value = 1, "Yes","No")};
            HEADER 'M/R Complete' size 100


       // custom attributes


       oLbx:lFooter   := .t.
       oCol           := oLbx:aCols[ 1 ]
       oCol:bFooter   := { || Ltrim( Str( oLbx:KeyNo() ) ) + " / " + LTrim( Str( oLbx:KeyCount() ) ) }
       oLbx:bChange   := { || oCol:RefreshFooter() }


     *  oLbx:lHScroll := .f. // turn off horiz scroll bar

     *  oLbx:bClrRowFocus    := { || { CLR_BLACK, RGB(185,220,255) } }
     *  oLbx:nMarqueeStyle   := MARQSTYLE_HIGHLROWMS

     * oLbx:bLDblClick := { |nRow,nCol | _BinView( "V",oRsBin,"", "", "", "",;
                                         oBtn1,oBtn2,oBtn3,oBtn4,oBtn5),oLbx:SetFocus() }
     * oLbx:bKeyDown   := { |nKey| _Manual( nKey,oRsBin,oBtn1,oBtn2,oBtn3,oBtn4,oBtn5,oBtn6,oLbx ) }


   REDEFINE BTNBMP oBTN1 ID 113           ;
       PROMPT "ReFresh Data" LEFT 2007;
       ACTION( oSql:ReQuery(),oSql:MoveFirst(),oLbx:ReFresh(),oLbx:SetFocus())

   REDEFINE BTNBMP oBTN2 ID 114           ;
       PROMPT "Quit" LEFT 2007;
       ACTION( oDlg:End())
   
   
ACTIVATE DIALOG oDlg

oSql:CLose()

// end

[cdmmaui]

Thanks Rick!

[Rick Lipkin]

Let me know if you need anything else

Rick