Updating DataSet's identity after insert

Topics: Data Access Application Block
May 17, 2007 at 3:47 PM
Edited May 17, 2007 at 3:48 PM
My primary key on my Invoice table is an identity column called InvoiceID. How do I update this field's value in the dataset after I perform the insert using the UpdateDataSet function?

Here is the my function that creates the function that is being called

Public Function SaveDataSet(ByVal ds As DataSet, ByVal tableName As String) As Integer
Dim _db As Database = MyDbFactory.CreateDatabase(Globals.ConnString)

Dim insertCmd As DbCommand = _db.GetStoredProcCommand("InvoiceInsert")
_db.AddInParameter(insertCmd, "TranID", DbType.Int32, "TranID", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "InternalID", DbType.String, "InternalID", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "ApprovalRequired", DbType.Boolean, "ApprovalRequired", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "ApproverEmail", DbType.String, "ApproverEmail", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "InstancePrefix", DbType.String, "InstancePrefix", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "JournalOnly", DbType.Boolean, "JournalOnly", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "Status", DbType.String, "Status", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "CreatedUser", DbType.String, "CreatedUser", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "CreatedDate", DbType.DateTime, "CreatedDate", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "ModifiedDate", DbType.DateTime, "ModifiedDate", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "SubmittedDate", DbType.DateTime, "SubmittedDate", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "ApprovedDate", DbType.DateTime, "ApprovedDate", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "DateSent", DbType.DateTime, "DateSentToIDEAS", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "Misc1", DbType.String, "Misc1", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "Misc2", DbType.String, "Misc2", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "Misc3", DbType.String, "Misc3", DataRowVersion.Current)
_db.AddInParameter(insertCmd, "Misc4", DbType.String, "Misc4", DataRowVersion.Current)

Return _db.UpdateDataSet(ds, tableName, insertCmd, Nothing, Nothing, UpdateBehavior.Standard)

End Function

Here is the InvoiceInsert stored proceure:

----------------------------------------------------------------------------
-- Insert a single record into Invoice
----------------------------------------------------------------------------
CREATE PROC dbo.InvoiceInsert
@TranID int,
@InternalID varchar(100) = NULL,
@ApprovalRequired bit = NULL,
@ApproverEmail varchar(100) = NULL,
@InstancePrefix varchar(15) = NULL,
@JournalOnly bit = NULL,
@Status varchar(50) = NULL,
@CreatedUser varchar(50) = NULL,
@CreatedDate datetime = NULL,
@ModifiedDate datetime = NULL,
@SubmittedDate datetime = NULL,
@ApprovedDate datetime = NULL,
@DateSent datetime = NULL,
@Misc1 varchar(250) = NULL,
@Misc2 varchar(250) = NULL,
@Misc3 varchar(250) = NULL,
@Misc4 varchar(250) = NULL
AS

INSERT Invoice(TranID, InternalID, ApprovalRequired, ApproverEmail, InstancePrefix, JournalOnly, Status, CreatedUser, CreatedDate, ModifiedDate, SubmittedDate, ApprovedDate, DateSent, Misc1, Misc2, Misc3, Misc4)
VALUES (@TranID, @InternalID, @ApprovalRequired, @ApproverEmail, @InstancePrefix, @JournalOnly, @Status, @CreatedUser, @CreatedDate, @ModifiedDate, @SubmittedDate, @ApprovedDate, @DateSent, @Misc1, @Misc2, @Misc3, @Misc4)

SELECT SCOPE_IDENTITY() AS NewInvoiceID

---------------------------------------------------

Any help is greatly appreciated.
May 17, 2007 at 4:41 PM
I found a solution, but now I have a new problem.

I added "insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord" to my SaveDataSet function. Then I changed my query to select the entire row that was updated, not just the identity.
May 17, 2007 at 6:51 PM
I am having the same problem as well where the dataset is not reflecting the sql server id's after the UpdateDataSet. I have followed the examples in this article: http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.80).aspx and my code is as follows (simplified):

DAAB:
DbCommand insertCommand = db.GetStoredProcCommand("dbo.Employee.Insert");
db.AddParameter(insertCommand, "Id", DbType.Int32, ParameterDirection.Output, "Id", DataRowVersion.Current, "Id");
insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
db.UpdateDataSet(employeeDS, "Employee", insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard, 0);

After the UpdateDataSet call the Ids for the newly inserted rows in employeeDS are still null (checked all DataRow versions). The value of the parameter (insertCommand.Parameters"@Id".Value) is returned correctly from SQL Server however. I also tried the method of UpdateRowSource.FirstReturnedRecord but to no avail.
May 7, 2009 at 6:16 PM
bump!
May 8, 2009 at 9:12 AM

Hi,

Try using cmd.UpdatedRowSource = UpdateRowSource.Both; and see what happens.

Valiant Dudan
Global Technology & Solutions
Avanade, Inc.

entlib.support@avanade.com