[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Idenitity Value during a SQL transaction

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()
        connection.Open()
        Dim transaction As IDbTransaction = connection.BeginTransaction()

        Try
            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(accountBean.AccountName)
            sqlCommand.Append("', ")
            sqlCommand.Append(accountBean.NmdId)
            sqlCommand.Append(", '")
            sqlCommand.Append(createUserId)
            sqlCommand.Append("', ")
            sqlCommand.Append(" GETDATE() ")
            sqlCommand.Append(", '")
            sqlCommand.Append(createUserId)
            sqlCommand.Append("', ")
            sqlCommand.Append(" GETDATE() ")
            sqlCommand.Append(")")

            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)
            Next

            '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


            transaction.Commit()
            Return nextAccountId
        Catch ex As Exception

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


Thanks,

Brian
0
u7c76
Asked:
u7c76
1 Solution
 
Bob LearnedCommented:
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
 Try
   myConnection.Open
   myTransaction = myConnection.BeginTransaction
   myCommand.Transaction = myTransaction
   myCommand.ExecuteNonQuery
   Dim returnValue As Integer = CType(myCommand.Parameters("@ReturnValue").Value, Integer)
   If returnValue <= 0 Then
     Throw newArgumentOutOfRangeException("Value not inserted.")
   End If
   myCommand.Parameters.Clear
   myCommand.CommandText = SP_INSERT_USER_ROLE
   myCommand.CommandType = CommandType.StoredProcedure
   myCommand.Parameters.AddWithValue("@UserID", returnValue)
   myCommand.ExecuteNonQuery
   result = True
 Catch ex As Exception
   Dim exception As String = ex.Message
   myTransaction.Rollback
 Finally
   myTransaction.Commit
   myConnection.Close
   myCommand.Dispose
 End Try
 Return result
End Function

Bob
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now