PC Review


Reply
Thread Tools Rate Thread

Record selection advise needed

 
 
John
Guest
Posts: n/a
 
      7th Jan 2009
Hi

I have a winform database app that loads one record at a time. I use the
following two sqls to allow users to scroll to next and previous records;

MoveNext

SELECT TOP 1 <field list>
FROM tblClients
WHERE (ID > ?)
ORDER BY ID


Move Prev

SELECT TOP 1 <field list>
FROM tblClients
WHERE (ID < ?)
ORDER BY ID DESC

This works fine on a unique id and when records are scrolled in id order.

The client now wants the records scrolled in company type+company name
order. The problem is that niether company type nor company name is unique
and it is difficult to select next record in sequence without a unique id as
to which is the next record. How can I achieve the scrolling in company
type+company name order one record at a time?

Thanks

Regards


 
Reply With Quote
 
 
 
 
Cor Ligthert[MVP]
Guest
Posts: n/a
 
      7th Jan 2009
John,

In my idea do you first have to determ what is the next in those
collections,

Cor

"John" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> I have a winform database app that loads one record at a time. I use the
> following two sqls to allow users to scroll to next and previous records;
>
> MoveNext
>
> SELECT TOP 1 <field list>
> FROM tblClients
> WHERE (ID > ?)
> ORDER BY ID
>
>
> Move Prev
>
> SELECT TOP 1 <field list>
> FROM tblClients
> WHERE (ID < ?)
> ORDER BY ID DESC
>
> This works fine on a unique id and when records are scrolled in id order.
>
> The client now wants the records scrolled in company type+company name
> order. The problem is that niether company type nor company name is unique
> and it is difficult to select next record in sequence without a unique id
> as to which is the next record. How can I achieve the scrolling in company
> type+company name order one record at a time?
>
> Thanks
>
> Regards
>
>


 
Reply With Quote
 
 
 
 
Armin Zingler
Guest
Posts: n/a
 
      7th Jan 2009
Cor Ligthert[MVP] wrote:
> John,
>
> In my idea do you first have to determ what is the next in those
> collections,


I'm not sure if it means the same as I was also about to write:
If you have

company type: 1, name: Ligthert
company type: 1, name: Ligthert

which is the first and which the second? That's what you, John, have to
determine first.


Armin

 
Reply With Quote
 
Jesús
Guest
Posts: n/a
 
      8th Jan 2009
Add the primary key or a unique key to sort order:

MoveNext:

SELECT TOP 1 <field list>
FROM tblClients
WHERE
CompanyType > @LastReadCompanyType
OR (
CompanyType = @LastReadCompanyType
AND CompanyName > @LastReadCompanyName
)
OR (
CompanyType = @LastReadCompanyType
AND CompanyName = @LastReadCompanyName
AND CompanyID > @LastReadCompanyID
)
ORDER BY
CompanyType, CompanyName, CompanyID


MovePrev:

SELECT TOP 1 <field list>
FROM tblClients
WHERE
CompanyType < @LastReadCompanyType
OR (
CompanyType = @LastReadCompanyType
AND CompanyName < @LastReadCompanyName
)
OR (
CompanyType = @LastReadCompanyType
AND CompanyName = @LastReadCompanyName
AND CompanyID < @LastReadCompanyID
)
ORDER BY
CompanyType DESC, CompanyName DESC, CompanyID DESC


Regards

Jesús López




"John" <(E-Mail Removed)> escribió en el mensaje de noticias
news:(E-Mail Removed)...
> Hi
>
> I have a winform database app that loads one record at a time. I use the
> following two sqls to allow users to scroll to next and previous records;
>
> MoveNext
>
> SELECT TOP 1 <field list>
> FROM tblClients
> WHERE (ID > ?)
> ORDER BY ID
>
>
> Move Prev
>
> SELECT TOP 1 <field list>
> FROM tblClients
> WHERE (ID < ?)
> ORDER BY ID DESC
>
> This works fine on a unique id and when records are scrolled in id order.
>
> The client now wants the records scrolled in company type+company name
> order. The problem is that niether company type nor company name is unique
> and it is difficult to select next record in sequence without a unique id
> as to which is the next record. How can I achieve the scrolling in company
> type+company name order one record at a time?
>
> Thanks
>
> Regards
>
>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Record selection advise needed John Microsoft VB .NET 3 8th Jan 2009 01:12 PM
Copy Selection - Paste Selection - Delete Selection Uninvisible Microsoft Excel Programming 2 25th Oct 2007 01:31 PM
Copy Selection - Transpose Selection - Delete Selection Uninvisible Microsoft Excel Misc 2 23rd Oct 2007 04:18 PM
Combo Box selection disappearing on new record and change of option selection EmmA Microsoft Access Forms 2 17th Jun 2004 02:21 AM
Advise needed from windows experts khteo Windows XP Help 5 9th Aug 2003 01:24 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:42 PM.