bb.donnay-software.com

Hi,

I want create small program for learn, how it works.

Now I have program, that open table, seek data for input parameters and then filtering it for show in browse.
First in browse window show data, but after scrolling in table with scroll on mouse I can go "up" and duplicate rows showing. Why ?
How can I prevent go up, down only for filtered data ?
And also I cannot go down ? If I have 2 results cursor stay only on first row and cannot go to second.
But can go up .
Maybe my program is all bad, I need some samples for learn, how can seek,filter, update data with using eXpress and SQL Express, if somebody have any, I will be glad him.

Here is my test program (modifyed SQL express sample for my database)
:

#include "sql.ch"
#include "sqlext.ch"
#include "dcdialog.ch"

SET PROCEDURE TO SQLXKEY.PRG

//-----------------------------------------------------------------------------
PROCEDURE DbeSys() ; Return
PROCEDURE AppSys() ; Return

PROCEDURE Main()

LOCAL oConnection, oCursor, GetList[0], GetOptions, oBrowse, i, cStatement, ;
cConnectString, cTableName, aSort
LOCAL nasiel
LOCAL oData,oDatax,nField1,nField2,nField3

PARAMETERS cisloku,cislolv,pserver,pdatabase,puid,ppwd // spustim so zadanim parametrov kat.územie a cislo lv a vypíse plomby

// set up default configuration of GUI browse headers for sorting
aSort := Array(4)
aSort[1] := GRA_CLR_WHITE // Sort Selected Color (Foreground)
aSort[2] := GRA_CLR_RED // Sort Selected Color (Background)
aSort[3] := GRA_CLR_WHITE // Sort Unselected Color (Foreground)
aSort[4] := GRA_CLR_DARKGRAY // Sort Unselected Color (Background)
DC_BrowseSort(aSort)

DC_AutoRestoreWindow({HKEY_LOCAL_MACHINE,'Software\Donnay Software\Samples\Windows'})

pdriver:="SQL Server" // driver odbc

cConnectString := "DRIVER="+pdriver+";SERVER="+pserver+";UID="+puid+";PWD="+ppwd+";DATABASE="+pdatabase

cTableName := 'LIS_PLOMBA,LIS_PLOMBA_HIS,LIS_KNIHY' // super toto konečne už odfiltruje platné plomby
cStatement := 'SELECT * FROM LIS_PLOMBA,LIS_PLOMBA_HIS,LIS_KNIHY WHERE (LIS_PLOMBA.PLO_ID = LIS_PLOMBA_HIS.PLO_ID) AND (LIS_PLOMBA.LIS_ID = LIS_KNIHY.LIS_ID) AND (LIS_PLOMBA.CLV='+cislolv+') AND (LIS_PLOMBA.KU_ID='+cisloku+') AND (LIS_KNIHY.ZNA=1 OR LIS_KNIHY.ZNA=2 OR LIS_KNIHY.ZNA=3) AND (LIS_KNIHY.STL=1 OR LIS_KNIHY.STL=4 OR LIS_KNIHY.STL=5) AND (LIS_PLOMBA.PLM=2 OR LIS_PLOMBA.PLM=3) AND (LIS_PLOMBA.PLS=1) AND (LIS_KNIHY.LTY=1)'

oConnection := SQLConnection():new()

oConnection:driverConnect(nil, @cConnectString)

* upozornenie, ak nieje možné vytvoriť spojenie s databázou
if ! oConnection:isConnected
DC_WinAlert("Pripojenie ku databaze neuspesne !!!")
Return
else
DC_WinAlert("Pripojenie ku databaze uspesne")
endif

* create statement string
cStatement := 'SELECT KU_ID,CLV,PCS,ZNA,CPS,ROL FROM LIS_PLOMBA,LIS_PLOMBA_HIS,LIS_KNIHY WHERE (LIS_PLOMBA.PLO_ID = LIS_PLOMBA_HIS.PLO_ID) AND (LIS_PLOMBA.LIS_ID = LIS_KNIHY.LIS_ID) AND (LIS_KNIHY.ZNA=1 OR LIS_KNIHY.ZNA=2 OR LIS_KNIHY.ZNA=3) AND (LIS_KNIHY.STL=1 OR LIS_KNIHY.STL=4 OR LIS_KNIHY.STL=5) AND (LIS_PLOMBA.PLM=2 OR LIS_PLOMBA.PLM=3) AND (LIS_PLOMBA.PLS=1) AND (LIS_KNIHY.LTY=1)'

create dataset for statement
oData := oConnection:DataSet(cStatement) // vytvorí dataset pre zadaný statement ešte bez filtrovania

* sorting data by 2 keys
oData:Sort( {"KU_ID","CLV"} ) // zotriedi podľa KU_ID a CLV a PCS


* cycle for input searching values for KU_ID and CLV
do while alltrim(cisloku)!="*"

* ďalej pokus z oCursor ak zadávam hodnoty
******************************************
@0,0 dcsay "Zadaj hodnoty, pre prerušenie zadaj *, kurzor sa generuje 10x pre test rychlosti"
@1,0 dcsay "Zadaj cislo ku" get cisloku
@2,0 dcsay "Zadaj cislo LV" get cislolv
dcread to lOk

