Hi there Stephen I developed a class which I call "pagination", which enables me to bring just the number of items that fit in a page of a grid, either on a form directly or a pageframe. The idea behind it all is best described in this article I wrote some time ago in the UniversalThread:
http://www.universalthread.com/ViewPageArticle.aspx?ID=847 At the time I knew nothing about SQL Server but the technique works very well in Visual FoxPro. Now that I am migrating a VFP app to SQL server I decided to use the same tecnique to bring only those records that can be viewed. Some of the local SQL gurus told me that I should forget about the VCR type of controls we use in VFP to navigate a table, because that was not possible with SQL. The class I developed following the concepts described in my article proved them absolutely wrong. I created a container control with VCR buttons (top,previous,next,last and next page and previous page) that is stuck directly on a form derived from a class that manipulates records from a SQL database (using functions like sqlexec,sqlconnect, etc) and I was very glad to show to those gurus that the fox is not as dead as they all thought (:-)) Regards Rafael Copquin El 21/02/2011 17:06, Stephen Russell escribió: > 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. > > _______________________________________________ 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.
