Another for MDB recorset

Silvio · Post by **Silvio** » Sat Feb 09, 2008 11:07 am

I must create two index ord on a table of a Mdb

Mdb : ecom
Table : Utenti

first = Id
second = cognome

How I must make it ?

Post by **nageswaragunupudi** » Sat Feb 09, 2008 11:39 am

When we deal with RDMS, it is not necessary to create any index to read the table in a particular order. Careful choice of indexes on very large table improves the performance of queries.

If you want to read the columns in the order of Id the query should be "SELECT ID, COGNOME FROM UTENTI ORDER BY ID" or if you want to read in the order or cognome, say ORDERY BY COGNOME.

You can even change the order after reading the recordset.

oRs:Sort := 'ID'
or
oRs:Sort := 'COGNOME'

For all this it is not necessary to create indexes.

Silvio · Post by **Silvio** » Sat Feb 09, 2008 11:53 am

i WANTED TO CREATE A TAB WITH TWO FOLDER
ON THE FIRST i MUST INSERT "id" AND ON THE SECOND "cOGNOME"

WHEN i CLICK ON FIRST TAB THE XBROWSE MUST CHANGE VISUALIZATION
@ oApp():oDlg:nGridBottom, nSplit+2 TABS oApp():oTab ;
OPTION nOrder SIZE oApp():oWndMain:nWidth()-50, 12 PIXEL OF oApp():oDlg ;
ITEMS ' First ', ' Last '
ACTION ( nOrder := oApp():oTab:nOption ,;
CU->(DbSetOrder(nOrder)),;
CU->(DbGoTop()) ,; oApp():oGrid:Refresh(.t.) ,;
RefreshCont(oCont, "CU") )
IMAGES "image1.bmp","image2.bmp"

HOW i CAN MAKE IT?

Post by **nageswaragunupudi** » Sat Feb 09, 2008 12:09 pm

For RecordSets:

oRs:Sort := "ID"

is equilvalent to ALIAS->(ORDSETFOCUS("ID"))

You can change sort orders viewed by simply assigning the values to oRs:Sort

Examples:

oRs:Sort := "COLNAME"
oBrw:Refresh()

Silvio · Post by **Silvio** » Sat Feb 09, 2008 12:17 pm

ok but Ihave an variable
nOrder := 1

and then on tab I insert

ACTION ( nOrder := oApp():oTab:nOption ,;
oRs:Sort := (nOrder),;
oRs:MoveFirst())

it make error !!

Code: Select all

 Path and name: C:\work\prg\Mwopen_W\source\FSDI2006.Exe (32 bits)
   Size: 1,709,568 bytes
   Time from start: 0 hours 0 mins 4 secs 
   Error occurred at: 11-02-2008, 13:21:36
   Error description: Error ADODB.Recordset/16389  E_FAIL: _SORT
   Args:
     [   1] = N   2

Stack Calls
===========
   Called from: win32ole.prg => TOLEAUTO:_SORT(0)
   Called from: pcustomer.prg => (b)CUSTOMER(208)
   Called from: TTabs.prg => TTABS:SETOPTION(712)
   Called from: TTabs.prg => TTABS:LBUTTONDOWN(632)
   Called from:  => TWINDOW:HANDLEEVENT(0)
   Called from: CONTROL.PRG => TTABS:HANDLEEVENT(0)
   Called from: WINDOW.PRG => _FWH(0)
   Called from:  => WINRUN(0)
   Called from: WINDOW.PRG => TWINDOW:ACTIVATE(0)
   Called from: main.prg => TAPPLICATION:ACTIVATE(148)
   Called from: main.prg => MAIN(36)

I write this

@ oApp():oDlg:nGridBottom, nSplit+2 TABS oApp():oTab ;
OPTION nOrder SIZE oApp():oWndMain:nWidth()-50, 12 PIXEL OF oApp():oDlg ;
ITEMS ' First ', ' Last ' ;
ACTION ( nOrder := oApp():oTab:nOption ,;
oRs:Sort := (nOrder),;
oRs:MoveFirst() ,;
oApp():oGrid:Refresh(.t.) ,;
RefreshCont(oCont,oRs) )

Post by **nageswaragunupudi** » Sat Feb 09, 2008 12:34 pm

You should assign column name or names delimited by commas. Not Numeric Values.

CORRECT :
oRs:Sort := 'ID'
oRs:Sort := 'COGNOME'

WRONG:
oRs:Sort := 1 or 2

Try something like this:

Code: Select all

 ACTION ( nOrder := oApp():oTab:nOption , oRs:Sort := oRs:Fields(nOrder-1):Name,  ..... )

Silvio · Post by **Silvio** » Sat Feb 09, 2008 12:59 pm

Now I made

