Change your filter to between 13 and 22 instead of less than 23?

--

rk

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On Behalf Of Rafael Copquin

Sent: Monday, February 21, 2011 7:45 AM

To: profoxtech@leafe.com

Subject: top clause in sql Server

I have a legacy vfp 6 database that I am migrating to SQL Server 2008.

The items table has a field named "itemcode" with a character field type of width 6

This field would suposedly contain alphanumeric codes, but the client always used numeric codes, starting in '1' and ending in '999999'

I migrated the data to a SQL Server table, making the itemcode field of type char(6) not null.

Now I want to bring a limited number of records from this table, to show in a VFP grid. Because the grid can only show 20 records at a time, I developed a pagination routine that only brings 20 records at a time, when the user presses the next page or the previous page buttons on the form.

My problem is with the previous page routine. My statement is:

Text to cCmd textmerge noshow flags 2 pretext 15

select top 20 itemcode,(some more records) from silver.dbo.items where itemcode < 23 order by itemcode desc

endtext

SQLExec(thisform.nHandle,cCmd,'curItems')

The cursor curItems brings the correct list of items (13 to 22) but ordered from 22 to 13. Since I want them to be ordered like: 13 to 22, I change the order by clause as: order by itemcode asc.

But I get records 1 to 10, and I want 13 to 22.

I am aware that I can live with curItems the way SQL Server generates it and then simply issue:

select curItems

index on itemcode tag itemcode

or alternatively

select * from curItems into cursor curAnotherCursor order by itemcode

But I do not want to have a VFP index in this cursor or have to create another cursor from the obtained recordset. I just want to get the cursor directly from SQL Server, ordered from 13 to 22.

Any suggestions?

Rafael Copquin

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0441922355368@ACKBWDDQH1.artfact.local

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.