DataAdapter Update() requires input parameter for Auto increment primary key column

Question

While updating a DataTable to a SQL Server database I get the error message "Column 'PK_Column' does not allow nulls" after calling GetErrors() I don't want to provide a value for PK_Column because it is a auto increment primary key column in the database. My insert statement looks like this:

INSERT INTO [Order] ([Customer_Id], [OrderTime], [OrderType])
VALUES(@Customer_Id, @OrderTime, @OrderType)
SELECT CAST(SCOPE_IDENTITY() AS int) AS '@PK_Column'

It works as expected in SQL Server Management Studio, so the query is obviously not the problem.

I have four parameters on the insert command, one output parameter (@PK_Column) and three input parameters (@Customer_Id, @OrderTime, @OrderType). I figured out that I don't get the error if I set @PK_Column to InputOutput parameter, but then the PK_Column value does not get updated with the correct value created by the database.

Answer 1

Try

SELECT @PK_Column = SCOPE_IDENTITY()

This way, you assign to the local variable @PK_Column, which is picked up as a parameter.

When you do ... AS '@PK_Column', you are creating a data set with one column called "@PK_Column", when you want to assign a value to local var/parameter @PK_Column

Note: ... AS @PK_Column would fail because this is column alias. You're relying on a setting that allows '@PK_Column' to be a valid alias where as @PK_Column would fail