I am trying to use transaction to archive a data, but I receive such error:
‘Connection property has not been initialized’.
It seems that the line:
cmd.Transaction = trn

did not work. What needs to be changed?


Dim cnn As SqlClient.SqlConnection = _
New SqlClient.SqlConnection(SQLCONNECTIONSTRING)
cnn.Open()

Dim trn As SqlClient.SqlTransaction = cnn.BeginTransaction
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand()
Dim prm As SqlClient.SqlParameter

Try
' Start transaction
cmd.Transaction = trn
cmd.CommandText = "procArhivingPurchases"
cmd.CommandType = CommandType.StoredProcedure
prm = cmd.Parameters.Add("@Date", _
SqlDbType.VarChar, 25)
prm.Value = ConvertDateTime(GetYearToArchive(), GetMonthToArchive())

cmd.ExecuteNonQuery()


trn.Commit()
MsgBox("Successfully archived all data", MsgBoxStyle.Information)
Catch ex As Exception
trn.Rollback()
MsgBox(ex.ToString, MsgBoxStyle.Exclamation, "Failed to archive the data")
Finally

If cnn.State <> ConnectionState.Closed Then
cnn.Close()
End If

cnn = Nothing
trn = Nothing
cmd = Nothing
prm = Nothing
End Try