updating table but why it is giving insertion error ?

Question

0.00/5 (No votes)

See more:

I am getting below error while updating the data adapter.
" Violation of PRIMARY KEY constraint 'PK__Compalint__62D5F22C'. Cannot insert duplicate key in object 'Compalint'. The statement has been terminated. "

i am updating table but why it is giving insertion error ?
Let me know if i am doing something wrong.. :(

i used below code:-

VB

Dim Da As New SqlDataAdapter("select userid,loginid from compalint", conn)
  Dim cmdbld As New SqlCommandBuilder(Da)
     cmdbld.GetUpdateCommand()
         Da.Update(ds1, "table")

and my ds1 contain two fields userid,loginid only...

thanks in advance...

Posted 3-Jun-12 22:48pm

ssd_coolguy

Updated 10-Apr-19 2:09am

Add a Solution

Comments

Jim Jos 4-Jun-12 4:56am

What are you having in DS1? Please put the code where you are getting DS1 rows updated.. If you are putting new rows in DS1 it will not update it will only try to insert..

ssd_coolguy 4-Jun-12 5:03am

thanks for reply...
dataset ds1 contains userid and loginid. and it is getting from xml file.
and i want to update compalint table's loginid field only.. because userid is primary key..
is my dataadapter update code is wrong?

Jim Jos 4-Jun-12 5:07am

You need to update the value in dataset so that the 'RowState' property is set to UPDATE mode.. then only it will update.. that's y I am asking the code where you update the dataset. You are saying Dataset is loaded from xml but you are trying to update the table.. DS1 should be taken from Complaint table for update to work.. otherwise it will be inserting only..

ssd_coolguy 4-Jun-12 5:20am

yeah Sir you are right we need to set the 'RowState' property is UPDATE mode..
As per you, we can't update the tabel if we not take dataset from that table only..
So is there any way to solve this problem??

Sorry for my poor english :(

Jim Jos 4-Jun-12 5:52am

OK no problems.. let me summarize your requirements.. 1. Read Data from xml.. 2. update the complaint table using the data read from xml .. am I right?

ssd_coolguy 4-Jun-12 5:59am

yes right...

3 solutions

Solution 3

You need to update the value in dataset so that the 'RowState' property is set to UPDATE mode.. then only it will update.. that's y I am asking the code where you update the dataset. You are saying Dataset is loaded from xml but you are trying to update the table.. DS1 should be taken from Complaint table for update to work.. otherwise it will be inserting only..

Posted 10-Apr-19 2:09am

Seth Owens

Comments

Dave Kreskowiak 10-Apr-19 11:38am

And I doubt they're still looking for a solution to this SEVEN YEARS later.

OriginalGriff 11-Apr-19 8:18am

See S&A ...

Dave Kreskowiak 11-Apr-19 8:24am

Yeah, I already did. I posted the above and then found him in S&A when I checked an hour later.

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)

Shahin Khorshidnia · Accepted Answer · 2012-06-03T23:06:00

Solution 1

Check the Update Command I think it wants to change or insert a PrimaryKey column. Improve the Update Command.
Or
You need to check the constraints of the table in SQL Server. Remove the unnecessarily constraints.
Or
After the Transact-SQL statement is first generated, the application must explicitly call RefreshSchema if it changes the statement in any way. Otherwise, the GetUpdateCommand will still be using information from the previous statement, which might not be correct. The Transact-SQL statements are first generated when the application calls either Update or GetUpdateCommand. (More information in MSDN)

And also, look at this: Generating Commands with CommandBuilders (ADO.NET)

Posted 3-Jun-12 23:06pm

Shahin Khorshidnia

Updated 3-Jun-12 23:23pm

v2

Comments

ssd_coolguy 4-Jun-12 5:12am

thanks for reply..
what you mean improve the update command? i am not writing any updated command..
and my table contins only one primary key "uesrid".
Can you please explain more??

Shahin Khorshidnia 4-Jun-12 5:24am

You're welcome. Please read the updated solution again :)

ssd_coolguy 4-Jun-12 6:04am

hi shahin .. i am confused... :(

because.. when i seen the commandtext from cmdbld.GetUpdateCommand().commandtext() and i got below update command..

UPDATE [compalint] SET [userid] = @p1, [loginid] = @p2 WHERE (([userid] = @p3) AND ((@p4 = 1 AND [loginid] IS NULL) OR ([loginid] = @p5)))

update command is right.. but why it is giving error?? :( :(

Shahin Khorshidnia 4-Jun-12 7:35am

Because you can not set userId in update command. UseID is a primary key and I'm sure it's an Identity (Means increasing automatically). generally you can not set Identity Primary Keys by hand.

ssd_coolguy 4-Jun-12 8:43am

hey sorry but how i can remove it from update list... i tried to remove from select but it's giving error as primary key needs to update statement..:(

Shahin Khorshidnia 4-Jun-12 10:08am

I don't know the table. Maybe it needs to see the create t-sql query of the table. But try to use Select *. And... did you use cmdbld.RefreshSchema() after Dim cmdbld As New SqlCommandBuilder(Da)? It refreshes the automatically generated command.
And also, is it a real table or a view?!

ssd_coolguy 5-Jun-12 0:23am

hey.. i hav used refreshschema(). and i cheked with select *. but it asked for remaining fields..:(

my create query is like :-CREATE TABLE [Compalint] (

[UserID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Password] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[LoginID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Type] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ACTIVE_FLG] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[LAST_SIGN_IN] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[LAST_PWD_CHG] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

PRIMARY KEY CLUSTERED ( [UserID] ) ON [PRIMARY] ) ON [PRIMARY]GO

Shahin Khorshidnia 5-Jun-12 0:52am

I thought the UserID was Int or BigInt and Identity. That's why you can't update the table. It was better to defind UserID as BigInt and set it as Identity(1,1). But now, UserID is Not null and you need to insert a value to it while inserting and updating. I think you are inserting a duplicate UserID or You are inserting nothing.

ssd_coolguy 6-Jun-12 6:18am

hey shahin ...thanks for reply..

ssd_coolguy 5-Jun-12 1:04am

hey i think we are going wrongway..
can you just see the conversation between me and JimJos.
i am trying to updated the dataset using dataadapter but my datasets source is xml file...
your help would be appreciable...

Jim Jos · Accepted Answer · 2012-06-04T00:13:00

Here is a solution (lets use DataTable instead of DataSet)

1. Let dt1 have xml data

Create dt2 from DB

VB

Dim Da As New SqlDataAdapter("select userid,loginid from compalint", conn)
        Dim cmdbld As New SqlCommandBuilder(Da)
        Dim dt2 As DataTable = New DataTable
        Da.Fill(dt2)

3. Now you need to go through each row in dt1 and update dt2. Once you have finished updating dt2 call the udpate command..

VB

Dim foundRows As DataRow()

For Each dRow As DataRow In dt1.Rows

    'search userid in Dt2
    foundRows = dt2.Select("userid = '" & dRow("UserId") & "'")
    'go thru each row in foundrows and update the value
    For Each dFoundRow As DataRow In foundRows
        dFoundRow("LoginId") = dRow("LoginId")
    Next

Next

dt2.AcceptChanges()

cmdbld.GetUpdateCommand()
Da.Update(dt2, "table")

updating table but why it is giving insertion error ?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us