Query?

Discussion in 'ASP General' started by Oli, Dec 4, 2003.

  1. Oli

    Oli Guest

    Hello today!

    Right - hopefully this is the only question of the day.

    I have a database where the default value of "AllocatedTo" is "0". When
    AllocatedTo corresponds to a company name in the session then the user can
    see the value.

    What I need to do is to allocate to a user, have a textbox asking "how many
    units to allocate?" and a dropdown box listing users. Assume I enter 50.
    The database then must be searched for the first 50 records where
    AllocatedTo=0 and changed to the corresponding company name of that user.

    Any ideas?

    TIA
    Oli
     
    Oli, Dec 4, 2003
    #1
    1. Advertising

  2. Oli

    Rob Meade Guest

    "Oli" wrote ...

    > What I need to do is to allocate to a user, have a textbox asking "how

    many
    > units to allocate?" and a dropdown box listing users. Assume I enter 50.
    > The database then must be searched for the first 50 records where
    > AllocatedTo=0 and changed to the corresponding company name of that user.


    <%
    intAllocation = Request.Form("allocation") ' this would be your 50

    strSQL = "SELECT TOP " & intAllocation & " FROM tblTableName WHERE
    AllocatedTo = '0'"

    %>


    Not quite sure what you mean about the 'changed to the corresponding company
    name of that user' etc?

    Do you first select the 50 rows where its 0 and then after you've done that
    update those 50 rows to then have the allocatedTo field = the company name??

    Regards

    Rob
     
    Rob Meade, Dec 4, 2003
    #2
    1. Advertising

  3. Oli

    Bob Barrows Guest

    Oli wrote:
    > Hello today!
    >
    > Right - hopefully this is the only question of the day.
    >
    > I have a database where the default value of "AllocatedTo" is "0".
    > When AllocatedTo corresponds to a company name in the session then
    > the user can see the value.
    >
    > What I need to do is to allocate to a user, have a textbox asking
    > "how many units to allocate?" and a dropdown box listing users.
    > Assume I enter 50. The database then must be searched for the first
    > 50 records where AllocatedTo=0 and changed to the corresponding
    > company name of that user.
    >
    > Any ideas?
    >
    > TIA
    > Oli


    What database and version?
    What is the name of the table?

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Dec 4, 2003
    #3
  4. Oli

    Bob Barrows Guest

    Oli wrote:
    > Hello today!
    >
    > Right - hopefully this is the only question of the day.
    >
    > I have a database where the default value of "AllocatedTo" is "0".
    > When AllocatedTo corresponds to a company name in the session then
    > the user can see the value.
    >
    > What I need to do is to allocate to a user, have a textbox asking
    > "how many units to allocate?" and a dropdown box listing users.
    > Assume I enter 50. The database then must be searched for the first
    > 50 records where AllocatedTo=0 and changed to the corresponding
    > company name of that user.
    >
    > Any ideas?
    >
    > TIA
    > Oli


    Oh! And where does the company name for the user come from?

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Dec 4, 2003
    #4
  5. Oli

    Ray at Guest

    If I follow you, you'd want to execute something like:

    UPDATE SomeTable SET CompanyName='your value' WHERE TheUniqueID IN (SELECT
    TOP 50 UniqueID FROM SomeTable WHERE AllocatedTo=0 ORDER BY SomeColumn)

    The order by is what will determine "first" 50. There is no natural "first"
    in a database table.

    Ray at work


    "Oli" <> wrote in message
    news:bqndef$c6q$...
    > Hello today!
    >
    > Right - hopefully this is the only question of the day.
    >
    > I have a database where the default value of "AllocatedTo" is "0". When
    > AllocatedTo corresponds to a company name in the session then the user can
    > see the value.
    >
    > What I need to do is to allocate to a user, have a textbox asking "how

    many
    > units to allocate?" and a dropdown box listing users. Assume I enter 50.
    > The database then must be searched for the first 50 records where
    > AllocatedTo=0 and changed to the corresponding company name of that user.
    >
    > Any ideas?
    >
    > TIA
    > Oli
    >
    >
    >
     
    Ray at, Dec 4, 2003
    #5
  6. Oli

    Oli Guest

    Hi Rob

    You are correct with your assumption:
    > Do you first select the 50 rows where its 0 and then after you've done

    that
    > update those 50 rows to then have the allocatedTo field = the company

    name??

    So how would I integrate this into your code?

    TIA
    Oli



    "Rob Meade" <> wrote in message
    news:...
    > "Oli" wrote ...
    >
    > > What I need to do is to allocate to a user, have a textbox asking "how

    > many
    > > units to allocate?" and a dropdown box listing users. Assume I enter

    50.
    > > The database then must be searched for the first 50 records where
    > > AllocatedTo=0 and changed to the corresponding company name of that

    user.
    >
    > <%
    > intAllocation = Request.Form("allocation") ' this would be your 50
    >
    > strSQL = "SELECT TOP " & intAllocation & " FROM tblTableName WHERE
    > AllocatedTo = '0'"
    >
    > %>
    >
    >
    > Not quite sure what you mean about the 'changed to the corresponding

    company
    > name of that user' etc?
    >
    > Do you first select the 50 rows where its 0 and then after you've done

    that
    > update those 50 rows to then have the allocatedTo field = the company

    name??
    >
    > Regards
    >
    > Rob
    >
    >
     
    Oli, Dec 4, 2003
    #6
  7. Oli

    Oli Guest

    Hi Bob

    It an Access DB stored in d:\iis_database\userdata.mdb

    Using standard ASP (i.e. not .net)

    tblNumbers is the table where the column AllocateTo is stored and the users
    come from Members.

    TIA
    Oli


    "Bob Barrows" <> wrote in message
    news:...
    > Oli wrote:
    > > Hello today!
    > >
    > > Right - hopefully this is the only question of the day.
    > >
    > > I have a database where the default value of "AllocatedTo" is "0".
    > > When AllocatedTo corresponds to a company name in the session then
    > > the user can see the value.
    > >
    > > What I need to do is to allocate to a user, have a textbox asking
    > > "how many units to allocate?" and a dropdown box listing users.
    > > Assume I enter 50. The database then must be searched for the first
    > > 50 records where AllocatedTo=0 and changed to the corresponding
    > > company name of that user.
    > >
    > > Any ideas?
    > >
    > > TIA
    > > Oli

    >
    > Oh! And where does the company name for the user come from?
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    Oli, Dec 4, 2003
    #7
  8. Oli

    Ray at Guest

    That should have been TheUniqueID in the subquery as well.

    Ray at work

    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:...
    > If I follow you, you'd want to execute something like:
    >
    > UPDATE SomeTable SET CompanyName='your value' WHERE TheUniqueID IN (SELECT
    > TOP 50 UniqueID FROM SomeTable WHERE AllocatedTo=0 ORDER BY SomeColumn)
    >
    > The order by is what will determine "first" 50. There is no natural

    "first"
    > in a database table.
    >
    > Ray at work
    >
    >
    > "Oli" <> wrote in message
    > news:bqndef$c6q$...
    > > Hello today!
    > >
    > > Right - hopefully this is the only question of the day.
    > >
    > > I have a database where the default value of "AllocatedTo" is "0". When
    > > AllocatedTo corresponds to a company name in the session then the user

    can
    > > see the value.
    > >
    > > What I need to do is to allocate to a user, have a textbox asking "how

    > many
    > > units to allocate?" and a dropdown box listing users. Assume I enter

    50.
    > > The database then must be searched for the first 50 records where
    > > AllocatedTo=0 and changed to the corresponding company name of that

    user.
    > >
    > > Any ideas?
    > >
    > > TIA
    > > Oli
    > >
    > >
    > >

    >
    >
     
    Ray at, Dec 4, 2003
    #8
  9. Oli

    Rob Meade Guest

    "Oli" wrote ...

    > So how would I integrate this into your code?


    Hi Oli,

    Probably depends what you want to do a bit here...

    Do you need to use these first 50 (or however many) rows for anything else
    other than updating them?

    Also - are you assuming that the user is the 'company name' etc - ie,
    someone logs in for ONE company - so in this case the AllocatedTo - would be
    that id?


    Regards

    Rob
     
    Rob Meade, Dec 4, 2003
    #9
  10. Oli

    Bob Barrows Guest

    I still don't have enough information. Does the company name come from the
    Members table? Could you shouw us the structures of the two tables involved?

    Bob Barrows
    Oli wrote:
    > Hi Bob
    >
    > It an Access DB stored in d:\iis_database\userdata.mdb
    >
    > Using standard ASP (i.e. not .net)
    >
    > tblNumbers is the table where the column AllocateTo is stored and the
    > users come from Members.
    >
    > TIA
    > Oli
    >
    >
    > "Bob Barrows" <> wrote in message
    > news:...
    >> Oli wrote:
    >>> Hello today!
    >>>
    >>> Right - hopefully this is the only question of the day.
    >>>
    >>> I have a database where the default value of "AllocatedTo" is "0".
    >>> When AllocatedTo corresponds to a company name in the session then
    >>> the user can see the value.
    >>>
    >>> What I need to do is to allocate to a user, have a textbox asking
    >>> "how many units to allocate?" and a dropdown box listing users.
    >>> Assume I enter 50. The database then must be searched for the first
    >>> 50 records where AllocatedTo=0 and changed to the corresponding
    >>> company name of that user.
    >>>
    >>> Any ideas?
    >>>
    >>> TIA
    >>> Oli

    >>
    >> Oh! And where does the company name for the user come from?
    >>
    >> Bob Barrows
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Dec 4, 2003
    #10
    1. Advertising

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. Learner
    Replies:
    1
    Views:
    992
    Marina Levit [MVP]
    Jan 30, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,480
    Anonymous
    Oct 13, 2005
  3. David Gordon

    xpath query query

    David Gordon, May 18, 2005, in forum: XML
    Replies:
    2
    Views:
    797
    David Gordon
    May 18, 2005
  4. Eric Nelson
    Replies:
    5
    Views:
    1,533
    Alexey Smirnov
    Feb 4, 2009
  5. Jon F.

    CAML Query: Multiple Query Fields Issue

    Jon F., May 12, 2004, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    758
    Jon F.
    May 12, 2004
Loading...

Share This Page