if open data with FW_OpenTRecSet(oCon, cSql) then
the data in oBrw2 is not refreshed,
but if it is open in FW_OpenRecordSet( oCon, cSql ) mode then the data in oBrw2 can be displayed.
in TRecSet mode can run if only filter command.
while in complex applications, I don't use filter mode. '
This is just a simple example of an application that uses complex queries
Code: Select all
#include "FiveWin.ch"
function Main()
local oDlg, oBrw1, oBrw2, ors1, ors2
local xcode := "", xunit := "", i
local aLocationDesc := {}, aLocationCode := {}
aLocationCode := { "001", "002", "003", "004"}
aLocationDesc := { "Location 001", "Location 002", "Location 003", "Location 004" }
ors1 := open_rs1()
if ors1:Recordcount > 0
// TRecSet
// xcode := ors1:Code
// xunit := ors1:Brand
// ADO
xcode := ors1:fields("Code"):Value
xunit := ors1:fields("Brand"):Value
Endif
ors2 := open_rs2( xcode )
DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL TITLE "ADO vs TRecSet" RESIZABLE
@ 5, 5 XBROWSE oBrw1 SIZE 200,-20 PIXEL OF oDlg ;
DATASOURCE ors1
COLUMNS "CODE", "BRAND" ;
HEADERS "CODE", "BRAND" ;
CELL LINES NOBORDER AUTOSORT
oBrw1:CreateFromCode()
oBrw1:bChange := ;
<||
// the difference between TRecSet and ADO RecordSet :
// TRecSet
xcode := ors1:Code
xUnit := ors1:Brand
// 1 : display data on oBrw2 with open new recordset ---> Data in oBrw2 not displayed
//
ors2 := open_rs2( xcode )
oBrw2:oRs := ors2
oBrw2:Refresh()
// filter mode: ---> on oBrw2 with filter mode, data can be displayed
//
ors2:filter := ""
ors2:filter := "CODE='" + xcode + "'"
oBrw2:Refresh()
// 2 : ADO
// data can be displayed in the new open recordset mode or filter mode
//
xcode := ors1:fields("Code"):Value
xunit := ors1:fields("Brand"):Value
// open new recordset = FW_OpenRecordSet( oCn, cSql ) ---> Data in oBrw2 can displayed
ors2 := open_rs2( xcode )
oBrw2:oRs := ors2
oBrw2:Refresh()
// filter mode: ---> oBrw2 displayed
//
ors2:filter := ""
ors2:filter := "CODE='" + xcode + "'"
oBrw2:Refresh()
oBrw2:SetGroupHeader("DETAIL UNIT : " + xUnit, 1, 3)
Return Nil
>
@ 5,215 XBROWSE oBrw2 SIZE 200,-20 PIXEL OF oDlg ;
DATASOURCE ors2
COLUMNS "UNIT", "LOCATION", "SERIALNO", "YEAR", "CONDITION" ;
HEADERS "UNIT", "LOCATION / POSITION", "SERIALNO", "YEAR", "CONDITION" ;
CELL LINES NOBORDER AUTOSORT
with object oBrw2
:AutoFit()
:CreateFromCode()
End
for i := 1 to 5
if i = 2
oBrw2:aCols[i]:nEditType := EDIT_LISTBOX
Else
oBrw2:aCols[i]:nEditType := EDIT_GET
Endif
next i
oBrw2:aCols[2]::aEditListTxt := aLocationDesc
oBrw2:aCols[2]:aEditListBound := aLocationCode
oBrw2:aCols[2]:bEditValue := {|| ors2:fields("location"):value }
oBrw2:SetGroupHeader("DETAIL UNIT : " + xUnit, 1, 3)
ACTIVATE DIALOG oDlg CENTERED NOMODAL
return nil
static Function open_rs1
local cSQL, ores, oCon := connect_to_oracle()
// my header table like this :
// CODE VARCHAR2(5 BYTE)
// BRAND VARCHAR2(50 BYTE)
// FOTO BLOB
// AKTIF VARCHAR2(1 BYTE)
// my data like this :
// CODE BRAND
//----------------
// 001 BRAND 1
// 002 BRAND 2
// 003 BRAND 3
text into cSql
select * from MACHINE_HDR order by kode
endtext
// Return AdoExecute( oCon, { |oCn| FW_OpenTRecSet( oCn, cSql ) } )
Return AdoExecute( oCon, { |oCn| FW_OpenRecordSet( oCn, cSql ) } )
static Function open_rs2( xcode )
local cSQL, oCon := connect_to_oracle()
// detail table like this
// CODE VARCHAR2(5 BYTE)
// LOCATION VARCHAR2(5 BYTE)
// SERIALNO VARCHAR2(50 BYTE)
// YEAR VARCHAR2(4 BYTE)
// CONDITION VARCHAR2(100 BYTE)
// my data like this :
// CODE LOCATION SERIALNO YEAR CONDITION
//----------------------------------------
// 001 001 SERIAL 111 2010 Condition 1
// 001 001 SERIAL 22 2013 Condition 2
// 003 002 SERIAL 002 2014 Condition 1
// 003 003 SERIAL 003 2015 Condition 3
if ! Empty( xcode )
text into cSql
select * from MACHINE_DTL where kode = &1 order by kode
endtext
cSql := FW_AdoApplyParams( cSql, { xcode } )
Else
text into cSql
select * from MACHINE_DTL order by code
endtext
Endif
// Return AdoExecute( oCon, { |oCn| FW_OpenTRecSet( oCn, cSql ) } )
Return AdoExecute( oCon, { |oCn| FW_OpenRecordSet( oCn, cSql ) } )
static Function connect_to_oracle()
local oCon
// etc
// TNS and connection string to open oracle database server
// ...
Return oCon
// the function on this forum
function AdoExecute( oCn, bAction )
local uRet
if oCn:State == 0 .or. Empty( oCn:Properties( "Current Catalog" ):Value )
oCn:Close()
oCn:Open()
endif
if oCn:State > 0
TRY
uRet := Eval( bAction, oCn )
CATCH
FW_ShowAdoError( oCn )
END
else
? "Connection Lost. Check your connection"
endif
return uRet
//--------------------------------------------------//
all solutions are welcome.
thanks.
Regards.
Mulyadi
