CODEWITHSUNDEEP
c#asp.netsqlsql-serversql-insert
mitsu
mitsu

Reputation: 430

Insert in a table which has already id inserted

I have a table 'Agent' its ID is already inserted before and displayed in a textbox for using it in my insertion , now I try just to add the rest of records but in couldn't do that an error is displayed ...(I'm working in asp with c#) and SQL SERVER :

Violation of PRIMARY KEY constraint 'PK__Agent. "Can not insert duplicate key in object 'dbo.Agent. "The duplicate key value is (1). The statement has been terminated.

this my code behind : 

protected void Button_validerinfo_Click(object sender, EventArgs e)
{
    try
    {
        c.cmd = c.cn.CreateCommand();
        c.cmd.CommandText = "AjouterAgent";
        c.cmd.CommandType = CommandType.StoredProcedure;
        if (c.cn.State == ConnectionState.Closed)
        {
            c.cn.Open();
        }

        c.cmd.Parameters.Add("@ppr", SqlDbType.Int);
        c.cmd.Parameters.Add("@lieu", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@adresspro", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@adressperso", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@telbureau", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@telgsm", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@email", SqlDbType.VarChar);

        c.cmd.Parameters.Add("@np", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@proff", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@empl", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@retraite", SqlDbType.VarChar);
        c.cmd.Parameters.Add("@TypeOperation", SqlDbType.Int);

        c.cmd.Parameters["@ppr"].Value = TextBox_PPR.Text;
        c.cmd.Parameters["@lieu"].Value = TextBox_ln.Text;
        c.cmd.Parameters["@adresspro"].Value = TextBox_adrspro.Text;
        c.cmd.Parameters["@adressperso"].Value = TextBox_adrssperso.Text;
        c.cmd.Parameters["@telbureau"].Value = TextBox_bureau.Text;
        c.cmd.Parameters["@telgsm"].Value = TextBox_gsm.Text;
        c.cmd.Parameters["@email"].Value = TextBox_email.Text;
        c.cmd.Parameters["@np"].Value = TextBox_npconj.Text;
        c.cmd.Parameters["@proff"].Value = TextBox_prof.Text;
        c.cmd.Parameters["@empl"].Value = TextBox_empl.Text;
        c.cmd.Parameters["@retraite"].Value = DropDownList_retraite.SelectedValue;
        c.cmd.Parameters["@TypeOperation"].Value = 0;
        c.cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        if (c.cn.State == ConnectionState.Open)
        {
            c.cn.Close();
        }
    }
}

and my stroredprocedure :

USE [CVtech]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[AjouterAgent]
 @ppr int,
@lieu varchar(100),
@adresspro varchar(100),
@adressperso varchar(100),
@telbureau varchar(100),
@telgsm varchar(100),
@email varchar(100),

@np varchar(100),
@proff varchar(100),
@empl varchar(100),
@retraite varchar(3),
@TypeOperation nvarchar(1)
as
if(@TypeOperation = '0')
begin tran
 if exists ( select ppr from Agent where PPR = @ppr)
 begin
    insert into Agent (LieuNaissance,AdressePro, AdressePerso,TelBureau,TelPerso,Email)
    values (@lieu,@adresspro, @adressperso,@telbureau,@telgsm,@email)
end

insert into Conjoint (PPR,NomPrenom , Profession, Employeur, Retraite) values (@ppr ,@np ,@proff,@empl,@retraite)
commit

Upvotes: 0

Views: 97

Answers (5)

Martin Brown
Martin Brown

Reputation: 181

It doesn't seem to make sense that you are trying to do an insert where some value in Agent exists.

if exists ( select ppr from Agent where PPR = @ppr)
begin
    insert into Agent (LieuNaissance,AdressePro, AdressePerso,TelBureau,TelPerso,Email)
    values (@lieu,@adresspro, @adressperso,@telbureau,@telgsm,@email)
end

I would expect that if the item exists that you want to do an UPDATE not an INSERT.

Upvotes: 0

NickyvV
NickyvV

Reputation: 1746

So the code would be this:

ALTER proc [dbo].[AjouterAgent]
 @ppr int,
@lieu varchar(100),
@adresspro varchar(100),
@adressperso varchar(100),
@telbureau varchar(100),
@telgsm varchar(100),
@email varchar(100),

@np varchar(100),
@proff varchar(100),
@empl varchar(100),
@retraite varchar(3),
@TypeOperation nvarchar(1)
as
if(@TypeOperation = '0')
begin tran
 if exists ( select ppr from Agent where PPR = @ppr)
 begin
    UPDATE Agent
    SET LieuNaissance = @lieu
        ,AdressePro = @adresspro
        ,AdressePerso = @adressperso
        ,TelBureau = @telbureau
        ,TelPerso = @telgsm
        ,Email = @email
    WHERE ppr = @ppr
end

insert into Conjoint (PPR,NomPrenom , Profession, Employeur, Retraite) values (@ppr ,@np ,@proff,@empl,@retraite)
commit

Upvotes: 0

mitsu
mitsu

Reputation: 430

USE [CVtech]
GO
/****** Object:  StoredProcedure [dbo].[AjouterAgent]    Script Date: 02/04/2014 12:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[AjouterAgent]
 @ppr int,
@lieu varchar(100),
@adresspro varchar(100),
@adressperso varchar(100),
@telbureau varchar(100),
@telgsm varchar(100),
@email varchar(100),

@np varchar(100),
@proff varchar(100),
@empl varchar(100),
@retraite varchar(3),
@TypeOperation nvarchar(1)
as
if(@TypeOperation = '0')
begin tran
 if exists ( select ppr from Agent where PPR = @ppr)
 begin
    update Agent set  LieuNaissance=@lieu,AdressePro=@adresspro, AdressePerso=@adressperso , TelBureau=@telbureau,TelPerso=@telgsm,Email=@email
 where PPR = @ppr
end
 if exists ( select ppr from Agent where PPR = @ppr)
  begin 
  update Conjoint set PPR=@ppr,NomPrenom =@np, Profession=@proff, Employeur=@empl, Retraite=@retraite  where ppr= @ppr
  end 
  else 
     insert into Conjoint (PPR,NomPrenom , Profession, Employeur, Retraite) values (@ppr ,@np ,@proff,@empl,@retraite)

commit

Upvotes: 0

Mez
Mez

Reputation: 4726

Does your table have Identity Specification switched to off? To check go on your table, right click, design, select the primary key, in the options displayed you should see IDENTITY SPECIFICATION. This must be set to yes in your case. For more information about this, you can view this link from MSDN : http://msdn.microsoft.com/en-us/library/x5s13zy2.aspx

Obviously, if you want to do an update, you must execute an update statement, and not insert the same record again.

Upvotes: 1

vino20
vino20

Reputation: 429

Yes, first my question is, are you send the column value (which one has primary key constrain) from user? if no mean check that column has the identity or not(if no mean change it) if yes then you have to pass the unique value for that textbox

Upvotes: 0

Related Questions