Prevent duplicate record insert in database if record exists in ASP.Net using StoredProcedure

Hi akhter,

Check this example. Now please take its reference and correct your code.

SQL

CREATE PROCEDURE CheckRecord
    @IPAddress VARCHAR(20),
    @ETime TIME
AS
BEGIN
      SET NOCOUNT ON;
 
      DECLARE @Exists INT; 
      IF EXISTS(SELECT Codeitem FROM Items WHERE IPAddress = @IPAddress AND ETime = @ETime)
      BEGIN
            SET @Exists = 1
      END
      ELSE
      BEGIN
            SET @Exists = 0
      END
 
      RETURN @Exists
END

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void OnInsert(object sender, EventArgs e)
{
    if (IsExist(lblIP.Text))
    {
        ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('Data already exist.')", true);
    }
    else
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand("spInsertPorderder");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Codeitem", ddpitem.SelectedValue);
            cmd.Parameters.AddWithValue("@IPAddress", lblIP.Text);
            cmd.Parameters.AddWithValue("@ETime", DateTime.Now.ToString("hh:mm:ss"));
            cmd.Parameters.AddWithValue("@Gweigth", txttw.Text);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
        ClientScript.RegisterStartupScript(this.GetType(), "", "alert('Data inserted successfully.')", true);
    }
}

private bool IsExist(string ip)
{
    bool isExist = false;
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        SqlCommand cmd = new SqlCommand("CheckRecord");
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@IPAddress", ip);
        cmd.Parameters.AddWithValue("@ETime", DateTime.Now.ToString("hh:mm:ss"));
        cmd.Connection = con;
        con.Open();
        isExist = Convert.ToBoolean(cmd.ExecuteScalar());
        con.Close();

        return isExist;
    }
}

VB.Net

    Protected Sub OnInsert(ByVal sender As Object, ByVal e As EventArgs)
        If IsExist(lblIP.Text) Then
            ClientScript.RegisterClientScriptBlock(Me.[GetType](), "", "alert('Data already exist.')", True)
        Else
            Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

            Using con As SqlConnection = New SqlConnection(conString)
                Dim cmd As SqlCommand = New SqlCommand("spInsertPorderder")
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@Codeitem", ddpitem.SelectedValue)
                cmd.Parameters.AddWithValue("@IPAddress", lblIP.Text)
                cmd.Parameters.AddWithValue("@ETime", DateTime.Now.ToString("hh:mm:ss"))
                cmd.Parameters.AddWithValue("@Gweigth", txttw.Text)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using

            ClientScript.RegisterStartupScript(Me.GetType(), "", "alert('Data inserted successfully.')", True)
        End If
    End Sub

    Private Function IsExist(ByVal ip As String) As Boolean
        Dim isExist As Boolean = False
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

        Using con As SqlConnection = New SqlConnection(conString)
            Dim cmd As SqlCommand = New SqlCommand("CheckRecord")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@IPAddress", ip)
            cmd.Parameters.AddWithValue("@ETime", DateTime.Now.ToString("hh:mm:ss"))
            cmd.Connection = con
            con.Open()
            isExist = Convert.ToBoolean(cmd.ExecuteScalar())
            con.Close()
            Return isExist
        End Using
    End Function