Add a row to a sql database which has a identity primary key

Question

I have a simple test db which has a column called ID which is a Primary Key with autoidentity set.

I am trying to add a new row to the datatable I created using the sqladapter

when i leave the ID column of the new row empty thinking the sql would add it it displays the following error:

"Column "ID" doesn't not allow nulls

I solved it but giving the ID any value like dr["ID"] = 99999

And it worked, but now while the SQL database ignores the value and just gives it the correct number the datatable has the 99999 ID which makes it kinda confusing and will probably throw errors if i try to insert another record

How can i solve this problem?

sorry about that:

            DataRow dr = dt.NewRow();
            dr["Name"] = "fds";//E.Name;
            dr["Age"] = 12;// E.Age;
            dr["DateOfBirth"] = "1-1-1999";// E.DoB;
            //dr["ID"] = 99999;
            dt.Rows.InsertAt(dr, dt.Rows.Count - 1);
            DBOperations.UpdateTable(dt);
            MessageBox.Show("Record added successfully");

    static internal void UpdateTable(DataTable dt)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
        SqlDataAdapter Adapter = new SqlDataAdapter();
        Adapter.SelectCommand = new SqlCommand("select * from Emp",con); 
        Adapter.UpdateCommand = new SqlCommandBuilder(Adapter).GetUpdateCommand();
        Adapter.InsertCommand = new SqlCommandBuilder(Adapter).GetInsertCommand();
        Adapter.DeleteCommand = new SqlCommandBuilder(Adapter).GetDeleteCommand();
        Adapter.Update(dt);
    }

DB Script:

USE [HR]
GO
/****** Object:  Table [dbo].[Emp]    Script Date: 2/12/2013 8:10:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emp](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Age] [int] NOT NULL,
    [DateOfBirth] [date] NOT NULL,
 CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Screenshot:

Answer 1

Three things you might want to check:

1) You could try

dt.Rows.Add(dr);

instead of

dt.Rows.InsertAt(dr, dt.Rows.Count - 1);

2) Does the SQL Server create a new ID / complete the statement w/o errors if you run an INSERT statement against the table? Like

INSERT INTO tb_mytable (Name, Age) VALUES ('fds', 12) 

If not there might be something wrong with the ID field.

3) Have you checked the Adapter's (created) Insert Command for correctness?

Edit: You could also - instead of using the CommandBuilder, try creating the Insert Command manually, like this and see what happens - if this is working ok then there is something wrong with your CommandBuilder:

string sql  = "INSERT INTO [dbo].[emp] ";
sql += "(Name, Age, DateOfBirth) ";
sql += "Values (@Name, @Age, @DateOfBirth)";

SqlCommand insertCommand = new SqlCommand();
insertCommand.CommandText = sql;

SqlParameter paramName = new SqlParameter();
paramName.ParameterName = "@Name";
paramName.DbType = DbType.AnsiString;
paramName.SqlDbType = SqlDbType.NVarChar;
paramName.SourceVersion = DataRowVersion.Current;
paramName.SourceColumn = "Name";

SqlParameter paramAge = new SqlParameter();
paramAge.ParameterName = "@Age";
paramAge.DbType = DbType.Int32;
paramAge.SqlDbType = SqlDbType.Int;
paramAge.SourceVersion = DataRowVersion.Current;
paramAge.SourceColumn = "Age";

SqlParameter paramDoB = new SqlParameter();
paramDoB.ParameterName = "@DateOfBirth";
paramDoB.DbType = DbType.Date;
paramDoB.SqlDbType = SqlDbType.Date;
paramDoB.SourceVersion = DataRowVersion.Current;
paramDoB.SourceColumn = "DateOfBirth";

insertCommand.Parameters.Add(paramName);
insertCommand.Parameters.Add(paramAge);
insertCommand.Parameters.Add(paramDoB);

Adapter.InsertCommand = insertCommand;

Answer 2

The error is coming from the DataAdapter, I'm pretty sure, not from the database. See http://msdn.microsoft.com/en-us/library/ms971502.aspx