Retrieve ID-value when adding a new record into a database.
HiIf I have a Database with the following columns:
- ID, int, auto-increase
- Name, char(25)
If I now download all content in this database into a dataset (adapter.Fill(dataset, "record")), and then create a XmlDataDocument:
XmlDataDocument doc = new XmlDataDocument(dataset);
and then add one new record:
XmlNode record = doc.CreateElement("record");
XmlNode name = doc.CreateElement("name");
name.InnerText = "George";
record.AppendChild(name);
doc.AppendChild(record);
Then I call dataset.AcceptChanges();
The database is then updated.
But how do I retrieve the new "ID" for the newly inserted record?
My DataGridView (that has the dataset as DataSource), shows the newly inserted record, but the "ID" field is empty.
Do I have to manually download all data again from the database, in order to get the value of the new ID of the inserted row?
Kind Regards
If you call AcceptChanges() before you update the database, the database won't be updated.
Remove the call to AcceptChanges() so the changes to the DataSet will be reflected in the database during the adapter.Update(dataset) command.
You could add your data to the DataSet Table directly as opposed to using XML. It would be less verbose. For example, assuming the DataTable is called, Customers, you could add the new record as such:
dataset.Customers.AddCustomersRow("George");
adapter.Update(Customers);
You could also take advantage of the TableAdapters:
dataset.Customers.AddCustomersRow("George");
CustomersTableAdapter adapter = new CustomersTableAdapter();
adapter.Update(dataset.Customers);
// It gets reflected in the XMLDataDocument
int count = doc.ChildNodes.Count;
Regards,
Dave
You need to make sure you are getting the PK from the database during the update.
In this article I just wrote about TableAdapters:
TableAdapters : DataTable ConstraintException Was Unhandled - Column is Constrained to be Unique - Value is Present
You will notice that two SQL Statements are sent during an insert command. The first one enters the information into the table, the second one retrieves the PK (CustomerId) that was generated by the table. Both of these SQL Statements fire for each inserted record:
INSERT INTO [dbo].[Customers] ([FirstName], [LastName]) VALUES (@FirstName, @LastName);
SELECT CustomerId, FirstName, LastName FROM Customers WHERE (CustomerId = SCOPE_IDENTITY())
The key is the call to SCOPE_IDENTITY(), which gets the autogenerated PK.
SCOPE_IDENTITY() vs. @@IDENTITY - Retrieving Identity for Most Recently Added Row in Table
I don't know if you have the opportunity in your application to use the TableAdapters, but if you do, they make life quite a bit easier for doing. The TableAdapters handle all of this for you with code that looks like I mentioned above:
dataset.Customers.AddCustomersRow("George");
CustomersTableAdapter adapter = new CustomersTableAdapter();
adapter.Update(dataset.Customers);
If you are not using TableAdapters, you will have to set things up manually.
CustomersDataSet dataset = new CustomersDataSet();
dataset.Customers.AddCustomersRow("Bill");
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand("INSERT INTO [dbo].[Customers] ([Name]) VALUES (@Name) SELECT CustomerId, Name FROM Customers WHERE (CustomerId = SCOPE_IDENTITY())");
adapter.InsertCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
adapter.InsertCommand.Connection = new SqlConnection("...");
adapter.Update(dataset.Customers);
Regards,
Dave
