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.
prInsert_OrderDetail
OrdDetID
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()
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";
sqlDataDet.Update(ds.Tables[0]);
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