Multiple Primary Key

Question

 

Hello;

I'm a computer enginnering student at Turkey- Sakarya University..

I have a basic problem; I dont know whether this is a bug or not...

 

I have a basic DB design like this

When I want update the data that I get from TELEFON table I get an exception like this

 

Value of member 'TelNo' of an object of type 'Telefon' changed.
A member defining the identity of the object cannot be changed.
Consider adding a new object with new identity and deleting the existing one instead.

 

My code is like that

 

Code Snippet

ctx = new TelDBDataContext();

var a = from t in ctx.Telefons select t;

dataGridView1.DataSource = a;

 

 

At the update buton:

 

Code Snippet

ctx.SubmitChanges();

 

Best Regards...

Answer 1

I guess You changed value of TelNo (your primary key) in your form, so you got the error.

 

If this is correct,

consider adding new column TelId of type int or uniqueidentifier to database table and your data model in project and set this column as PrimaryKey.

 

If you don't want create new column, you need to redesign your form. If user want to change TelNo you ie. display Dialog like "Are you sure?" - if yes: copy editing item to new item

Code Snippet

TELEFON current = dataGridView1.CurrentRow.DataBoundItem  as TELEFON;

TELEFON item = new TELEFON();

item.col1 = current.col1;

...

item.colN = current.colN;

 

//after copy you need to delete old item and add new item from/to table

ctx.Telefons.Remove(current);

ctx.Telefons.Add(item)

 

 

I think that first way is simpler and less complicated. Your code will be also cleaner.

Answer 2

 

Sorry; I didn't realise that my database design image isn't shown...

 

My design is here:

 

 

Person(Table)

PersonId (PK,int, not null)

Name(nvarchar,not null)

Surname(nvarchar,not null)

 

Telefon(Table)

PersonId(PK,FK,int, not null)

TelNo(PK,nvarchar, not null)

 

if my Telefon table's design was like below; my program will run without any exception(of course after .dbml file re-created)

 

Telefon(Table)

TelefonID(PK,int not null)

PersonId(FK,int, not null)

TelNo(nvarchar, not null)

 

But as you seen: my Database Design that has multiple primarykeys, must be changed... And I don't want that...

 

Best regards!!

Answer 3

Hello,

 

By their very nature, primary keys should be only placed on columns which will be the same for the life of the record. So not something like a telephone number or address.....good examples are date of birth, or auto id.

 

For example, if you had other tables off the TelNo key then changing the number will affect the relationship between the two tables for that record and you could have data integrity problems.

 

The best solution would be to change the design of your table to be more flexible.  Long term payoff.

 

If thats not an option then you have to remove then reinsert.   Possibly implementing the UpdateTelefon to do the work for you would work (remove old instance and insert new) so the work the calling code has to do is less.

 

Hope this helps.

 

Ben

Answer 4

Very Thanks Ben;

 

You gave me the solution..

 

Good work..

 

Best Regards

Answer 5

Hey Dude, i know this post has been long but currently im kinda facing the same problem. im getting an exception when i try to update a table in my database referencing another table in my database. i get an exception msg that says

Value of member 'RoleId' of an object of type 'ReleaseContact' changed.
A member defining the identity of the object cannot be changed.
Consider adding a new object with new identity and deleting the existing one instead.

i am sort of confused as to what that error means and actually how i should solve it. because all the data in the table i want to update are all FKs to other tables and i am not sure how they are being identity.

i included my code for the update function here

 ReleaseContact rcp = (from u in db.Users
                                     from r in db.Releases
                                     from rct in db.ReleaseContacts
                                     where u.Name == listbox_alluser.SelectedItem.Text && u.UserId == rct.UserId && r.ReleaseName
                                          == listbox_release.SelectedItem.Text && r.ReleaseId == rct.ReleaseId
                                     select rct).Single<ReleaseContact>();

                rcp.RoleId = (Convert.ToInt16(ddl_relrole.SelectedIndex.ToString()));

                    db.SubmitChanges();

The table ReleaseContact is the table i want to update its RoleId but i need to make sure that the user name i selected and the release name i selected exists so that i can get their ids because all i input in my ReleaseContact table are just (ReleaseId, UserId, RoleId).

Any help will be appreciated.
Thanks

Answer 6

Hi Man;

I understand that you have a table that have 3 columns and these columns are FK and them 3 are composite Primary Key;

I can say you that; add a new auto-incremented identity column(PK) -like ReleaseContactID - and take PK back from 3 of that columns. This will solve the problem.

On the other hand as Ben said; you can delete the row and insert again... This can be another solution..

Sorry for my bad english

Best regards..

M. Tahir Çakmak
Ford Otosan
Software Developer

Answer 7

hey man but how exactly can i add the row again when im not getting any of my values directly im getting them referencing from a list box e.g when i click listbox A(ReleaseName), i should go to Release table  and take Release id before i can add it in ReleaseContact. how do i actually code that.

I wrote a sample code that can help me reference each table but i dont know how to add it nd submit to database


ReleaseContact relcnt = (from usrs in db.Users
                                    from rels in db.Releases
                                 from role in db.Roles
                                   where usrs.Name == listbox_alluser.SelectedItem.Text && rels.ReleaseName ==
                        listbox_sortrel.SelectedItem.Text && role.RoleId == (Convert.ToInt16(ddl_relrole.SelectedIndex.ToString()))
                                  select new { rels.ReleaseId, usrs.UserId, role.RoleId });


            relcnt.ReleaseId = .........?? how to get the same release id tht i have selected from my result from my LINq sql??

Thanks
Kingsley