如何更新主键列值， [英] How to update a primary key column value,

问题描述

我有一个如下表

信息(Srno int，InfoName varchar(20)，InfoDetails varchar(40))Srno作为主键

我使用
在表格中显示该表的记录

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

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);

现在，每当删除一条记录时，我都想更新所有记录的srno.即
如果我有以下记录

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

我删除了srno 2的记录，然后我想更新srno的其余记录.即

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

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

和以下命令来更新其余的sr号

and following command to update rest of the sr numbers

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

并要求在数据库中对其进行更新
da.Update(dt)//但是此命令给了我并发冲突异常

请建议我应该为此做什么?我只想删除一个主行值，并更新其余记录.

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.

推荐答案

我对此没有确切的答案，但是我的解决方法可以解决. .获取记录数据并删除该主记录，然后再次将其重新插入.
如果要提供用户定义的主键，请使用"SET Insert_Identity OFF/ON".

为了解决外键约束，请按照以下步骤操作

禁用所有约束(带有NOCHECK CONSTRAINT ALL的ALTER TABLE foo)
然后更新您的PK
然后更新您的FK以匹配PK更改
最后启用向后强制FK约束.


希望您知道如何在C#中做上述事情.

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#.

通过以下方法解决了

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();

这篇关于如何更新主键列值，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！