Insert command should work... but it doesn't.

[SaintJimmy]

I know how to construct an InsertCommand for a DataAdapter so that the adapter updates the identity (autonumber) field with the value the database assigns... but for whatever reason, I cannot get it to work.

Check it out... here's how I've set up my adapter:

            Dim cmdSelectOLDCustomerTable As New SqlCommand
            With cmdSelectOLDCustomerTable
                .CommandText = "SELECT ID, CustomerNumber, Cus_Network_Info " & _
                    "FROM OLD_Customer "
                .Connection = CLIDConnection
            End With
            Adapters.OLD_Customer = New SqlDataAdapter
            With Adapters.OLD_Customer
                .TableMappings.Add(New DataTableMapping("OLD_Customer", _
                    "CustomerNotes"))
                .SelectCommand = cmdSelectOLDCustomerTable
                .FillSchema(CLIDDataSet, SchemaType.Mapped, "CustomerNotes")
                ' This table will be fully editable, so build commands for
                ' INSERT, UPDATE, and DELETE operations
                Dim cbldrCustomerNotes As New SqlCommandBuilder(Adapters.OLD_Customer)
                .UpdateCommand = cbldrCustomerNotes.GetUpdateCommand
                .DeleteCommand = cbldrCustomerNotes.GetDeleteCommand
                ' The INSERT Command requires some fine-tuning to retrieve the
                ' returned ID value that SQL Server assigns
                Dim cmdInsertNote As New SqlCommand
                With cmdInsertNote
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "InsertCustomerNote"
                    .Connection = CLIDConnection
                    With .Parameters.Add("@CustomerID", SqlDbType.NVarChar)
                        .SourceColumn = "CustomerNumber"
                        .Direction = ParameterDirection.Input
                    End With
                    With .Parameters.Add("@NoteText", SqlDbType.NVarChar)
                        .SourceColumn = "Cus_Network_Info"
                        .Direction = ParameterDirection.Input
                    End With
                    With .Parameters.Add("@Identity", SqlDbType.Int)
                        .SourceColumn = "ID"
                        .Direction = ParameterDirection.Output
                    End With
                    .UpdatedRowSource = UpdateRowSource.Both
                End With
                .InsertCommand = cmdInsertNote
            End With

You can see I'm using an SQL Server database, and I've got a stored procedure called "InsertCustomerNote" with an output parameter that should update the ID field in the local DataSet. I've set it up as I've seen in hundreds of examples... and it's not working. I add a new row to the DataSet and then call Update on the adapter, and the ID in the local DataSet and the ID in the database are out of sync.

Am I missing something in setting up the adapter?


FYI: Here is the stored procedure:

[SIZE=2][COLOR=#0000ff][COLOR=#000000]ALTER PROCEDURE [dbo].[InsertCustomerNote]
 @CustomerID nvarchar(7),
 @NoteText nvarchar(max),
 @Identity int OUT
AS
INSERT INTO OLD_Customer (CustomerNumber, Cus_Network_Info)
 VALUES (@CustomerID, @NoteText)
SELECT @Identity = SCOPE_IDENTITY()[/COLOR][/COLOR][/SIZE]