SqlCommand not updating output parameters

ListBox1.Items.Add(dr("@RowID").ToString())

you shouldn't use @@identity
use
scope_identity instead

Yes, I realize this but that's not the problem in this case.  Changing it to Scope_Identity() makes no difference.  Plus, since there are no triggers involved in this case, @@Identity and Scope_Identity() will return the same value.  The reason I was using @@Identity is that I was moving this code over from another db system that doesn't support Scope_Identity also.

Output parameters will not be available until the connection is closed.

You must read them after the "End Using" statement.

I think that is only true for a datareader.  Are you suggesting that I need to close the connection after every data row insert?  I have never seen that in any code construct.  Think of a sqldataadapter where the code construct is almost the exact same.  I'm inserting 10,000 records.  Surely the db connection isn't closed an opened 10,000 times...

I researched this a bit further and the actual case is that the reader itself needs to be closed, not the connection.  Moreover, this only applies if you're doing an executereader command, not an executenonquery command.  Lastly this "functionality" is considered by most as an ADO.NET bug.

I believe that output parameters only applies to stored procedures which have a parameter set as OUTPUT.  In your code, you are SELECTing the identity, which returns a resultset, not an output parameter.

You may try creating an insert procedure and select the identity back into the output parameter.

More to the story... I have used for quite some time a utility called DataAdapterBuilder from Microsoft that builds a dataadapter with all of the update, insert, and delete commands.  You simply need to connect to the database, give the select statement, and it builds the following code.  I use it all the time instead of the new TableAdapter functionality which I've never taken much of a shine to.  In any case, check out the code below.  It works perfectly.  However, what is not shown is what the dataadapter is doing under the covers, that is how does it pass all the rows to the database and update the identity values appropriately back into the datatable.  That is what I'm trying to figure out how to do.  I know I could continue to use this, but I'm trying to add to my general purpose db library the ability to pass into my crud routine a progress bar so that it can update the progress bar as records are being done.

Private Function CreateDataAdapter(conn AS SQLConnection) As SqlDataAdapter

    Dim da As New SqlDataAdapter("SELECT * FROM Test", conn)

    Dim dtm As Common.DataTableMapping
    dtm = da.TableMappings.Add("Table", "Test")
    dtm.ColumnMappings.Add("RowID", "RowID")
    dtm.ColumnMappings.Add("SKU", "SKU")
    dtm.ColumnMappings.Add("FieldName", "FieldName")
    dtm.ColumnMappings.Add("FieldValue", "FieldValue")

    Dim cmd As SqlCommand
    da.UpdateCommand = da.SelectCommand.Connection.CreateCommand()
    cmd = da.UpdateCommand
    cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
    cmd.CommandText = "UPDATE [Test] SET [SKU] = @SKUCurrent, [FieldName] = @FieldNameCurrent, [FieldValue] = @FieldValueCurrent WHERE [RowID] = @RowIDOriginal AND [SKU] = @SKUOriginal AND [FieldName] = @FieldNameOriginal AND [FieldValue] = @FieldValueOriginal; SELECT * FROM Test WHERE [RowID] = @RowIDCurrent"
    cmd.Parameters.Add(New SqlParameter("@SKUCurrent", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Current, Nothing))
    cmd.Parameters.Add(New SqlParameter("@FieldNameCurrent", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Current, Nothing))
    cmd.Parameters.Add(New SqlParameter("@FieldValueCurrent", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Current, Nothing))
    cmd.Parameters.Add(New SqlParameter("@RowIDOriginal", SqlDbType.Int, 4, ParameterDirection.Input, False, 10, 0, "RowID", DataRowVersion.Original, Nothing))
    cmd.Parameters.Add(New SqlParameter("@SKUOriginal", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Original, Nothing))
    cmd.Parameters.Add(New SqlParameter("@FieldNameOriginal", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Original, Nothing))
    cmd.Parameters.Add(New SqlParameter("@FieldValueOriginal", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Original, Nothing))
    cmd.Parameters.Add(New SqlParameter("@RowIDCurrent", SqlDbType.Int, 4, ParameterDirection.Input, False, 10, 0, "RowID", DataRowVersion.Current, Nothing))

    da.InsertCommand = da.SelectCommand.Connection.CreateCommand()
    cmd = da.InsertCommand
    cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
    cmd.CommandText = "INSERT INTO [Test] ([SKU], [FieldName], [FieldValue]) VALUES (@SKU, @FieldName, @FieldValue); SELECT * FROM Test WHERE [RowID] = SCOPE_IDENTITY()"
    cmd.Parameters.Add(New SqlParameter("@SKU", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Current, Nothing))
    cmd.Parameters.Add(New SqlParameter("@FieldName", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Current, Nothing))
    cmd.Parameters.Add(New SqlParameter("@FieldValue", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Current, Nothing))

    da.DeleteCommand = da.SelectCommand.Connection.CreateCommand()
    cmd = da.DeleteCommand
    cmd.UpdatedRowSource = UpdateRowSource.None
    cmd.CommandText = "DELETE FROM [Test] WHERE [RowID] = @RowID AND [SKU] = @SKU AND [FieldName] = @FieldName AND [FieldValue] = @FieldValue"
    cmd.Parameters.Add(New SqlParameter("@RowID", SqlDbType.Int, 4, ParameterDirection.Input, False, 10, 0, "RowID", DataRowVersion.Original, Nothing))
    cmd.Parameters.Add(New SqlParameter("@SKU", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Original, Nothing))
    cmd.Parameters.Add(New SqlParameter("@FieldName", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Original, Nothing))
    cmd.Parameters.Add(New SqlParameter("@FieldValue", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Original, Nothing))

    Return da
End Function

Select all Open in new window

I just tested your code and it works if you add the following after the ExecuteNonQuery call:

intRowCount += cmd.ExecuteNonQuery()

dr.Item("RowID") = cmd.Parameters("@RowID").Value

So, wouldn't you think the sqlcommand parameter logic would put the value back in since it easily takes it out when going the other direction for the input values?  That, I guess, is the fundamental question that I'm having.  The logic I sent you that works just fine doesn't do that, but that code works.  Moreover, the code I sent you from the automatic code builder doesn't even reference the datarow or datatable as the last argument to the sqlparameters instantiation.

The Enterprise Library uses a DBDataAdapter (the SQLDataAdapter is a DBDataAdapter).

Here is a description of what happens when the update is called:
DbDataAdapter.Update
http://msdn.microsoft.com/en-us/library/z1z2bkx2.aspx

You can see that the rows in the table are updated in step 4 and 5.

The SQLCommand does not have this functionality.