Click here to Skip to main content
15,392,679 members
Please Sign up or sign in to vote.
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
Updated 2-Nov-11 20:22pm
v2
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.

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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900