SQL Server, SQL Server Express, and SqlDataSource Control

Re: Check to see if record already exists

ramireddyindia — Sun, 05 Jul 2009 06:00:52 GMT

Thanks Naom, I forgot this point. :)

Re: Check to see if record already exists

Naom — Sun, 05 Jul 2009 02:37:31 GMT

Rami,

Think a bit. You made a check and found that no record exists. Another user made the same check at the same time and inserted the record, now since your check also just returned 0, you also inserted the record and ended up with duplicates. Even if you put the whole logic in one SP, it is not 100% bullet-proof solution in a very busy website. Only using UNIQUE index in addition with the stored procedure will guarantee uniqueness. Therefore, any extra checks like you suggest, become useless.

Re: Check to see if record already exists

ramireddyindia — Sat, 04 Jul 2009 06:20:02 GMT

Naom:
Using Rami's approach would not guarantee uniqueness

why it won't work? when u call that stored proc. by passing the input the user entered, it will returns how many times its in table if its greater than 0, we will give a message that already exists,otherwise, we will insert the record? y this won't work?

Re: Check to see if record already exists

malcolms — Sat, 04 Jul 2009 00:28:51 GMT

A good option is to insert the record, and if it fails, catch that in your C#/VB.NET code. You can put a try/catch/finally block around your code, or set the customErrors attribute in your web.config to catch the error and fail gracefully.

Re: Check to see if record already exists

RickNZ — Sat, 04 Jul 2009 00:06:37 GMT

I agree with Naom, and would add that with an appropriate unique key in place, you should just let the insert fail if there's a duplicate, rather than first checking for one. That saves a round-trip to the DB.

Re: Check to see if record already exists

Naom — Fri, 03 Jul 2009 20:54:24 GMT

Using Rami's approach would not guarantee uniqueness and therefore there is no need to use such SPs at all. If you want to check for uniqueness, you need to do this in SP with

IF NOT EXISTS(select ...)

insert ...

However, IMHO, even this would not guarantee 100% uniqueness, so the best way would be to add a unique index on the table as well.

Re: Check to see if record already exists

PeteNet — Fri, 03 Jul 2009 20:47:31 GMT

It would be efficient to address this into the stored procedure IF you already have one in place; you then can accommodate the stored procedure to return a particular status and status message (as output parameters) which basically would say something to the effect that the record already exists - the advantage would be that if the record does not exist the code, in the procedure, would go ahead and insert like normal and you would have handled both situations nicely (and additional code in the page)

But, if you don't have a stored procedure in place then you could always look for a record (based on whatever are the fields that make it unique) using a simple ExecuteScalar statement with a SqlCommand and only if it does not then go ahead and insert the new record etc
Look at this example here for a simple ExecuteScalar: http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/ExecuteScalarExample.htm , another within an insert function: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx (it would be nice do it like this function as it will then give you a straight-forward integer value returned it the record exists)

Re: Check to see if record already exists

ramireddyindia — Fri, 03 Jul 2009 20:41:22 GMT

Creae a stored proc. like below.

create proc sc_getCount

as

begin

select count(*) from tablename where columnname = @inputvalue

end

then call this stored procedure using either sqlcommand(if you are using layered appraoch in your application, then use them.)

eg: to call using sqlcommand see the below

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
Object returnValue;

cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType. StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

returnValue = cmd.ExecuteScalar();

sqlConnection1.Close();

Re: Check to see if record already exists

Naom — Fri, 03 Jul 2009 20:40:57 GMT

The simplest and best way would be to put this in SP and also have a unique index on the column to ensure no duplicates.

Check to see if record already exists

tcreynolds — Fri, 03 Jul 2009 18:28:27 GMT

I need a way to check to see if a record has already been inputted. If it has, the record should not be reinputted and should raise an error.

I'm wondering whether this would be best done in C#, my stored procedure, or both. This is for a site where the user completes a survey and the information is stored in my database. Once the survey is completed, they should not have any way to modify their input.