Prevent Duplicate Entry while update

[eraser950]

Helllo I have a problem while updating data in Sql table, i have a table named City it contains CityID,CityName,CityShortName i have created a stored procedure PROCEDURE [dbo].[UpdateCity] @CityID int,@CityName nvarchar(50),@CityShortName nvarchar(50) AS BEGIN Declare @ReturnVal nvarchar(20) SET NOCOUNT ON; SET XACT_ABORT ON Begin Try IF EXISTS (SELECT CityName From City WHERE CityName =@CityName) BEGIN SET @ReturnVal ='Already Exists!' --Return @ReturnVal END ELSE BEGIN Update City Set CityName=@CityName,CityShortName=@CityShortName where CityID=@CityID Set @ReturnVal ='Updated SuccessFully' --Return @ReturnVal End end try but when i m just editing Shortname its not updating , but its working perfect when i m editing either cityname or cityname and shortname,, i know that i m doing mistake but where i can't found second thng how can i show message updated successfully or already exist as i m using asp gridview Please help me Thanx

[Bernhard Hiller]

Here is the source of your problem:

eraser950 wrote:

IF EXISTS (SELECT CityName From City WHERE CityName =@CityName) BEGIN SET @ReturnVal ='Already Exists!' --Return @ReturnVal END

When you do not change the city name, that if clause returns true, and hence you return 'Already Exists!'. Try:

IF EXISTS (SELECT CityName From City WHERE CityName =@CityName AND CityID<>@CityID)

[eraser950]

thnx i will try this

[eraser950]

Thnx its working perfect kindly tell me is this good approach or theres any other method to do this? second how can show message as i m usng sqldatasource(GridView) to update and delete