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
_______________________________________________
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/4D625E52.6010404@fibertel.com.ar
** 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.
On Mon, Feb 21, 2011 at 6:45 AM, Rafael Copquin
<rcopquin@fibertel.com.ar> 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'
------------
You could add computed column to the table that is not a real column
and use that as well.
--
Stephen Russell
Sr. Production Systems Programmer
CIMSgts
901.246-0159 cell
_______________________________________________
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/AANLkTim_AotW79Z_9NjBjdMZriAfVjygTA5wSbTZQ3ki@mail.gmail.com
** 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.
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.
On Thu, Feb 24, 2011 at 9:18 AM, Richard Kaye <RKaye@artfact.com> wrote:
> Change your filter to between 13 and 22 instead of less than 23?
----------
When you know your data you can code it to be between 13 and 22. How
do you let the data grow and know what values to pass in?
The code to multiply the volume of the sub query x the page # you are
on is nice when the index is char() and they fill it with int. This
will bite developers when they mix up the values in the char() on you.
Just saying.
--
Stephen Russell
Sr. Production Systems Programmer
CIMSgts
901.246-0159 cell
_______________________________________________
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/AANLkTinY0wugWLhepjEjaZqhbN99dFxDEB4D+6jHjDGX@mail.gmail.com
** 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.