This topic is locked

Restricting number of records that a member can add

3/2/2007 11:16:57 AM
ASPRunnerPro General questions
T
text author

Hi - I'm using 4.0 build 86
I would like to be able to restict members to adding a certain number of records per member.
So for example Member 1 who can only see and add his own data is only allowed to add up to 10 records likewise for member 2 etc etc.
I know I probably need to use the count function in the add page section but apart from that I am clueless.
Please can you help.
Thanks

Sergey Kornilov admin 3/2/2007

Use BeforeAdd event to calculate how many records this user has already and if limit is reached display the error message and prohibit record addition.
More detailed answer is not possible without knowing details of your project and database structure.

T
text author 3/6/2007

Use BeforeAdd event to calculate how many records this user has already and if limit is reached display the error message and prohibit record addition.

More detailed answer is not possible without knowing details of your project and database structure.


Sorry Sergey I should have mentioned we are using MYSQL with a very simple structure of two tables, a members table (used for the log in process) and the record table which just has three fields:
member_ID - which is related to the Members table
Contact_Name
Contact_Number
regards
Richard

J
Jane 3/6/2007

Richard,
here is a sample code for the AddOnLoad event:

Sub AddOnLoad()

str = "select count(*) from " & strTableName & " where member_ID='" & Session("UserID") & "'"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection

if rsTemp(0)>10 then

%><script>

alert("you can't add more then 10 records");

</script><%

end if

rsTemp.Close : set rsTemp = Nothing

End Sub

T
text author 3/8/2007

Hi Jane
I get this error when I use the sample code:
Error number -2147217900

Error description [MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Member_ID='Richard'' at line 1

URL /nct/contact_add.asp

SQL query select `Contact_id`, `Member_ID`, `Contact_name`, `Contact_number` from `contact` where (1<0) and (`contact`.`Member_ID`='Richard')

Additional info Event: Call AddOnLoad()
Any ideas ?
Thanks
Richard

T
text author 3/8/2007

Actually - sorry I shouldn't have replaced StrTableName I believe.
however with your orginal code it doesn't work, it just allows another record to be added

Sergey Kornilov admin 3/8/2007

Here is the slightly better version that actually prevents new record from being added.
You need to use BeforeAdd event for this purpose:

Function BeforeAdd(dict)
str = "select count(*) from " & strTableName & " where member_ID='" & Session("UserID") & "'"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection
if rsTemp(0)>10 then

Response.Write "No can do"

rsTemp.Close : set rsTemp = Nothing

BeforeAdd = False

Exit Function

end if
BeforeAdd = True
End Function
T
text author 3/9/2007

Here is the slightly better version that actually prevents new record from being added.

You need to use BeforeAdd event for this purpose:

Function BeforeAdd(dict)
str = "select count(*) from " & strTableName & " where member_ID='" & Session("UserID") & "'"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection
if rsTemp(0)>10 then

Response.Write "No can do"

rsTemp.Close : set rsTemp = Nothing

BeforeAdd = False

Exit Function

end if
BeforeAdd = True
End Function


I must be doing something really stupid...it doesn't work it just allows another record to be added. Its odd though because if I use response.write ("rsTemp") to test it will show the correct number of records for the member so its certainly counting the correctly !
Thanks for trying anway

T
text author 3/12/2007

Here is the slightly better version that actually prevents new record from being added.

You need to use BeforeAdd event for this purpose:

Function BeforeAdd(dict)
str = "select count(*) from " & strTableName & " where member_ID='" & Session("UserID") & "'"

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection
if rsTemp(0)>10 then

Response.Write "No can do"

rsTemp.Close : set rsTemp = Nothing

BeforeAdd = False

Exit Function

end if
BeforeAdd = True
End Function



Hi Sergey
I actually don't think this code will work with MYSQL. I think I will probably have to have a Member's total records column to the member's table that is updated and added to and then used to check how many records have been added.
I will let you know.

J
Jane 3/13/2007

This code works fine on my test box.
You can please publish your project on Demo Account and post a URL to your pages here or send it to support@xlinesoft.com.

I'll find what's wrong with your project inspecting it at Demo account site.

T
text author 3/13/2007

This code works fine on my test box.

You can please publish your project on Demo Account and post a URL to your pages here or send it to support@xlinesoft.com.

I'll find what's wrong with your project inspecting it at Demo account site.


Hi Jane
There is some sort of problem with the > number
I placed quotation marks around the 10 and the if statement worked, however it also "worked" when the records were less than 10.
I will upload to a demo site and forward a link to you.
regards
Richard