Idenitity Value during a SQL transaction

Posted on 2006-05-30
Last Modified: 2008-02-26
Is it possible to a get an Idenitity value during a SQL transaction?  I keep recieving the following error:
System.InvalidOperationException: The stored procedure '1' doesn't exist.
   at System.Data.SqlClient.SqlCommand.DeriveParameters(

Below is a snipplet of my code:

        Dim connection As IDbConnection = db.GetConnection()
        Dim transaction As IDbTransaction = connection.BeginTransaction()

            Dim sqlCommand As New StringBuilder
            sqlCommand.Append("Insert into TACCOUNT (ACCOUNTNAME, ")
            sqlCommand.Append("NATIONALMARKETINGDIRECTORID, ")
            sqlCommand.Append("CREATEUSERID, CREATETIMESTAMP, ")
            sqlCommand.Append("MODIFYUSERID, MODIFYTIMESTAMP) ")
            sqlCommand.Append("Values ('")
            sqlCommand.Append("', ")
            sqlCommand.Append(", '")
            sqlCommand.Append("', ")
            sqlCommand.Append(" GETDATE() ")
            sqlCommand.Append(", '")
            sqlCommand.Append("', ")
            sqlCommand.Append(" GETDATE() ")

            Dim dbCommandWrapper As DBCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand.ToString)

            'Add the account to the database
            db.ExecuteNonQuery(dbCommandWrapper, transaction)

            'command.CommandText = "SELECT @@IDENTITY"
            'Dim newCustomerID As Integer = Convert.ToInt32(db.ExecuteScalar())

            Dim identity As Integer = CType(db.ExecuteScalar(CommandType.Text, transaction, "SELECT @@IDENTITY"), Integer)

            'Dim nextAddressId As Integer = getNextAddressId(db, transaction) + 1
            'Add the addresses to database
            For i As Integer = 0 To accountBean.AddressList.Count - 1
                addAddress(accountBean.AddressList(i), db, transaction)

            'Add the office phone number
            If accountBean.OfficePhoneNumber.AreaCode.Length = 3 And _
                accountBean.OfficePhoneNumber.PrefixCode.Length = 3 And _
                accountBean.OfficePhoneNumber.ExchangeCode.Length = 4 Then

                ''nextOfficePhoneId = getNextPhoneId(db, transaction) + 1
                'addPhone(accountBean.OfficePhoneNumber, db, transaction)
            End If

            'Add the main phone number
            If accountBean.MainPhoneNumber.AreaCode.Length = 3 And _
                accountBean.MainPhoneNumber.PrefixCode.Length = 3 And _
                accountBean.MainPhoneNumber.ExchangeCode.Length = 4 Then

                ''nextMainPhoneId = getNextPhoneId(db, transaction) + 1
                'addPhone(accountBean.MainPhoneNumber, db, transaction)
            End If

            Return nextAccountId
        Catch ex As Exception

            ' Rollback transaction
            log.Error("Database Transaction rolled back due to error", ex)
            Throw ex
            If Not connection Is Nothing Then connection.Close()
        End Try


Question by:u7c76
    1 Comment
    LVL 96

    Accepted Solution

    Example using a return value (@ReturnValue):

    Public Shared Function AddUser(ByVal firstName As String, ByVal lastName As String) As Boolean
     Dim result As Boolean = False
     Dim myConnection As SqlConnection = newSqlConnection(ConnectionString)
     Dim myTransaction As SqlTransaction = Nothing
     Dim myCommand As SqlCommand = newSqlCommand(SP_INSERT_USER, myConnection)
     myCommand.CommandType = CommandType.StoredProcedure
     myCommand.Parameters.AddWithValue("@FirstName", firstName)
     myCommand.Parameters.AddWithValue("@LastName", lastName)
     myCommand.Parameters.Add("@ReturnValue", SqlDbType.Int, 4)
     myCommand.Parameters("@ReturnValue").Direction = ParameterDirection.ReturnValue
       myTransaction = myConnection.BeginTransaction
       myCommand.Transaction = myTransaction
       Dim returnValue As Integer = CType(myCommand.Parameters("@ReturnValue").Value, Integer)
       If returnValue <= 0 Then
         Throw newArgumentOutOfRangeException("Value not inserted.")
       End If
       myCommand.CommandText = SP_INSERT_USER_ROLE
       myCommand.CommandType = CommandType.StoredProcedure
       myCommand.Parameters.AddWithValue("@UserID", returnValue)
       result = True
     Catch ex As Exception
       Dim exception As String = ex.Message
     End Try
     Return result
    End Function


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video discusses moving either the default database or any database to a new volume.

    779 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

    10 Experts available now in Live!

    Get 1:1 Help Now