SqlCommand not updating output parameters

Why doesn't the following code snippet work?  By work, I mean, update the 10 datarows in the dattatable with the identity values when the record was inserted into the database?

Thanks

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' This test app needs a sql database on the localhost named 'Test' and a table named 'Test' created with the following script:

        'CREATE TABLE [dbo].[Test]
        '    (
        '      [RowID] [int] IDENTITY(1, 1)
        '                    NOT NULL,
        '      [SKU] [char](10) NOT NULL,
        '      [FieldName] [varchar](20) NOT NULL,
        '      [FieldValue] [varchar](200) NOT NULL,
        '      CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [RowID] ASC )
        '    )

        Dim intRowCount As Integer

        Dim dt As New DataTable("Test")
        dt.Columns.Add("RowID", GetType(Integer))
        dt.Columns.Add("SKU", GetType(String)).MaxLength = 10
        dt.Columns.Add("FieldName", GetType(String)).MaxLength = 20
        dt.Columns.Add("FieldValue", GetType(String)).MaxLength = 200
        dt.PrimaryKey = New DataColumn() {dt.Columns("RowID")}
        dt.Columns("RowID").AutoIncrement = True
        dt.Columns("RowID").AutoIncrementSeed = -1
        dt.Columns("RowID").AutoIncrementStep = -1

        For intIndex As Integer = 1 To 10

            Dim dr As DataRow = dt.NewRow
            dr("SKU") = "SKU" & intIndex.ToString
            dr("FieldName") = "FieldName" & intIndex.ToString
            dr("FieldValue") = "FieldValue" & intIndex.ToString
            dt.Rows.Add(dr)

        Next

        Using conn As New SqlConnection("server=localhost;database=test;integrated security=yes;")

            Dim cmd As SqlCommand = conn.CreateCommand
            cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

            conn.Open()
            For Each dr As DataRow In dt.Rows

                cmd.Parameters.Clear()
                cmd.CommandText = "INSERT INTO Test (SKU, FieldName, FieldValue) VALUES (@SKU, @FieldName, @FieldValue); SELECT @RowID = @@IDENTITY;"

                cmd.Parameters.Add(New SqlParameter("@SKU", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Current, dr("SKU")))
                cmd.Parameters.Add(New SqlParameter("@FieldName", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Current, dr("FieldName")))
                cmd.Parameters.Add(New SqlParameter("@FieldValue", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Current, dr("FieldValue")))
                cmd.Parameters.Add(New SqlParameter("@RowID", SqlDbType.Int, 4, ParameterDirection.Output, False, 10, 0, "RowID", DataRowVersion.Current, dr("RowID")))

                intRowCount += cmd.ExecuteNonQuery()

            Next

            For Each dr As DataRow In dt.Rows

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

            Next

        End Using

    End Sub

Open in new window

LVL 1
dpbouchardAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Erick37Connect With a Mentor Commented:
I would speculate that the dataadapter knows what to do internally because you have specified that it is an insert (da.InsertCommand) and the table has an identity column.  The logic internally is matching up the returned id to the local row's id and updating it for you.

We use the Microsoft Enterprise Library for data connections and I will look there to see how they implement the UpdateDataset calls...
0
 
HumpdyCommented:
ListBox1.Items.Add(dr("@RowID").ToString())

you shouldn't use @@identity
use
scope_identity instead
0
 
dpbouchardAuthor Commented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Go Premium
 
Erick37Commented:
Output parameters will not be available until the connection is closed.

You must read them after the "End Using" statement.
0
 
dpbouchardAuthor Commented:
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...
0
 
dpbouchardAuthor Commented:
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.
0
 
Erick37Commented:
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.
0
 
dpbouchardAuthor Commented:
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

Open in new window

0
 
Erick37Commented:
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
0
 
dpbouchardAuthor Commented:
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.
0
 
Erick37Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.