Solved

SqlCommand not updating output parameters

Posted on 2010-11-30
11
660 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

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…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now