C# DataGridView with Identity Column

Question

I am using Visual Studio 2010, language C# and SQL Server Express Edition as backend. There is one form with DataGridView control, I've setup columns manually using designer, then on runtime set the data source.

connectionString = ConfigurationManager.AppSettings["connectionString"];
sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
String sqlSelectDet = "Select OrdDetID, OrderID, ProductID, UnitPrice, Quantity from OrderDetails"; 

//===============================================================================
//--- Set up the INSERT Command OrderDetails
//===============================================================================

sDetInsProcName = "prInsert_OrderDetail";
insertcommandDet = new SqlCommand(sDetInsProcName, sqlConnection);
insertcommandDet.CommandType = CommandType.StoredProcedure;

insertcommandDet.Parameters.Add("@nNewDetID", SqlDbType.Int, 4, "OrdDetID");
insertcommandDet.Parameters.Add("@nOrderID", SqlDbType.Int, 4, "OrderID");
insertcommandDet.Parameters.Add("@nProductID", SqlDbType.Int, 4, "ProductID");
insertcommandDet.Parameters.Add("@mUnitPrice", SqlDbType.Money, 8, "UnitPrice");
insertcommandDet.Parameters.Add("@nQuantity", SqlDbType.SmallInt, 2, "Quantity");

sqlDataDet.InsertCommand = insertcommandDet;

As code mentioned, I am using a stored procedure prInsert_OrderDetail. The OrdDetID is a sort of LineItem number, which is autoincrement field in the database.

For setting up DataGridView I am using following:

dtDet = new DataTable();
dtDet.Clear(); 
sqlDataDet.FillSchema(dtDet, SchemaType.Source);

ds = new DataSet();
ds.Tables.Add(dtDet);

ds.Tables[1].Columns["OrdDetID"].AutoIncrement = true;
ds.Tables[1].Columns["OrdDetID"].AutoIncrementSeed = -1;
ds.Tables[1].Columns["OrdDetID"].AutoIncrementStep = -1;

bsDet = new BindingSource();
bsDet.DataSource = ds;
bsDet.DataMember = "OrderDetails"; 

// Name of DataGridView Control is dgInvDet
dgInvDet.AutoGenerateColumns = false;
dgInvDet.DataSource = bsDet;
dgInvDet.Columns["OrdDetID"].DataPropertyName = "OrdDetID";
dgInvDet.Columns["OrderID"].DataPropertyName = "OrderID";
dgInvDet.Columns["ProductID"].DataPropertyName = "ProductID";
dgInvDet.Columns["UnitPrice"].DataPropertyName = "UnitPrice";
dgInvDet.Columns["Quantity"].DataPropertyName = "Quantity";

In the Save button click event, following code is use to save the data from DataGridView to Database:

sqlDataDet.Update(ds.Tables[0]);

The stored procedure in SQL Server is this:

ALTER  PROCEDURE [dbo].[prInsert_OrderDetail]
@nOrderID INT, 
@nProductID INT, 
@mUnitPrice MONEY, 
@nQuantity SMALLINT,
@nNewDetID INT OUTPUT
AS 
INSERT INTO [OrderDetails] (OrderID, ProductID, UnitPrice, Quantity)
VALUES (@nOrderID, @nProductID, @mUnitPrice, @nQuantity)

    SET @nNewDetID = SCOPE_IDENTITY()

Data is saved in database correctly, my problem is how can I get OrdDetID actual identity values, dataGridView doesn't shows actual values in DataGridView after records are inserted in database. Because what I am getting is -1, -2, -3 and so on due to the settings of column.

Any ideas/suggestions?

Thanks

Ahmed