DataGridView with Identity Column

[ahmed_one]

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. I need OrdDetID to batch update the data from DataGridView to database.


Any ideas/suggestions?



Thanks


Ahmed

 

[jmcilhinney]

Your problem is that you're populating an output parameter with the new ID but, when you call Update on a data adapter, you have no way to get that parameter value. What you need to do is actually select the new ID back into the same DataTable. If you want the sproc to support both options then you can change it to 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()
SELECT OrdDetID = SCOPE_IDENTITY()

If you don't need to use the output parameter anywhere else then you can do away with it altogether.

By the way, you should be doing something similar when you insert an Order record. If you a single DataSet containing two related DataTable then you can insert the Order, automatically retrieve the new ID, automatically propagate it to the OrderDetail records, save them and then automatically retrieve the new IDs for them too.

 

[ahmed_one]

Thanks for your prompt reply, Actually this is the part of code what you've mentioned, It is Master/Detail type of project, I can enter Order, and everything with OrderDetails and then saved it with the code mentioned earlier, OrdDetID is a reference for each and every product entered by user, so that when user needs to update the record, we can use OrdDetID (with OrderID) to update that product (e.g unit, price). If there is any easier way I will appreciate if you can teach me, or better if there is any sample you can share, with the same theme discussed here?