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.

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.

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();

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)

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.

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.

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.

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?

 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.