On Mon, Feb 21, 2011 at 7:27 AM, Rafael Copquin <[email protected]> wrote: > 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. ------------------------------ non SP version for you.
How do you know that you want itmcode between 13-22? Because the first pass of top 20 gave you to itmcode 12? How do you know that you are not loosing any rows between set 1 and set 2? Will there always be a 1:1 relationship code to row for each itmcode that is not deleted? If that is so you can select distinct itmcode from table order by itmcode. Now you know all of them. To get the details of only 20 you pass in a string of in list( ) select itemcode, other columns from silver.dbo.items where itemcode in ( 20, 21, 23, 25, 30, 33. ... all 20 listed here) order by itemcode How about that? Now how do you get the list? Make a function to iterate through from start to current set you want and then string out the next 20 or eof() on that distinct pull you did earlier. -- Stephen Russell Sr. Production Systems Programmer CIMSgts 901.246-0159 cell _______________________________________________ Post Messages to: [email protected] 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/[email protected] ** 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.
