ClaireinLeeds
|
| Posted: 10/06/2003, 12:29 AM |
|
Help! I'm using codecharge 2, ASP and Access,
I have two tables, Users and Entries.
When a user adds a record to the entries table, the owner of that entry is recorded (by inserting the UserID session variable into each record that user creates),that works fine, but!....in each users record, is a field called MaxEntries, as the name suggests, I need to restrict the number of entries a user can make to the "entries" table, to whatever their "maxentries" value is, is this an easy thing to do? whats the best way to do it?
Any help would be very much appreciated...thanks.
Claire
|
|
|
 |
ryan
|
| Posted: 10/06/2003, 1:02 AM |
|
As I visualize your tables
Users(userid,username)
userid--------username
1-------------ryan
2-------------leed
Entries(entryid,userid,maxentries)
entryid-------userid-------maxentries
1-------------1------------5
2-------------2------------4
TriggerPerUser(triggerid,userid)
triggerid-----------userid
0-------------------1
0-------------------2
Hmm...clearly I must only be allowed 5 dbase entries
and 4 for leed, not sure of the shortcut but I created
TriggerPerUser table with triggerid as integer (default 0)
and userid field
BEFORE INSERT or UPDATE to Entries the triggerid will add for that
user
CDbl(rs("triggerid")) = CDbl(rs("triggerid")) + 1
triggerid = CDbl(rs("triggerid"))
Then check triggerid if it exceeds the Entries.maxentries:-» you make
a condition for that :)
IF triggerid > rs("maxentries") THEN
'do not insert redirect or something
ELSE
'DBConnectionName.Execute(SQLforINSERT)
END IF
------------however the whole algo becomes complicated
a>for every Users.userid created you must also insert TriggerPerUser.userid
(2 INSERTS)
b>you can use the cascade updated/cascade deleted ENFORCE on Access
relationship to take care of the deletes and not code it or
have an event for deleting it
c>you must do the proper sequence
UPDATE TriggerPerUser
SET triggerid="&triggeridincrementvariable
WHERE (Userid="&UserIDvariable&")"
then the condition
i hope that helps...get cracking girl
|
|
|
|
ClaireinLeeds
|
| Posted: 10/06/2003, 1:49 AM |
|
Ryan, thanks...that helps a lot, but would it not be easier to increment/decrement the "maxentries" field using an event, rather than creating a whole new table?
Claire
|
|
|
|
ryan
|
| Posted: 10/06/2003, 8:55 PM |
|
I do not know of your actual schema so I created the trigger table
you can create a triggerid field on the Userstable with a default 0. Or you can do it with an incrementing application variable. However incrementing the maxentries hmmm I am not quite sure how you would make this easy
Users
userid----------username-----allowedentry
1---------------ryan---------5
2---------------leed---------4
Entries
entryid---------userid-------maxentries
1---------------1------------0(increment by one)
2---------------2------------0(increment by one)
If Users.allowedentry > Entries.maxentries then
'redirect stop insert
else
DBConnectionName.Execute(SQLforINSERT)
end if
Just note two fields/variable/whatever
a>the field you will assign the number of entries allowed per user
b>the incrementing field
|
|
|
|
|
