I want to insert number for City_Id and City_name , i wrote a query for auto generate number for City_Id,working fine but in the city name i want to avoid inserting duplicate value like if Mumbai is a City ihave already saved then system should not allow that name again. neither in small or in capital or mixed letter.

Can anybody help me..

Thanks In Advance
Archana

Thanks for your reply.

I have tried to do so but It is not allowing me to do two primary key in that table .City_id i have set as a primary key.

And If cities are in lower and upper case then where should i write some constraints.

Acctually i am new to dotnet,so please guide me.
this is my script.
USE [icms]
GO
/****** Object: Table [dbo].[CITY_MASTER] Script Date: 06/30/2010 12:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CITY_MASTER](
[CITY_ID] [int] IDENTITY(1,1) NOT NULL,
[CITY_NAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_CITY_MASTER] PRIMARY KEY CLUSTERED
(
[CITY_ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Hi,

What I would do is before your are going to Insert your new city record check for matching records first, if it is clear insert, if not then query the user:

strSQL="SELECT COUNT(*) FROM CITY_MASTER WHERE LOWER(CITY_NAME) = '" &lcase(MyCity) &"'"
.....
If CityCount > 0 then
  msgbox ("Entry already exists for " &MyCity &".")
Else

 strSQL ="INSERT INTO...."

End if
This question has already been answered. Start a new discussion instead.