SQL table accepting same names again and again database name checking

I am trying to insert the guestpass type name in table guestpasstypes and at a time it will check the database whether the database has already that name or not by using this statement:

@"INSERT INTO guestpasstypes(guestPasstype_Name)values('" + tbPassType.Text + "') where not exists (select 'guestPasstype_Name' from guestpasstypes where guestPasstype_Name = '" + tbPassType.Text + "')"

but 开发者_如何学JAVAit accepts the duplicate name too, and it does not work. Would anyone please help on this?

For SQL Server it would look like this.

insert into guestpasstypes (guestPasstype_Name)
select 'name1'
where not exists (select *
                  from guestpasstypes
                  where guestPasstype_Name = 'name1')

I think it should work for MySQL as well.

If you are on SQL Server 2008 you can use MERGE.

merge guestpasstypes as G
using (select 'name2') as S(Name)
on G.guestPasstype_Name = S.Name
when not matched then
  insert (guestPasstype_Name) values (Name);

UPDATE

I think the first option could be applied to your problem like this:

@"INSERT INTO guestpasstypes(guestPasstype_Name) select '" + tbPassType.Text
  + "' where not exists (select * from guestpasstypes where guestPasstype_Name = '"
  + tbPassType.Text + "')"

If you want it to throw an error you can either :

• Put a unique index on the column (the easiest and preferred way)

or

• Write a stored procedure which returns an error flag. Within the procedure, you first check for a matching value and if one is found, set the error flag and return. Otherwise do the insert as normal.

Try either INSERT IGNORE or INSERT ON DUPLICATE KEY:

INSERT IGNORE INTO `guestpasstypes`(`guestPasstype_Name`) values('" + tbPassType.Text + "');

OR

INSERT INTO `guestpasstypes`(`guestPasstype_Name`)values('" + tbPassType.Text + "') ON DUPLICATE KEY UPDATE `guestPasstype_Name` = `guestPasstype_Name`;