Another for MDB recorset

[Silvio]

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 ?

[nageswaragunupudi]

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]

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?

[nageswaragunupudi]

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]

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  Expand view  RUN

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) )

[nageswaragunupudi]

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  Expand view  RUN

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


[Silvio]

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 ?

[nageswaragunupudi]

> '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]

I made this but not run

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

[Silvio]

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]

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) )

[nageswaragunupudi]

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.

[nageswaragunupudi]

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

This should run ok.

[Rick Lipkin]

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

[nageswaragunupudi]

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.