Loading ...

Prevent Duplicate Value in a Table | CodeAsp.Net

Prevent Duplicate Value in a Table

 /5
0 (0votes)

In this blog I will explain how to prevent duplicate value in a table.
I have a Student form. In this form user can insert student’s details. But duplicate value in
Roll No field isn’t allowed.  In below example I show you how you can stop duplicate value in a field.

Here is my code:-

In this StoredProcedure I am checking Roll no, if it is already exist then it won’t save in database.

 

CREATE PROCEDURE [Test_Insert]
	@StudentID INT OUTPUT,
	@StudentName NVARCHAR(100),
	@RollNo INT,
	@FatherName NVARCHAR(100),
	@MotherName NVARCHAR(100)
AS
	IF NOT EXISTS(SELECT 1 FROM [StudentInfo] WHERE [RollNo] = @RollNo)
Begin

	INSERT INTO [StudentInfo]	
	(StudentName,RollNo,FatherName,MotherName) 
	VALUES
	(@StudentName,@RollNo,@FatherName,@MotherName)	
	
	SET @StudentID = SCOPE_IDENTITY()
	
END	

ELSE

BEGIN

	SET @StudentID = 0

END

There are four Textbox, one Button and one Label. First user fills the textboxes and press Submit button.
When user press submit button code behind class file for the page invokes the btnSubmit_Click method.
In btnSubmit_Click method I am checking true or false if it’s true data is inserted successfully and
if it’s false duplicate value will not be posted.

 

 

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>StudentName: </td>
                <td><asp:TextBox ID="txtStudentName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Student RollNo: </td>
                <td><asp:TextBox ID="txtRollNo" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Father Name: </td>
                <td><asp:TextBox ID="txtFatherName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Mother Name: </td>
                <td><asp:TextBox ID="txtMotherName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td colspan="2"><asp:Button ID="btnSubmit" runat="server" Text="Submit" 
                        onclick="btnSubmit_Click" /></td>
            </tr>
            <tr>
                <td><asp:Label ID="lblResult" runat="server"></asp:Label></td>
            </tr>
           
        </table>
    </div>
    </form>
</body>
</html>

 

using System;
using System.Collections.Generic;
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 PreventDuplicateValueInATable : System.Web.UI.Page
{
    private int _studentID;

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        if (InsertData())
            lblResult.Text = "You have sucessfully inserted data";
        else
            lblResult.Text = "You can't insert duplicate value in Rollno Textbox";
    }
    private bool InsertData()
    {
        
        string cmdText = "Test_Insert";
        string connectionString = "conString";

        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(cmdText,con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter paramStudentID = new SqlParameter("@StudentID",SqlDbType.Int);
        paramStudentID.Value = _studentID;
        paramStudentID.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(paramStudentID);

        SqlParameter paramStudnetName = new SqlParameter("@StudentName",SqlDbType.NVarChar);
        paramStudnetName.Value = txtStudentName.Text;
        cmd.Parameters.Add(paramStudnetName);

        SqlParameter paramRollNo = new SqlParameter("@RollNo",SqlDbType.Int);
        paramRollNo.Value = Convert.ToInt32(txtRollNo.Text);
        cmd.Parameters.Add(paramRollNo);

        SqlParameter paramFatherName = new SqlParameter("@FatherName",SqlDbType.NVarChar);
        paramFatherName.Value = txtFatherName.Text;
        cmd.Parameters.Add(paramFatherName);

        SqlParameter paramMotherName = new SqlParameter("@MotherName",SqlDbType.NVarChar);
        paramMotherName.Value = txtMotherName.Text;
        cmd.Parameters.Add(paramMotherName);

        con.Open();
        cmd.ExecuteNonQuery();

        _studentID = (int)paramStudentID.Value;
        return _studentID > 0 ? true : false;  
    }
    
}

With the help of this you can Prevent Duplicate Value in a Table:)

Comments (1)

   
buy dissertations

I have read so many articles to prevent duplicate values of microsoft blogs. Then we are all happy to write instructions and essay concepts. So they are all happy to watch your online resources and essays.

5/5/2017
 · 
by
  • :*
  • :*
  • :
 *

Top Posts