Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error while retrieving @@Identity from SQL server

Posted on 2004-10-06
5
Medium Priority
?
274 Views
Last Modified: 2008-02-01
Please help, the program work fine without the "Private Sub sqlTransMtrDa_RowUpdated" procedure.
How when the procedure is existed, the following error will occurs!!!
The error message is :
"Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized."

Can you tell me why and how to solve this problem ?

Thanks in advance!

Imports System.Data.SqlClient
Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim SQLcnn As SqlConnection
    Dim SQLCnnStr As String
    Dim objTx As SqlTransaction
    Dim SqlDS As New DataSet
    Dim objCmdBuilder As SqlCommandBuilder
    Dim WithEvents sqlTransMtrDa As SqlDataAdapter

#Region " Windows Form Designer generated code "

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SQLCnnStr = "Persist Security Info=False" & _
           ";User ID=xxx" & _
           ";Password=xxx" & _
           ";Initial Catalog=xxx" & _
           ";Data Source=xxx" & _
           ";Connect Timeout=30"
        SQLCnn = New SqlClient.SqlConnection
        SQLCnn.ConnectionString = SQLCnnStr
        SQLcnn.Open()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim TxNo As String

        sqlTransMtrDa = New SqlDataAdapter("SELECT * FROM trans_mast", SQLcnn)

        objCmdBuilder = New SqlCommandBuilder(sqlTransMtrDa)
        sqlTransMtrDa.Fill(SqlDS, 1, 1, "trans_mast")

        sqlTransMtrDa.InsertCommand = objCmdBuilder.GetInsertCommand
        sqlTransMtrDa.UpdateCommand = objCmdBuilder.GetUpdateCommand
        sqlTransMtrDa.DeleteCommand = objCmdBuilder.GetDeleteCommand

        objTx = SQLcnn.BeginTransaction(IsolationLevel.Serializable)
        sqlTransMtrDa.InsertCommand.Transaction = objTx
        sqlTransMtrDa.UpdateCommand.Transaction = objTx
        sqlTransMtrDa.DeleteCommand.Transaction = objTx

        Try
            With SqlDS.Tables("trans_mast")
                Dim dr As DataRow = .NewRow

                dr.Item("comp_code") = "xxx"
                dr.Item("tx_type") = "INV"
                .Rows.Add(dr)
                sqlTransMtrDa.Update(SqlDS, "trans_mast")
            End With
            objTx.Commit()
            MsgBox("Updated")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub sqlTransMtrDa_RowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles sqlTransMtrDa.RowUpdated
        If (e.StatementType = StatementType.Insert) Then
            Dim cmd As New SqlCommand("SELECT @@IDENTITY", SQLcnn)

            e.Row.Item("tx_no") = cmd.ExecuteScalar() ' <--------------- Error Occurs Here
            e.Row.AcceptChanges()
        End If

    End Sub
End Class
0
Comment
Question by:vbhaha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12244977
The SQLConnection is working with the objTx transaction, and if the transaction has not made a commit or rollback the execution of the transaction still executing itself.

You must use another connection, or you can set the transaction of the cmd command in your sqlTransMtrDa_RowUpdated event to objTx
0
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12244982
I hope this could help you :)
0
 

Author Comment

by:vbhaha
ID: 12245031
eozz_2000, although it can work after changing the connection, I cannot get the "Identity" number, in fact it returned "null" !
0
 
LVL 4

Accepted Solution

by:
eozz_2000 earned 1500 total points
ID: 12245118
Declare de objTx transaction in the properties of the class (out of any method).

And then in the RowUpdated event make this:

If e.StatementType = StatementType.Insert Then
  Dim cmd As New SqlCommand("SELECT @@IDENTITY", Me.conn, Me.objTx)
  MsgBox(cmd.ExecuteScalar)
End If
0
 

Author Comment

by:vbhaha
ID: 12245225
eozz_2000

It works!

In addition, I have changed the dim statement into :

Dim cmd As New SqlCommand("SELECT @@IDENTITY", e.Command.Connection, e.Command.Transaction)

So that I don't have to worry about the connection and transaction object.

Anyway thanks for your help.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

705 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