Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dinghus
Starting Member
4 Posts |
 Posted - 2009-03-30 : 16:52:36
|
| This one should be so easy but I am obviously missing something.Basically, when a person submits their login info to a website, the stored procedure is called to 1) verify their login 2) update a "logins" counter in the table 3) they are only allowed 3 logins This is for downloading software they have bought.Everything works great EXCEPT the increment.ALTER PROCEDURE CheckLogIn@email varchar(50),@password varchar(50),@validate bit OUTPUTASBEGINDECLARE @verified varchar;SET @verified = (SELECT transactionID FROM Purchases WHERE email=@email AND pword=@password);IF @verified IS NULL set @validate = 0ELSE DECLARE @counter int; SET @counter = (select count(transactionID) FROM purchases WHERE transactionid = @verified); IF @counter >= 3 set @validate = 0 ELSE BEGIN SET @counter = @counter + 1; UPDATE Purchases SET logins = @counter WHERE transactionid = @verified; SET @validate = 1 ENDENDGOThat is my latest attempt by pulling the count of logins out and then incrementing it and then trying to update the table.Help me before my forehead is bloody from beating my head against my monitor. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dinghus
Starting Member
4 Posts |
Posted - 2009-03-30 : 17:42:37
|
| The value in the logins field is not updating.I primarily work in Oracle, so I'm thinking I'm not implementing this correctly. The default value is 0 since I don't want a null in there. I've tried the update with logins=logins+1 and that didn't work either. I know it is getting inside that loop because I get a return of 1 (meaning successful login). |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dinghus
Starting Member
4 Posts |
Posted - 2009-03-30 : 18:31:39
|
| Should have clarified that the field "logins" is default to 0 when the file is created. But that is a good point about setting @counter to 0. Still doesn't explain why doing an increment (logins = logins + 1) doesn't work. But I'll see if this helps. |
|
|
|
Dinghus
Starting Member
4 Posts |
Posted - 2009-03-30 : 18:47:37
|
| That didn't work. Of course I had already set @counter in the line with the query. So it would not be null unless nothing is returned. When I use a legitimate login, it still does not increment. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Update a field