how to check duplicate record before insert in asp.net
In this article I will explain how to check duplicate record before insert in asp.net using Stored Procedure
Step1 - Create table and Stored Procedure
GO
/****** Object: Table [dbo].[tbl_Login] Script Date: 7/5/2016 10:56:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Login](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[Password] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_Login] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into tbl_Login (Username,Password)values('admin',123456)
select * from tbl_Login
CREATE PROCEDURE Insertintologin
@Username varchar(100),
@Password varchar(100)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tbl_Login WHERE Username = @Username)
BEGIN
SELECT 'FALSE'
END
ELSE
BEGIN
INSERT INTO tbl_Login(Username, Password)
VALUES(@Username, @Password)
SELECT 'TRUE'
END
END
GO
Step 2- Add aspx page with two textbox one login button and label
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtusername" placeholder="username" runat="server"></asp:TextBox>
<br />
<asp:TextBox ID="txtpassword" placeholder="password" TextMode="Password" runat="server"></asp:TextBox>
<br />
<asp:Button ID="btnlogin" runat="server" Text="Login" OnClick="btnlogin_Click" />
<br />
<asp:Label ID="lblmassage" runat="server" Text="" ></asp:Label>
</div>
</form>
</body>
</html>
Step1 - Create table and Stored Procedure
GO
/****** Object: Table [dbo].[tbl_Login] Script Date: 7/5/2016 10:56:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Login](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[Password] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_Login] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into tbl_Login (Username,Password)values('admin',123456)
select * from tbl_Login
CREATE PROCEDURE Insertintologin
@Username varchar(100),
@Password varchar(100)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tbl_Login WHERE Username = @Username)
BEGIN
SELECT 'FALSE'
END
ELSE
BEGIN
INSERT INTO tbl_Login(Username, Password)
VALUES(@Username, @Password)
SELECT 'TRUE'
END
END
GO
Step 2- Add aspx page with two textbox one login button and label
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtusername" placeholder="username" runat="server"></asp:TextBox>
<br />
<asp:TextBox ID="txtpassword" placeholder="password" TextMode="Password" runat="server"></asp:TextBox>
<br />
<asp:Button ID="btnlogin" runat="server" Text="Login" OnClick="btnlogin_Click" />
<br />
<asp:Label ID="lblmassage" runat="server" Text="" ></asp:Label>
</div>
</form>
</body>
</html>
Step3- Add connection String in webconfig
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=DESKTOP-TGH3GB7;Integrated Security=true;Initial Catalog=sample"/>
</connectionStrings>
Step4- Add below code on code behind .
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnlogin_Click(object sender, EventArgs e)
{
string strConnString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand("Insertintologin"))
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", txtusername.Text.Trim());
cmd.Parameters.AddWithValue("@Password", txtpassword.Text.Trim());
con.Open();
bool check = Convert.ToBoolean(cmd.ExecuteScalar());
if (check)
{
txtusername.Text = string.Empty;
txtpassword.Text = string.Empty;
lblmassage.Text = "Data inserted Successfully!";
}
else
{
txtusername.Text = string.Empty;
txtpassword.Text = string.Empty;
lblmassage.Text = "Username exists!";
}
con.Close();
}
}
}
}
Snapshot
Comments
Post a Comment