It has a primary key!

DRCTECH · September 10th, 2009, 11:51 AM

I hope I am not missing something obvious here. When trying to make modifications to a database using linq, you must have a primary key. My database has a primary key and I have identified it in the class.

Code:

[Table(Name = "Accounts")]
        public class AllAccounts
        {
            [Column(DbType = "int not null, IsPrimaryKey=true, IsDBGenerated=true")]
            public int AccountID;

However when I atempt to insert data, I get the following error.
"Can't perform Create, Update or Delete operations on 'Table(AllAccounts)' because it has no primary key."

Code:

public class DRCACCT : DataContext
        {
            public DRCACCT(string connection) : base(connection) { }
            //table definitions
            public Table<VItems> Items;
            public Table<AllAccounts> AllAccount;//Account
        }

Code:

DRCData.AllAccounts pacct = new DRCData.AllAccounts();
                        
                        pacct.IsValid = true;
                        pacct.ApplicationOwner = "DRCTECH";
                        pacct.AccountType = 6;
                        pacct.Name = User.Identity.Name + DateTime.Now.ToString();//tbDescription.Text;
                        pacct.Active = true;
                        pacct.CreatedBy = User.Identity.Name;
                        pacct.CreatedDateTime = DateTime.Now.ToShortDateString();
                        pacct.OpeningBalanceDate = DateTime.Now.ToShortDateString();
                        pacct.OpeningBalance = 0;
                        db.AllAccount.InsertOnSubmit(pacct);
                        db.SubmitChanges();

I thought this was working at one point, but in trying to solve another problem, I removed it and then put it back. I point this out because I am using VS2008 and have noticed that sometimes changes I have made to my code does not get updated somewhere in the vsguts. So perhaps, somewhere in the depths of VS this code is not what it appears to be. Or perhaps there is just something wrong with the code itself and I have overlooked it. Any help would be appreciated.

SQLScott · September 10th, 2009, 01:34 PM

Two things come to mind. First, I would regen the model. Something probably got whacked when you were making changes and it thinks there is no primary (identity) key. Second, did you make any changes to the table itself? I ask only because you state "I removed it and then put it back". Are you refering to the table itself or the underlying class (the code you show above)?

I mention these only to make sure all angles are checked. If the table does have a primary key, then I would regen the model only because it thinks no primary key exists even though you are telling it there is one. So, either it really doesn't exist in the table or the model is out of sync.

Please keep me posted.

Scott

DRCTECH · September 10th, 2009, 02:49 PM

Yes, the table itself does have the primary key and identity specification assigned to AccountID. I did not use OR or Sqlmetal to define the entity, does regeneration still apply?

SQLScott · September 10th, 2009, 04:05 PM

Was any of it generated via OR or Sqlmetal? Meaning, from your last post, it sounds like you generated the model via OR or SqlMetal but added the Account entity manually. I know that is doable but I think there is a bit more than that.

Regen is still an issue but it will overwrite any custom code you have. If that isn't a concern, regen is still an option.

SQLScott · September 10th, 2009, 04:06 PM

A quick test would be to create a completely seperate VS 2008 project and gen the model via the OR and run the same code. That should be a fairly quick test and provide some insight...

DRCTECH · September 10th, 2009, 05:13 PM

I typed all the code in manually. I did not use either tool. However I just generated the code using sqlmetal for the entire data base and copied it into my class. It seems to have done a much better job than I did all be it a bit large. Now I am engaged in the arduous labor of replacing all the references to point to the new context. After that I am hoping it will compile and properly identify the key. From the looks of it, it will. Based on the intelesense, the data is much more accesable throughout the application now.
One problem has cropped up however: I have an class.int that was fine before, but now I am getting an error that it can not convert int to int when it analyzes this kind of statement: class.integeritem=item.integerfromLinqDataTable.
I have not looked at it closely yet as I was a bit frustrated to find that I may have to do more than just change the table references.

DRCTECH · September 11th, 2009, 12:21 AM

So, after making a few statements work, though highly inefficient. From

Code:

 class.dectype=class.dectype+dbfield.dectype;

to

Code:

 class.dectype=Convert.ToDecimal(Convert.ToDouble(class.dectype)+Convert.ToDouble(dbfield.dectype));

I took a closer look at the error to find that it was not "cannot convert int to int but rather int? to int. So SQLMetal has saved me the trouble of putting a bunch of zeros in null fields of tables. And a little attention to detail has saved me from spending a lot of time typing some pretty ridiculous code. In addition to that my database is much more accessible throughout the application and I have saved hours of entity definition coding thanks to SQLMetal. And thanks to you for suggesting I regenerate the model.