How to auto-increment Primary key when adding a new row using Update method? RRS feed

  • Question

  • User1353940905 posted

    Hi guys,

    I followed the ASP.net official tutorial to create a DAL & Business Logic Layer (http://www.asp.net/learn/dataaccess/tutorial02cs.aspx). I have a table with a int ID field. I wish to write a function to add a new entry into this table but have the ID field auto-increment.

    The ID field is set as the Identity Column and has a Identity Increment & Seed of "1". If I manually go to the table and insert a new record leaving the ID value null it automatically increments. But if I create a C# function to add a new entry I get an error saying that the ID field can't be Null. Is there any way to use the Update method as shown on line 14 below to add a new entry but with it automatically incrementing?

    I did create a function called InsertDevice that simply inserts the other fields using a SQL INSERT and it auto-increments fine, just wondering if there is a way to do it using the DataTable and the Update method? Thanks for any help!!!



    1    public bool AddDevice(string make, string model)
    2        {
    3            //cannot have the same device entered twice!
    4            if (Adapter.FillDeviceCountByMakeModel(make, model) == 1)
    5                return false;
    7            RepositoryDataSet.DevicesDataTable devices = new RepositoryDataSet.DevicesDataTable();
    8            RepositoryDataSet.DevicesRow device = devices.NewDevicesRow();
    10           device.make = make;
    11           device.model = model;
    13           devices.AddDevicesRow(device); << Error thrown Here!
    14           int rows_affected = Adapter.Update(devices);
    16           return rows_affected == 1;        
    17       }

    Thursday, July 26, 2007 11:32 AM

All replies

  • User-442214108 posted

    It's hard to say - - show us the code for this method:
             devices.AddDevicesRow(device); << Error thrown Here!

    Thursday, July 26, 2007 12:09 PM
  • User1353940905 posted

     AddDevicesRow() is an auto - generated method done for you by Visual Studio. Its the same as DataTable.AddRow(). It just adds a row to the dataTable.

    Thursday, July 26, 2007 12:33 PM
  • User1111434469 posted

    Did you find a solution about this problem, because i have exactly the same problem and it throws:

    Cannot insert explicit value for identity column in table 'UserDetails' when IDENTITY_INSERT is set to OFF.

    Line 4186:        [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
    Line 4187:        public virtual int Update(FlightResults.UserDetailsDataTable dataTable) {
    Line 4188:            return this.Adapter.Update(dataTable);
    Line 4189:        }
    Line 4190:       
    Source File: C:\Inetpub\wwwroot\Views1\CodeClasses\DAL\MyResults.Designer.cs    Line: 4188

    Thursday, March 3, 2011 4:50 AM