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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Versteijn

    Need advise on application architecture

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

    Need advise on application architecture

    Versteijn, Jul 24, 2003, in forum: Microsoft ADO .NET
    Replies:
    0
    Views:
    475
    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:
    324
    Kevin Yu [MSFT]
    Jan 16, 2004
  4. John

    App design advise

    John, Jul 12, 2005, in forum: Microsoft ADO .NET
    Replies:
    5
    Views:
    311
    Chad Z. Hower aka Kudzu
    Jul 12, 2005
  5. Guest
    Replies:
    4
    Views:
    457
    Guest
    Oct 25, 2005
  6. John

    Reformed access user needs advise for future

    John, Feb 7, 2008, in forum: Microsoft ADO .NET
    Replies:
    6
    Views:
    262
    Spam Catcher
    Feb 9, 2008
  7. John

    New db application advise

    John, Mar 28, 2008, in forum: Microsoft ADO .NET
    Replies:
    3
    Views:
    215
    Backwater Geezer
    Mar 31, 2008
  8. John

    EF databinding advise needed

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