title: C# DataGridView with Identity Column
body: <p>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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>As code mentioned, I am using a stored procedure <code>prInsert_OrderDetail</code>. The <code>OrdDetID</code> is a sort of LineItem number, which is autoincrement field in the database.</p>
  
  <p>For setting up DataGridView I am using following:</p>
  
  <pre><code>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";
  </code></pre>
  
  <p>In the Save button click event, following code is use to save the data from DataGridView to Database:</p>
  
  <pre><code>sqlDataDet.Update(ds.Tables[0]);
  </code></pre>
  
  <p>The stored procedure in SQL Server is this:</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>Data is saved in database correctly, my problem is how can I get <code>OrdDetID</code> 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.</p>
  
  <p>Any ideas/suggestions?</p>
  
  <p>Thanks</p>
  
  <p>Ahmed </p>