Solved

SqlCommand not updating output parameters

Posted on 2010-11-30
11
666 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:dpbouchard
  • 5
  • 5
11 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34239675
ListBox1.Items.Add(dr("@RowID").ToString())

you shouldn't use @@identity
use
scope_identity instead
0
 
LVL 1

Author Comment

by:dpbouchard
ID: 34239750
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34239809
Output parameters will not be available until the connection is closed.

You must read them after the "End Using" statement.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:dpbouchard
ID: 34239841
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
 
LVL 1

Author Comment

by:dpbouchard
ID: 34239889
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34240119
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
 
LVL 1

Author Comment

by:dpbouchard
ID: 34240248
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34240321
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
 
LVL 1

Author Comment

by:dpbouchard
ID: 34240548
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
 
LVL 32

Accepted Solution

by:
Erick37 earned 500 total points
ID: 34240980
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34241209
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question