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.
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.
/****** Object: Table [dbo].[CITY_MASTER] Script Date: 06/30/2010 12:55:13 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
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 &".")
strSQL ="INSERT INTO...."