Query?

[Oli]

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

 

[Rob Meade]

...

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

 

[Bob Barrows]

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

 

[Bob Barrows]

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

 

[Ray at]

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]

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

 

[Oli]

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

 

[Ray at]

That should have been TheUniqueID in the subquery as well.

Ray at work

 

[Rob Meade]

...

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

 

[Bob Barrows]

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