Error while using SQLTransaction

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