How to update a primary key column value,

Question

0.00/5 (No votes)

See more:

I am having a table as follows

Info (Srno int, InfoName varchar(20), InfoDetails varchar(40)) Srno as primary key

I show the records of this table in a grid using

C#

da = new SqlDataAdapter(string.Format("select * from {0}", TableName), con);
   da.Fill(dt);
   SqlCommandBuilder cmdbldBINDASS = new SqlCommandBuilder(da);
     
   dgv.DataSource = dt;
   dt.PrimaryKey = new DataColumn[] {                 dt.Columns[m_StrColumnsName[(int)COLUMN_NAMES.SRNO]] };
   dt.AcceptChanges();
   dgv.Sort(dgv.Columns[m_StrColumnsName[(int)COLUMN_NAMES.SRNO]], ListSortDirection.Ascending);

Now whenever I deletes a record, I alse want to update the srno of all the records. Ie.
if i am having following records

Srno     InfoName     InfoDetails
1          ABC           ghshd
2          XYZ           fkjkfj
3          FGt           wgwwiii
4          hjk           dkwldiw

and I delete record of srno 2 then I want to update the rest of the records srno. ie

Srno     InfoName     InfoDetails
1          ABC           ghshd
2          FGt           wgwwiii  // here the srno is been updated from 3 to 2
3          hjk           dkwldiw  // here the srno is been updated from 4 to 3

I write following command to delete row from grid and data base

C#

dgv.Rows.Remove(row);
 da.Update(dt);

and following command to update rest of the sr numbers

C#

foreach (DataGridViewRow item in abc)
  {
     if (!item.IsNewRow)
     {
      item.Cells[m_StrColumnsName[(int)COLUMN_NAMES.SRNO]].Value = item.Index +1;
     }
  }

and calls to update it in data base
da.Update(dt) // but this command is giving me concurrency violation exception

please suggest what I should do for this?? I just want to delete a primary row value, and update rest of the records.

Posted 2-Nov-11 20:20pm

meha23

Updated 2-Nov-11 20:22pm

v2

Add a Solution

Comments

Bala Selvanayagam 3-Nov-11 8:01am

Out of interest, why do you want to update the primary key column and keep it as a continious number series ?

If does not needs to be.

updating the primary key column may defeat the idea the relational database when it comes to many table connected to your table. Are you going to update other tables too ?

meha23 3-Nov-11 14:21pm

Well Sir, It's according to my requirement actually, I am using a client - server application

The server is doing computation with each of the record one by one based on its sr number

The client can update, delete and insert records.

Whenever a client inserts a record at a particular location it sends the server serial number value, so that the server can also add the same record at the given position and updates the whole list accordingly.
same is the senario with delete.

That is the main reason, I need to update, all the records whenever an insertion or deletion takes place.

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)

deepakdynamite · Answer 1 · 2011-11-02T20:27:00

Solution 1

I don't have extact answer for this but I workaround will work.. Take the record data and delete that Primary record and again re-insert it.

well if you want to give userdefined primary key , in that case use "SET Insert_Identity OFF/ON".

In order to tackle with Foreign key constraints, Follow these steps

Disable all constraints (ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL)
Then update your PK
Then update your FKs to match the PK change
Finally enable back enforcing FK constraints.

I hope you know how to do above things in C#.

Posted 2-Nov-11 20:27pm

deepakdynamite

Updated 2-Nov-11 20:35pm

v3

Comments

meha23 3-Nov-11 2:39am

I don't have any foreign key constraints, I only want to update srn of records when a record is deleted. The reason I thought for this concurrency violation exception is that the record with srn 2 has been deleted, and I am updating a record with srn 2

deepakdynamite 5-Nov-11 0:15am

Then make it simple.... Either you delete the current record and create a new record with new ID and same data as deleted record.... or try to directly update it ..

meha23 · Answer 2 · 2011-11-04T18:03:00

Solved with following method

SqlConnection Con = new SQLConnection(connectionString);

con.open();

SQLTransaction trans = con.BeginTransaction();

SQLCommand cmd = con.CreateCommand();
cmd.connection=con;

//Transaction for delete operation

cmd.transaction= trans;

cmd.commandText="delete statement";

cmd.executeNonQuery();

trans.commit();

//open new transation for update

trans = con.BeginTransaction();

cmd.commandText="update statement";

cmd.executeNonQuery();

trans.commit();

con.close();