Regarding some Update Stored procedure
- by Serenity
I have two Tables as follows:-
Table1:-
-------------------------------------
PageID|Content|TitleID(FK)|LanguageID
-------------------------------------
1 |abc |101 |1
2 |xyz |102 |1
--------------------------------------
Table2:-
-------------------------
TitleID|Title |LanguageID
-------------------------
101 |Title1|1
102 |Title2|1
------------------------
I don't want to add duplicates in my Table1(Content Table). Like..there can be no two Pages with the same Title. What check do I need to add in my Insert/Update Stored Procedure ? How do I make sure duplicates are never added. I have tried as follows:-
CREATE PROC InsertUpdatePageContent
(
@PageID int,
@Content nvarchar(2000),
@TitleID int
)
AS
BEGIN
IF(@PageID=-1)
BEGIN
IF(NOT EXISTS(SELECT TitleID FROM Table1 WHERE LANGUAGEID=@LANGUAGEID))
BEGIN
INSERT INTO Table1(Content,TitleID)
VALUES(@Content,@TitleID)
END
END
ELSE
BEGIN
IF(NOT EXISTS(SELECT TitleID FROM Table1 WHERE LANGUAGEID=@LANGUAGEID))
BEGIN
UPDATE Table1 SET Content=@Content,TitleID=@TitleID WHERE PAGEID=@PAGEID
END
END
END
Now what is happening is that it is inserting new records alright and won't allow duplicates to be added but when I update its giving me problem.
On my aspx Page I have a drop down list control that is bound to DataSource that returns Table 2(Title Table) and I have a text box in which user types Page's content to be stored.
When I update, like lets say I have a row in my Table 1 as shown above with PageID=1.
Now when I am updating this row, like I didn't change the Title from the drop down and only changed Content in the text box, its not updating the record ..and when Stored procedure's Update Query does not execute it displays a Label that says "Page with this title exists already."
So whenever I am updating an existing record that label is displayed on screen.How do I change that IF condition in my Update Stored procedure??
EDIT:-
@gbn :: Will that IF condition work in case of update? I mean lets say I am updating the Page with TitleID=1, I changed its content, then when I update, it's gonna execute that IF condition and it still won't update coz TitleID=1 already exits!It will only update if TitleID=1 is not there in Table1. Isn't it? Guess I am getting confused. Please answer.Thanks.
