0

In the following instance, I have tried to simplify an issue to root components.

I've got a very simple SQL Azure database where I created a test table called Table1. Azure creates an ID field with Is Required, Is Primary Key checked. It will NOT allow to check the box Is Identity. There are a couple of other fields which are simply required.

In my VS2012 Project, I have created an LinqToSql Class which created a ProductionDataClasses1.dbml object.

I simply want to add a record to this table thru the method shown below. From what I am reading, ID would be set during the SubmitChanges() after InsertOnSubmit(NewRecord) is specified.

It does work the first time but value is set to zero. On subsequent save, I get an exception (basically it a duplicate record because ID=0 already exists).

To put this into context, I have included some sample code below. The idea is to first check if the record exists and update. If not, I want to add a record.

My question is... Do I need to manually set ID? If so, how do I set the value to an int and how to a retrieve the next value. I tried changing to a Guid but not allowed.

Here is my code sample:

      public bool AddTestRecord(string someValue)
     {
         ProductionDataClasses1DataContext context = new ProductionDataClasses1DataContext();
         try
         {
             var ExistingRecord = context.Table1s.SingleOrDefault(c => c.TextKey == someValue);
             if (ExistingRecord == null)
             {
                 var NewRecord = new Table1();

              //   NewRecord.ID = ???? ; How Do I Manually Set.  It is getting set to 0 causing a duplicate value exception
                 NewRecord.TextKey = someValue;
                 NewRecord.AnotherValue = DateTime.Now.ToShortTimeString();
                 context.Table1s.InsertOnSubmit(NewRecord);

             }
             else
             {
                 ExistingRecord.AnotherValue = DateTime.Now.TimeOfDay.ToString();
             }
             context.SubmitChanges();
             return true;
         }
         catch (Exception e)
         {
             Console.WriteLine(e.Message);
             return false;
             context.SubmitChanges();
         }
     }
Mohamed Mansour
  • 39,445
  • 10
  • 116
  • 90
Fast_Eddie
  • 1

2 Answers2

0

I would suggest manually running a SQL script to alter the table and make the column an identity. Look at this answer

Adding an identity to an existing column

Community
  • 1
  • 1
Matt Watson
  • 980
  • 7
  • 10
0

Thanks for your reply.

I just was finally able to make this work on a new table and will try to follow along your instructions to make modifications to my real table. My code (as written above) was OK so the issue is in the SQL Azure table definition.

I found the issue is that when you create a new table in SQL Azure, it creates a table with three fields, ID, Column1, Column2. By default, ID is set as the Primary Key but none are checked as Is Identity.

To make this work, I made ID the Is Identity and unchecked PrimaryKey and Column1 the In Primary Key. Thus when a new record is saved, the ID is set and Column1 is checked to make sure it is not already in the system. I had to do this when the table was first created. Once saved, it would not allow me to change.

Afterwards, I updated my Linq To SQL class and dropped the new table in. I noted that now the AutoGenerated Value on ID and PrimaryKey on Column1 was set and my code worked.

Fast_Eddie
  • 1