Record selection advise needed

Discussion in 'Microsoft ADO .NET' started by John, Jan 7, 2009.

  1. John

    John Guest

    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
     
    John, Jan 7, 2009
    #1
    1. Advertisements

  2. John,

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

    Cor

    "John" <> wrote in message
    news:...
    > 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
    >
    >
     
    Cor Ligthert[MVP], Jan 7, 2009
    #2
    1. Advertisements

  3. 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
     
    Armin Zingler, Jan 7, 2009
    #3
  4. John

    Jesús Guest

    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" <> escribió en el mensaje de noticias
    news:...
    > 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
    >
    >
     
    Jesús, Jan 8, 2009
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Versteijn

    Need advise on application architecture

    Versteijn, Jul 24, 2003, in forum: Microsoft ADO .NET
    Replies:
    6
    Views:
    588
    Versteijn
    Jul 31, 2003
  2. Versteijn

    Need advise on application architecture

    Versteijn, Jul 24, 2003, in forum: Microsoft ADO .NET
    Replies:
    0
    Views:
    408
    Versteijn
    Jul 24, 2003
  3. Stephen Walch

    Please advise on "normalizing" my data sets

    Stephen Walch, Jan 13, 2004, in forum: Microsoft ADO .NET
    Replies:
    5
    Views:
    239
    Kevin Yu [MSFT]
    Jan 16, 2004
  4. John

    App design advise

    John, Jul 12, 2005, in forum: Microsoft ADO .NET
    Replies:
    5
    Views:
    204
    Chad Z. Hower aka Kudzu
    Jul 12, 2005
  5. John

    EF databinding advise needed

    John, Jul 24, 2009, in forum: Microsoft ADO .NET
    Replies:
    0
    Views:
    333
Loading...

Share This Page