if lOk .and. alltrim(cisloku)!="*"


* seek
nasiel:=oData:Seek( {val(cisloku),val(cislolv)} ) // malo by vyhľadať záznam

if nasiel
DC_WinAlert("Nasiel") // found
else
DC_WinAlert("Nenasiel") // not found
endif

if nasiel // zobrazí browser iba ak nájde plomby

* odfiltrovanie plomb pre zadané LV pre zobrazenie v browseri
nField1 := oData:FieldPos("KU_ID") // zistí por.číslo poľa KU_ID
nField2 := oData:FieldPos("CLV") // zistí por.číslo poľa CLV


* !!! HERE FILTER Dataset for show in browser

oData:BrowseFilter := {|aRow,nRow,oData| (val(cisloku) == (aRow[nField1])) .and. (val(cislolv) == (aRow[nField2]))}
oData:Gotop()

* zobrazenie tabuľky v browseri - nájdené plomby ale neodfiltrované
********************************
@ 0,0 DCBROWSE oBrowse DATA oData SIZE 70,20 ;
PRESENTATION DC_BrowPres()

* generovanie zoznamu polí // názvy ale budú podľa názvu polí databázy
FOR i := 1 TO oData:fCount
DCBROWSECOL DATA SQLFieldBlock( oData, i ) ;
HEADER oData:fieldName(i) ;
PARENT oBrowse
NEXT

* tu už samotné zobrazenie BROWSERom
DCGETOPTIONS ;
AUTORESIZE ;
BUTTONALIGN DCGUI_BUTTONALIGN_CENTER

DCREAD GUI CLEAREVENTS ;
FIT ;
OPTIONS GetOptions ;
BUTTONS DCGUI_BUTTON_EXIT ;
TITLE 'Zobrazenie nájdených plomb' ;
EVAL {||oBrowse:GoTop(),oBrowse:RefreshAll():ForceStable()}

else
DC_WinAlert("Plomba na zadané LV nenájdená")
endif


endif // lOk
enddo

oConnection:destroy()

Return

//-----------------------------------------------------------------------------

STATIC FUNCTION CreateSQLCursor( oConnection, nMode, cStatement )

Local oCursor, nSuccess := SQL_XPP_ERROR

oConnection:displayErrors := .t.

DEFAULT nMode := 1

if nMode == 1
oCursor := SQLSelect():new(cStatement, oConnection, SQL_CONCUR_READ_ONLY, SQL_CURSOR_DYNAMIC)
nSuccess := oCursor:execute()
elseif nMode == 2
// retrieve a max of 1000 rows and convert date-time values to Xbase++ dates (loose time portion)
oCursor := SQLDataSet():new(cStatement, oConnection,,,1000,,,.t.,.t.)
nSuccess := oCursor:execute() // don't really need to execute an SQLDataSet, it's just here for consistency
endif

oConnection:displayErrors := .f.

RETURN oCursor

* -------------

FUNCTION SQLFieldBlock( oCursor, nField )

Return {|x|iif(Pcount()==0, oCursor:fieldGet(nField), oCursor:fieldPut(nField, x))}

* -------------

FUNCTION SQLSortBlock( oCursor, nField, nColumn )

RETURN {|a,b,descend|oCursor:sort(IIF(descend,;
{|x,y|IIF(Valtype(x[nField])='C', ;
Upper(x[nField]) > Upper(y[nField]), x[nField] > y[nField])}, ;
{|x,y|IIF(Valtype(x[nField])='C', ;
Upper(x[nField]) < Upper(y[nField]), x[nField] < y[nField])})), ;
nColumn := nField}

The below code (from _DCXBROW.PRG) is what handles navigation in DCBROWSE.
The skipper() method of the SQLexpress class is responsible for navigating correctly.
Have you run any of the sample programs that come with SQLexpress?

I tested all samples from SQL Express,

It is interesting, when run SQLBRO.PRG/EXE, and select View - SQLListTypeInfo,
rows was filtered,
when listing in table with keys up,down, wverything is ok

BUT when click to first row with left mouse button, then scroll up,down,up... rows in table copying, or show not correct, duplicate etc.
after refresh all is again ok.

This looks to not correct controlling input with mouse , or scroll mouse winder.

The same is in my test program, when I go to first row in table and then scroll up, program do not stop on Bottom but allow listing up, with not correct data (duplicate same row, or show other data )

Hi,
Now I modifyed program, to use not BrowseFilter , but new SELECT, and now works ok browsing, etc.

Only I do not know, why RecCount() did not return count of filtered data, but count record for SELECT :

oDataSet:BrowseFilter := {|aRow,nRow,oData|((oDataSet:FieldGet("CLV"))=val(cislolv) .and. (oDataSet:FieldGet("KU_ID"))=val(cisloku))}
pocetplomb:=oDataSet:RecCount()

In this moment, I can do , what I want, and other things I must study, how it works
Thanks for reply