locked
Error while using SQLTransaction RRS feed

  • Question

  • Hi All,

     

    I am accessing my sqlserver 2000 database from vb.net 2005.

     

    In my code I have used the SQLTransactions class for maintaining the transactions.

     

    When executing the query it shows the following error.

     

    "ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

     

    My Code is:

     

    Private Sub openConnection()

    CON = New SqlConnection(CONSTR)

    CMD = New SqlCommand

    CON.Open()

    End Sub

     

    Private Sub closeConnection()

    If CON.State = ConnectionState.Open Then

    CON.Close()

    End If

    CON.Dispose()

    CMD.Dispose()

    CON = Nothing

    CMD = Nothing

    End Sub

     

    Public Function saveMain(ByVal MainData As DataTable, ByVal FlowMode As String) As Boolean

    Try

    openConnection()

    Dim lngMainId As Long

    Dim dr As DataRow = MainData.Rows(0)

    TRN = CON.BeginTransaction

    If FlowMode = "Add" Then

    lngMainId = generateAutoCode("select isnull(max(main_id),0) as main_id from tb_Scope_Main")

    CMD.CommandText = "insert into tb_Scope_Main (Main_id, Main_Ref, Title, Detailed_Description, " _

    & "Currentdate, Currentstatus) values (" & lngMainId & ", '" & Convert.ToString(dr(1)) _

    & "', '" & Convert.ToString(dr(2)) & "', '" & Convert.ToString(dr(3)) & "', '" _

    & Convert.ToString(dr(4)) & "', " & Convert.ToString(dr(5)) & ")"

    CMD.Connection = CON

    CMD.ExecuteNonQuery()

    ElseIf FlowMode = "Edit" Then

    lngMainId = Convert.ToInt64(dr(0))

    CMD.CommandText = "update tb_Scope_Main set Main_id= " & lngMainId & ", Main_Ref='" _

    & Convert.ToString(dr(1)) & "', Title='" & Convert.ToString(dr(2)) _

    & "', Detailed_Description='" & Convert.ToString(dr(3)) & "', Currentdate='" _

    & Convert.ToString(dr(4)) & "', Currentstatus = " & Convert.ToString(dr(5)) _

    & " from tb_Scope_Main where Main_id = " & lngMainId

    CMD.Connection = CON

    CMD.ExecuteNonQuery()

    End If

    TRN.Commit()

    saveMain = True

    Catch ex As Exception

    MsgBox(ex.Message)

    saveMain = False

    TRN.Rollback()

    Finally

    closeConnection()

    End Try

    End Function

     

    when i execute the same with out "TRN", it works fine. error comes from the line CMD.ExecuteNonQuery()

     

    Please suggest me to solve this problem.

     

    Thanks in advance.

     

    dhana
    Tuesday, June 10, 2008 5:01 AM

Answers

  • you must specifically set the transaction to the command object. i don't know VB so well, but it should be something like this:

    Code Snippet
    myCommand = New SqlCommand(String, SqlConnection, SqlTransaction)

     

     

     

    see the following link for detalis about using this SqlCommand constructor: http://msdn.microsoft.com/en-us/library/352y4sff.aspx

     

    Tuesday, June 10, 2008 11:48 AM