Insert record into database if doesn't exist already in ASP.Net using Stored Procedure

Hi ramco1917,

There is no need to make use of Try Catch statement to check whether record exists or not.

You need to make use of EXISTS operator for checking whether record exists or not. If not exists then insert the record in database.

The EXISTS operator returns TRUE if the subquery returns one or more records.

Refer below example. For that i have created an Stored Procedure.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

Stored Procedure

CREATE PROCEDURE Customers_InsertCustomerNotExists
    @CustomerId INT, 
    @Name VARCHAR(50), 
    @Country VARCHAR(50)
AS
BEGIN
    IF NOT EXISTS (SELECT CustomerId FROM Customers WHERE CustomerId = @CustomerId) 
    BEGIN 
        INSERT INTO Customers VALUES (@Name, @Country)
    END
END

HTML

<table>
    <tr>
        <td>Id:</td>
        <td><asp:TextBox ID="txtId" runat="server" /></td>
    </tr>
    <tr>
        <td>Name:</td>
        <td><asp:TextBox ID="txtName" runat="server" /></td>
    </tr>
    <tr>
        <td>Country:</td>
        <td><asp:TextBox ID="txtCountry" runat="server" /></td>
    </tr>
    <tr>
        <td></td>
        <td><asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="OnSubmit" /></td>
    </tr>
</table>

Namespaces

You need to import the following 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

Inside the Button click event handler, the stored procedure is executed and check whether record exists or not.

Based on that appropriate message is displayed in JavaScript Alert Message Box.

C#

protected void OnSubmit(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "Customers_InsertCustomerNotExists";
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CustomerId", txtId.Text);
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if (i > 0)
            {
                ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Record inserted successfully.');", true);
            }
            else
            {
                ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Record already present.');", true);
            }
        }
    }
}

VB.Net

Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim query As String = "Customers_InsertCustomerNotExists"
        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@CustomerId", txtId.Text)
            cmd.Parameters.AddWithValue("@Name", txtName.Text)
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text)
            con.Open()
            Dim i As Integer = cmd.ExecuteNonQuery()
            con.Close()
            If i > 0 Then
                ClientScript.RegisterClientScriptBlock(Me.GetType(), "alert", "alert('Record inserted successfully.');", True)
            Else
                ClientScript.RegisterClientScriptBlock(Me.GetType(), "alert", "alert('Record already present.');", True)
            End If
        End Using
    End Using
End Sub

Screenshot