Ahmed Hashim

Ahmed Hashim

  • NA
  • 3
  • 80.6k

C# DataGridView with Identity Column

Jun 21 2013 2:13 AM
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





Answers (1)