how to avoid inserting duplicate records from asp to sql ?

Question

4.00/5 (2 votes)

See more:

SQL-Server-2005

, +

hi i created stored procedure to avoid duplicate entries but i dont knw how to give code in business class..can any one suggest me.

this my sp

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER PROCEDURE [dbo].[usp_InsertBuild]
@BuildNo varchar(50),
@StartDate varchar(50),
@EndDate varchar(50),
 @Success bit output, 
@msg varchar(50) output  


AS
BEGIN
if not exists(select * from tblBuild where BuildNo=@BuildNo) 
begin
set @Success=1
set @msg='Successfully Saved'      



insert into tblBuild(BuildNo,StartDate,EndDate)
 values(@BuildNo,@StartDate,@EndDate)
end
else  begin
set @Success=0           
set @msg='Build Number already exists, Cannot Save' 
END end

here is my aspx.cs code..

protected void imgbtnSave_Click(object sender, ImageClickEventArgs e)
   {
       InsertAllPL iaPL = new InsertAllPL();
       iaPL.BuildNo = txtBNo.Text;
       iaPL.StartDate = txtSDate.Text;
       iaPL.EndDate = txtEndDate.Text;

       InsertAllBLL iaBLL = new InsertAllBLL();
       iaBLL.InsertBuild(iaPL);

       //lblMessage.Text = "Record Added SuccessFully";
       divMessage.Visible = true;
       txtBNo.Text = "";
       txtSDate.Text = "";
       txtEndDate.Text = "";
   }

Posted 8-Mar-12 18:00pm

ythisbug

Add a Solution

Comments

Sergey Alexandrovich Kryukov 9-Mar-12 0:58am

What do you mean by duplicate, exactly? Totally identical record except primary key?
Database won't allow you the 100% exact duplicate...
--SA

ythisbug 9-Mar-12 1:15am

ya identical.like i have build number for example 10.2.3 i dont want to repeat this in my table so .any suggestion

Sergey Alexandrovich Kryukov 9-Mar-12 1:18am

You could make version a primary key, then the database won't allow to duplicate it. This would be the best way.
--SA

ythisbug 9-Mar-12 1:23am

can u telmme how to do that.?

Sergey Alexandrovich Kryukov 9-Mar-12 1:33am

To do what? Change database structure and code. My advice does not work in all cases though. Primary keys are designed to be unique by definition.
--SA

ythisbug 9-Mar-12 1:39am

ok thanks

Sergey Alexandrovich Kryukov 9-Mar-12 2:54am

You are welcome.
--SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nilesh Patil Kolhapur · Answer 1 · 2012-03-08T18:16:00

hi,
u just assign output parameter value to lblMessage using following code

C#

string msg =sqlCmd.Parameters["@Success"].Value;
    return msg;

check in btnSaveclick event

C#

if(msg=="1")
     lblMessage.Text="Successfully Saved";
   else
     lblMessage.Text="Build Number already exists, Cannot Save";

hope it will help
best luck

Ganesan Senthilvel · Answer 2 · 2012-03-08T18:21:00

You can use powerful LInQ query in business class. Here is the sample code to fetch the duplicate records before insert.

C#

var dbData = from x in db.tblBuild
                       where x.Sucess != 0
                       group x by x.BuildNo into g
                       let ct = g.Count()
                       orderby ct
                       where ct > 1
                       select new
                            {   
                                BuildNo = g.First().BuildNo,
                                StartDate = g.First().StartDate,
                                EndDate = g.First().EndDate,
                                Success = g.First().Success,
                                msg = g.First().msg,
                            };

how to avoid inserting duplicate records from asp to sql ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0