In this article I will explain with an example, how to prevent (avoid) duplicate (double) record insert into SQL Server Database in ASP.Net using C# and VB.Net.
Database
I have made use of the following table Customers with the schema as follows.
![Prevent (Avoid) Duplicate (Double) record insert into Database in ASP.Net]()
I have already inserted few records in the table.
![Prevent (Avoid) Duplicate (Double) record insert into Database in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The following HTML Markup consists of two TextBoxes, a Button and a GridView control with two BoundField columns.
The Button has been assigned with an OnClick event handler.
<table>
<tr>
<td>Name:</td>
<td><asp:TextBox runat="server" ID="txtName" /></td>
</tr>
<tr>
<td>Country:</td>
<td><asp:TextBox runat="server" ID="txtCountry" /></td>
</tr>
<tr>
<td><asp:Button ID="btnSave" Text="Add" runat="server" OnClick="Insert" /></td>
</tr>
</table>
<br/>
<asp:GridView runat="server" ID="gvCustomers" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Populating the GridView
Inside the Page Load event, the BindGrid function is called which populates the GridView from Customers table of SQL Server database.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
string conn = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conn))
{
using (SqlCommand cmd = new SqlCommand("SELECT Name, Country FROM Customers", con))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
gvCustomers.DataSource = dt;
gvCustomers.DataBind();
}
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim conn As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(conn)
Using cmd As SqlCommand = New SqlCommand("SELECT Name, Country FROM Customers", con)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Using dt As DataTable = New DataTable()
sda.Fill(dt)
gvCustomers.DataSource = dt
gvCustomers.DataBind()
End Using
End Using
End Using
End Using
End Sub
Inserting records to database
When Add Button is clicked, first an SQL query is built using String Concatenation method.
Note: The SQL query consists of a condition which makes sure that the data being inserted does not exists in the Table. If the data does not exists then only the record will be inserted.
Then the values of the Name and Country TextBoxes are fetched from their respective TextBoxes and passed as parameters to the SQL query and the query is executed.
The ExecuteNonQuery function returns the count of Rows Affected during the operation and the value is captured in recordsInserted variable. This count will be Greater than 1 when an INSERT operation is performed and it will be -1 when the INSERT operation is not performed.
Finally, the GridView is populated from Database and if the INSERT operation is not performed i.e. value of recordsInserted variable is -1, then a message conveying ‘Duplicate Record’ is displayed using a JavaScript Alert Message Box.
C#
protected void Insert(object sender, EventArgs e)
{
int recordsInserted = 0;
string sql = "IF NOT EXISTS(SELECT CustomerId FROM Customers WHERE Name = @Name and Country = @Country) ";
sql += "BEGIN ";
sql += "INSERT INTO Customers (Name, Country) VALUES(@Name, @Country) ";
sql += "END";
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
con.Open();
recordsInserted = cmd.ExecuteNonQuery();
con.Close();
}
}
this.BindGrid();
if (recordsInserted == -1)
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Duplicate record. Please insert Unique data.');", true);
}
}
VB.Net
Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
Dim recordsInserted As Integer = 0
Dim sql As String = "IF NOT EXISTS(SELECT CustomerId FROM Customers WHERE Name = @Name and Country = @Country) "
sql += "BEGIN "
sql += "INSERT INTO Customers (Name, Country) VALUES(@Name, @Country) "
sql += "END"
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(conString)
Using cmd As SqlCommand = New SqlCommand(sql)
cmd.Connection = con
cmd.Parameters.AddWithValue("@Name", txtName.Text)
cmd.Parameters.AddWithValue("@Country", txtCountry.Text)
con.Open()
recordsInserted = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Me.BindGrid()
If recordsInserted = -1 Then
ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Duplicate record. Please insert Unique data.');", True)
End If
End Sub
Screenshot
![Prevent (Avoid) Duplicate (Double) record insert into Database in ASP.Net]()
Downloads
