| Author |
Topic |
|
davidshq
Posting Yak Master
119 Posts |
 Posted - 2006-05-23 : 22:32:11
|
| I've created a feedback system. When an item is accepted it is placed into a temporary holding table. People can go to a Feedback page and leave feedback on the items they were involved in. This creates a permanent row in another table. My problem is that its supposed to set one of the columns in the temporary table to 1 after feedback has been left by either the offerer or recipient, but it isn't. Its adding the feedback to the permanent table, but not updating the status. Thus one can continue giving feedback forever, the temporary row is never deleted, etc. Here is my stored procedure:ALTER PROCEDURE dbo.LeaveFeedback ( @varUser varchar(max), @varID int, @varFeedback text, @varFeedbackLeftFor varchar(max), @varPositiveOrNegative int, @varOfferer varchar(max), @varIsRCom int, @varIsOCom int ) AS /* SET NOCOUNT ON */ INSERT INTO UserFeedback (UserName, Feedback, PosNeg, Item, LeftBy) VALUES (@varFeedbackLeftFor, @varFeedback, @varPositiveOrNegative, @varID, @varUser) SET @varOfferer=(select offerer from AvailableFeedback where Item=@varID) If @varOfferer=@varUser BEGIN UPDATE AvailableFeedback set OffererComplete=1 where Item=@varID END Else BEGIN UPDATE AvailableFeedback set RequestorComplete=1 where Item=@varID END SET @varIsRCom=(select RequestorComplete from AvailableFeedback where Item=@varID) SET @varIsOCom=(select OffererComplete from AvailableFeedback where Item=@varID) If @varISOCom=1 AND @varISRCom=1 BEGIN DELETE FROM AvailableFeedback where Item=@varID END RETURN- http://www.gamesecretary.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
davidshq
Posting Yak Master
119 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-05-24 : 10:10:21
|
| OK, a couple questions:Why are you using varchar(max) for @varUser and @varOfferer?Why are you accepting @varOfferer as a parameter and then resetting it without using it? Likewise with @varIsRCom and @varIsOCom.Try putting flags in your code to determine how far it is getting and what the variable values are at any particular point. |
 |
|
|
davidshq
Posting Yak Master
119 Posts |
Posted - 2006-05-24 : 22:20:45
|
| Most of these variables do have values, they are passed from an ASP.NET 2.0 application into the stored procedure.How would I add flags that would allow me to monitor how far the procedure is progressing? I thought maybe I was just doing something simple wrong - like having too many select statements in a single procedure?- http://www.gamesecretary.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-24 : 23:01:07
|
few things you may want to verify:does this provide you always with one value from the query?quote:
SET @varOfferer=(select offerer from AvailableFeedback where Item=@varID)
in your previous query, you set the values conditionally, so only one query inside the if block will be executed... so no deletes unless both are truequote:
If @varISOCom=1 AND @varISRCom=1BEGINDELETE FROM AvailableFeedback where Item=@varIDEND
--------------------keeping it simple... |
|
|
|
davidshq
Posting Yak Master
119 Posts |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-24 : 23:54:21
|
ok, now you need to verify the values as they are passed onto sql server from your asp apps... trace the conditions especially for the update/deletealso what's the RETURN for?  --------------------keeping it simple... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-25 : 00:12:21
|
I am a bit confused. Maybe it is just me.quote:
I've created a feedback system. When an item is accepted it is placed into a temporary holding table. People can go to a Feedback page and leave feedback on the items they were involved in. This creates a permanent row in another table. My problem is that its supposed to set one of the columns in the temporary table to 1 after feedback has been left by either the offerer or recipient, but it isn't. Its adding the feedback to the permanent table, but not updating the status. Thus one can continue giving feedback forever, the temporary row is never deleted, etc
1. What is the name of the temporary holding table ? Is it UserFeedback ?2. The another table is AvailableFeedback ?3. What is the name of the permanent table ?4. When is this SP called ?5. How did record get inserted into table AvailableFeedback ?
KH |
|
|
|
davidshq
Posting Yak Master
119 Posts |
Posted - 2006-05-26 : 02:31:44
|
| The temporary holding table is AvailableFeedback. The permanent table is UserFeedback. The another table is the same table as the permanent table (user Feedback). This SP is called when a user leaves feedback. The record got inserted into available feedback when a user requested the item.How do I perform traces?Basically, the flow is like this:1. Joe offers an item.2. Jane requests item. a. A row is created in AvailableFeedback.3. Jane visits the "My Feedback" page which queries the AvailableFeedback table for rows with her username as either the offerer or receiver.4. Jane rates Joe and his item. a. The feedback is applied to the permanent table (UserFeedback). b. The AvailableFeedback table is updated to note that Jane left feedback and shouldn't be allowed to again. c. If both Jane and Joe have left feedback, the row is deleted as there is no longer a need for it - neither of them can leave additional feedback on the item. Only steps 3-4 are covered in this stored procedure. Adding the feedback to the permanent table works fine, but not noting that Jane left feedback or checking to see whether both Jane and Joe have left feedback.- http://www.gamesecretary.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
|
|
Feedback System.