Using if exists

Question

I'm having an issue with duplicates being inserted on refresh. Our team here decided using 'if exists' in sql is the best way to stop duplicate inserts. However, what if a parameter is set to null?

string cmdText = " if (not exists(select * from table where field1 = @field1 and field2 = @field2 and field3 = @field3)) Insert into table(field1,field2,field3) Values(@field1,@field2,@field3)";

if (txtfield1.text != "")
    cmd.Parameters.Add(new SqlParameter("@field1", txtfield1.text));
else
    cmd.Parameters.Add(new SqlParameter("@ field1", DBNull.Value));

    cmd.Parameters.Add(new SqlParameter("@field2", txtfield2));
    cmd.Parameters.Add(new SqlParameter("@field3", txtfield3));

This does not work when there is a null value in field1.

Answer 1

Well you would have to use IS instead of = if you are going to set @field1 to NULL. NULL is not gonig to work with the equality operator. You need to use an equivalence type 'IS' for that.

string cmdText = " if (not exists(select * from table where field1 = @field1 and field2 = @field2 and field3 = @field3)) Insert into table(field1,field2,field3) Values(@field1,@field2,@field3)";

goes to

string cmdText = "";
if (txtfield1.text != "") 
cmdText = " if (not exists(select * from table where field1 = @field1 and field2 = @field2 and field3 = @field3)) Insert into table(field1,field2,field3) Values(@field1,@field2,@field3)";
else
cmdText = " if (not exists(select * from table where field1 IS @field1 and field2 = @field2 and field3 = @field3)) Insert into table(field1,field2,field3) Values(@field1,@field2,@field3)";
end if

Rinse and repeat and refactor :)

Answer 2

You could wrap your fields around isnull's or something to take into account for the nulls

isnull(field1, '') = isnull(@field1, '') and 
isnull(field2, '') = isnull(@field2, '') and 
isnull(field3, '') = isnull(@field, '')

Answer 3

where field1 IS @field1

is not valid syntax

Use isnull()

So:

string cmdText = " if (not exists(select * from table where isnull(field1, '') = isnull(@field1, '') ...

Answer 4

If you don't want duplicates in your table, maybe your table should have a primary key or at least a unique clustered index on your field1, field2 and field3.

That way, you could just try ton insert and catch the error if the row already exists.

Answer 5

Would it not be a better idea to reduce the load on the database and attack the problem at the source?

Usually this kind of error occurs when you have a page that handles its own submission, ie the form action property points back to itself, so when someone hits refresh after they posted something, the POST data is still 'live' and gets posted back to the page.

A better way is to have the data submitted to a second object which deals with insertion and then redirects back to where it came from, the redirection clears the POST data and you save yourself a LOT of unnecessary queries.

Just my 2c

Answer 6

following condition will help.

field1 is null or @field1 = field1

Answer 7

In field definition you can use NOT NULL constraint so that null element's wouldn't be able to be there.

http://www.w3schools.com/SQl/sql%5Fnotnull.asp

And use sql UNIQUE constraint so they have to be unique.

http://www.w3schools.com/SQl/sql%5Funique.asp

 CREATE TABLE YourTable
 (
   Field1 varchar(255),
   Field2 varchar(255),
   Field3 varchar(255),
   CONSTRAINT uc_fields UNIQUE (Field1, Field2, Field3)
 )

 CREATE TRIGGER table_null_convert
   ON YourTable
   FOR INSERT, UPDATE 
   REFERENCING NEW ROW AS n
   FOR EACH ROW
     SET n.Field1 = ISNULL(n.Field1, '')
     SET n.Field2 = ISNULL(n.Field2, '')
     SET n.Field3 = ISNULL(n.Field3, '');

And you'll be allowed to insert if these conditions are met.

Hope I got the trigger right. :)