if not exists inserting data

[christer99]

This insert statement keeps inserting data although there is already an identical record.

If Not Exists(SELECT * From dbo.Contact Where (ACCREC='"&recval&"') and (FIRSTNAME='"&tmfirstname&"') and (LASTNAME='"&tmfirstname&"')) INSERT INTO dbo.CONTACT (ACCREC,FIRSTNAME,LASTNAME,CONTACTEMAIL,WORKPHONE) VALUES ('"&recval&"','"& tmfirstname&"','"&tmlastname&"','"&emailaddress&"','"&phonenumdigits&"')

It does work when I change to only check for the ACCREC (and not the name) and if I change the select statement to select 1, but it doesn't work once I added the FIRSTNAME and the LASTNAME to the criteria. The same ACCREC can have multiple people, and thus only checking for the ACCREC is no good. I appreciate if anyone has the answer to this problem.

 

[mrdenny]

Case sensivitity, trailing spaces, leading spaces, extra characters in the name all come to mind.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Learnerprog]

Hello All,
Is there a way to ignore record with invalid dates in SQL queries because I am keep getting this error below
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

Thank you very much in advance.

 

[vongrunt]

Plz. open a new thread first, not reply to existing one...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)