Solved

SqlCommand not updating output parameters

Posted on 2010-11-30
11
665 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Disable extension 8 37
replicated - directional or bidirectional? 3 29
VB.net and sql server 4 33
Convert datetime to time string 10 19
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

786 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