添加一行到具有标识主键SQL数据库 [英] Add a row to a sql database which has a identity primary key

问题描述

我有有一个名为ID列是一个主键与autoidentity设置一个简单的测试分贝。

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

我想一个新行添加到数据表我创建使用sqladapter

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

当我离开的空想着SQL会增加它，它会显示以下错误的新行的ID列：

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

我解决了，但给人ID喜欢博士[ID] = 99999

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

和它的工作，但现在当SQL数据库忽略该值，只是给它正确的数字DataTable中有99999 ID，这使得它有点混乱，如果我尝试插入另一纪录很可能会引发错误

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?

我们对此深感抱歉：

            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脚本：

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:

推荐答案

三样东西，你可能要检查：

Three things you might want to check:

1）你可以尝试

dt.Rows.Add(dr);

而不是

instead of

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

2）在SQL Server是否创建一个新的ID /完成语句W / O错误，如果你运行对表的INSERT语句？像

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) 

如果不是有可能是坏了的ID字段。

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

3）你检查适配器的（创建）插入命令是否正确？

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

编辑：你也可以 - 而不是使用CommandBuilder的，尽量手动创建INSERT命令，这样，看看会发生什么 - 如果这是工作确定再有就是一些错了你CommandBuilder的：

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;

这篇关于添加一行到具有标识主键SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！