@ oApp():oDlg:nGridBottom, nSplit+2 TABS oApp():oTab ;
OPTION nOrder SIZE oApp():oWndMain:nWidth()-50, 12 PIXEL OF oApp():oDlg ;
ITEMS ' First ', ' Last ' ;
ACTION ( nOrder := oApp():oTab:nOption ,;
oRs:Sort := oRs:Fields(nOrder-1):Name,;
oRs:MoveFirst() ,;
oApp():oGrid:Refresh(.t.) ,;
RefreshCont(oCont,oRs) )

Application
===========
Path and name: C:\work\prg\Mwopen_W\source\FSDI2006.Exe (32 bits)
Size: 1,789,952 bytes
Time from start: 0 hours 0 mins 3 secs
Error occurred at: 11-02-2008, 14:00:09
Error description: Error ADODB.Recordset/16389 E_FAIL: _SORT
Args:
[ 1] = C UserID

Stack Calls
===========
Called from: win32ole.prg => TOLEAUTO:_SORT(0)
Called from: pcustomer.prg => (b)CUSTOMER(206)
Called from: TTabs.prg => TTABS:SETOPTION(712)
Called from: TTabs.prg => TTABS:LBUTTONDOWN(632)
Called from: => TWINDOW:HANDLEEVENT(0)
Called from: CONTROL.PRG => TTABS:HANDLEEVENT(0)
Called from: WINDOW.PRG => _FWH(0)
Called from: => WINRUN(0)
Called from: WINDOW.PRG => TWINDOW:ACTIVATE(0)
Called from: main.prg => TAPPLICATION:ACTIVATE(149)
Called from: main.prg => MAIN(37)

i'm afraid thart oRs:Sort take only filed name and not field number

any idea ?

Post by **nageswaragunupudi** » Sat Feb 09, 2008 1:03 pm

> 'm afraid thart oRs:Sort take only filed name and not field number

Yes. It takes only field name. but not field number.

make sure you assign the right field names to oRs:Sort

Silvio · Post by **Silvio** » Sat Feb 09, 2008 1:07 pm

I made this but not run

oRs:Sort :=iff( norder=1, oRs:Fields( "ID" ):Name,oRs:Fields( "Cognome" ):Name);

Silvio · Post by **Silvio** » Sat Feb 09, 2008 1:10 pm

and

@ oApp():oDlg:nGridBottom, nSplit+2 TABS oApp():oTab ;
OPTION nOrder SIZE oApp():oWndMain:nWidth()-50, 12 PIXEL OF oApp():oDlg ;
ITEMS ' First ', ' Last ' ;
ACTION ( nOrder := oApp():oTab:nOption ,;
if( norder=1, oRs:Sort( "ID" ),oRs:Sort( "Cognome" )),;
oRs:MoveFirst() ,;
oApp():oGrid:Refresh(.t.) ,;
RefreshCont(oCont,oRs) )

but it not run !!

Silvio · Post by **Silvio** » Sat Feb 09, 2008 1:12 pm

I try also this but not run

@ oApp():oDlg:nGridBottom, nSplit+2 TABS oApp():oTab ;
OPTION nOrder SIZE oApp():oWndMain:nWidth()-50, 12 PIXEL OF oApp():oDlg ;
ITEMS ' First ', ' Last ' ;
ACTION ( nOrder := oApp():oTab:nOption ,;
if( norder=1, oRs:Sort := 'ID' ,oRs:Sort := 'COGNOME' ),;
oRs:MoveFirst() ,;
oApp():oGrid:Refresh(.t.) ,;
RefreshCont(oCont,oRs) )

Post by **nageswaragunupudi** » Sat Feb 09, 2008 1:26 pm

Please send to my personal email, zip of your mdb, if it is not confidential. I send back a simple working code, which you can incorporate.

Post by **nageswaragunupudi** » Sat Feb 09, 2008 1:59 pm

> if( norder=1, oRs:Sort := 'ID' ,oRs:Sort := 'COGNOME' ),;

This should run ok.

Rick Lipkin · Post by **Rick Lipkin** » Sat Feb 09, 2008 2:53 pm

question ... on a related note .. consider this query :

Select lname,fname from Customer order by lname
...
...

lets say I want to re-order my existing recordset created by the above query .. are you saying I can oRs:Sort := 'fname' to reorder ?? or do I need to re-create the query with order by fname ?

Rick Lipkin

Post by **nageswaragunupudi** » Sat Feb 09, 2008 3:03 pm

Mr Rick Liptin

>
ets say I want to re-order my existing recordset created by the above query .. are you saying I can oRs:Sort := 'fname' to reorder ??
>

Yes.

>
or do I need to re-create the query with order by fname ?
>
No need at all.

If we do, that will increase response time and unnecessary burden on the server and also the network traffic.

Actually, it is a good idea not to request an ordered set from the server, but to do the sorting on the client. When we request an ordered set, the sever has to do an additional work of sorting, creating temporary tables and so on. We should place the least strain on the server and and the network bandwidth.