Validation to Check Duplicate Records

Prevent from inserting duplicate records into the table

You need to check if a duplicate entry exists in table before inserting the data. I suggest you could refer to the solution given below:

Approach 1=>

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultCSRConnection"].ConnectionString))
            {
                //Open your connection
                conn.Open();

                //Your Select Query
                //Change this as per your design and provide the primary key field here
                string selectString = "SELECT COUNT(*) FROM tbl_students WHERE YourIDField = @idfield";

                //Create Command object
                SqlCommand myCommand = new SqlCommand(selectString, conn);

                //Pass your parameter here
                myCommand.Parameters.AddWithValue("@id", yourexistingidvalue);

                // Get the Result query
                var idExists = (Int32)myCommand.ExecuteScalar() > 0;

                //Check if record exists in table
                if (!idExists)
                {
                    //Insert the Record
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        SqlCommand cmd1 = new SqlCommand("Insert into tbl_students (first_name,last_name,sex,dob,active) values(@first_name, @last_name,@sex,@dob,@active)", conn);
                        cmd1.Parameters.Add("@first_name", SqlDbType.NVarChar).Value = txtFname.Text;
                        cmd1.Parameters.Add("@last_name", SqlDbType.NVarChar).Value = txtLname.Text;
                        cmd1.Parameters.Add("@sex", SqlDbType.NVarChar).Value = ddlgender.SelectedValue;
                        cmd1.Parameters.Add("@dob", SqlDbType.DateTime).Value = txtDateofBirth.Text;
                        cmd1.Parameters.Add("@active", SqlDbType.Bit).Value = Convert.ToInt32(ddlActiveInactive.SelectedValue);
                        cmd1.ExecuteNonQuery();
                        conn.Close();
                        ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('page added sucessfully');window.location ='csrstudentprofile.aspx';", true);
                    }
                }
            }

Approach 2=>

CREATE PROC ValidateAndInsertStudent
(
@first_name VARCHAR(50),
@last_name VARCHAR(50),
@sex VARCHAR(50),
@dob DateTime,
@active BIT,
@Done BIT,
@Msg VARCHAR(200)
)
AS 
BEGIN
 SET @Done = 1,
 SET @Msg = ''
 IF (SELECT COUNT(*) FROM tbl_students WHERE first_name=@first_name AND last_name=@last_name AND sex=@sex AND dob=@dob AND active=@active)>0
 BEGIN
  SET @Done = 0
  SET @Msg = 'Record exists'
  RETURN;
 END
 Insert into tbl_students (first_name,last_name,sex,dob,active) values(@first_name, @last_name,@sex,@dob,@active)
END

Approach 3=> Using Output Parameter

CREATE PROCEDURE [dbo].[InsertsFruitName]

      @FruitId INT,

      @Exists VARCHAR(30) OUTPUT,

      @Details VARCHAR(300) 

AS

BEGIN

      SET NOCOUNT ON;

      IF EXISTS (SELECT TOP 1 FROM Fruits WHERE FruitId = @FruitId)

      BEGIN

      SELECT @Exists = 'EXISTS' 

     END

     ELSE

     BEGIN

         SELECT @Exists = '' 

         INSERT INTO Fruits (Details) VALUES                                              (@Details) WHERE FruitId = @FruitId

     END

END

Return Output parameter from Stored Procedure in ASP.Net

protected void Submit(object sender, EventArgs e)

{

    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constring))

    {insert

        using (SqlCommand cmd = new SqlCommand("InsertsFruitName", con))

        {

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@FruitId", int.Parse(txtFruitId.Text.Trim()));

cmd.Parameters.AddWithValue("@Details", int.Parse(txtFruitDetails.Text.Trim()));

            cmd.Parameters.Add("@Exists", SqlDbType.VarChar, 30);

            cmd.Parameters["@Exists"].Direction = ParameterDirection.Output;

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

IF(cmd.Parameters["@Exists"].Value.ToString() == "")

{

            lblFruitName.Text = "Fruit Details Added Successfully."';

}

else

{

            lblFruitName.Text = "Fruits Details Already Exists";

}

        }

    }

}

Read More »