December 12, 2015 at 8:45 pm
Would appreciate being pointed to documentation on how to limit entries into a database to a specific number (10, for example) per email address. To be more specific, an authenticated user authenticated by email address (part of db) would be allowed to enter up to 10 records but not allowed to enter more, ever.
My first post so I hope I placed this question in the correct place. Thanks in advance for any help.
December 12, 2015 at 10:14 pm
you could create a stored procedure to do the insert and prior to actually doing it, check the existing count of records for that user and cancel/not do the insert if that user is at his limit already.
December 12, 2015 at 10:49 pm
pietlinden, do you mean for me to physically check to see what the count is, or to write the check into the store procedure some way?
December 12, 2015 at 11:12 pm
This is one option.... kinda sloppy, but it works:
ALTER PROCEDURE uspAddEventParticipant
@ParticipantID INT,
@EventID INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MaxEventsPerParticipant TINYINT = 2; -- just makes this a lot easier to modify...
IF (SELECT COUNT(*) FROM PEvents WHERE ParticipantID = @ParticipantID) < @MaxEventsPerParticipant
INSERT INTO PEvents(ParticipantID, EventID) VALUES (@ParticipantID, @EventID);
ELSE
PRINT CONCAT(@ParticipantID,' is over the limit!');
END
December 12, 2015 at 11:50 pm
pietlinden, I trust I am not getting outside the scope of what I should be asking. I appreciate the example and think I can work it out. However, I am not sure how to use it in conjunction with the form which will be inputting the data entries I want to count and limit in the first place. The form uses the following to insert into the database:
SqlCommand storeimage = new SqlCommand("INSERT INTO Property"
+ "(YourName, EmailAddress, Broker, BrokerNumber, Company, ListingAgentName, AgentNumber, Type, CategoryType,)"
+ "values (@YourName, @EmailAddress, @Broker, @BrokerNumber, @Company, @ListingAgentName, @AgentNumber, @Type, @CategoryType)", myConnection);
The insert is actually longer than this but this gives an idea of what I am doing.
I guess my question is: How does the form use the above insert statement in conjunction with the stored procedure. I assume at some point the procedure has to be called but How is the question.
It is not as simple as just replacing the above insert with the stored procedure you gave as I am using drop down lists - which are supplied from other stored procedures - to give choices on certain categories of the input form.
Thanks for any help. BTW, EmailAddress is what I will be counting.
December 13, 2015 at 12:07 am
Surfed around for a second and found this:
http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp
then you'd just call the stored procedure that way.
Thanks for any help. BTW, EmailAddress is what I will be counting.
The code I provided was just a sample from something I had in one of my databases. it's just a guideline. Try to write it yourself, and if you get stuck, post. Sorry, but unless you try it yourself, you won't learn.
December 13, 2015 at 12:51 am
Thanks a million and I understand about the learning. Appreciate the help.
December 13, 2015 at 1:10 am
You're welcome. Post back if you get stuck.
December 14, 2015 at 2:19 pm
pietlinden, just to let you know what I did and it works fine, I used a trigger instead of a sp. The only problem I have now is trying to find out how to make the exception message appear friendlier to the user. This is the trigger I used.
ALTER trigger [dbo].[tri_EmailAddress] on [dbo].[Property]
for insert as
if exists (select * from [Property] od
join (select distinct EmailAddress, Free from inserted) i
on i.EmailAddress = od.EmailAddress
group by od.EmailAddress having count (*) >2)
begin
raiserror ('Cannot have more than ten free listings', 16, 1)
rollback tran
return
end
Thanks again for pointing me in the right direction.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply