Hi,

I would like to know how to retain the object property value in visual basic 6.0 when COM+ transaction object either SetComplete is invoked?


Case study with sample code:
1. Create a class to store the property values, called clsOprPropertiesBag.
---------------------------------------------------------------------------------------------
‘clsOprPropertiesBag.cls to store UserName and Password
Option Explicit
Private mstrUserName As String
Public Sub SetLoginBag(ByVal vstrUserName As String, Optional ByVal vstrPassword As String)
Me.UserName = vstrUserName
Me.Password = vstrPassword
End Sub
'UserName
Public Property Get UserName() As String
UserName = mstrUserName
End Property
Public Property Let UserName(ByRef vstrUserName As String)
mstrUserName = vstrUserName
End Property
'Password
Public Property Get Password() As String
Password = mstrPassword
End Property
Public Property Let Password(ByRef vstrPassword As String)
mstrPassword = vstrPassword
End Property
------------------------------------------------------------------------------------------

2. The clsInterfaceUser Class in data access layer Implements the ObjectControl and declare ObjectContext for com+ use. A Public module level object mobjOperator is declared and set instance of clsOprPropertiesBag when initialize the class.
-----------------------------------------------------------------------------------------
‘clsInterfaceUser
Implements ObjectControl
Private mobjCtx As ObjectContext
Public mobjOperator As clsOprPropertiesBag

Private Sub Class_Initialize()
Set mobjOperator = CreateObject("clsOprPropertiesBag")
End Sub
------------------------------------------------------------------------------------------

3. Add in standard MTS coding into clsInterfaceUser.
--------------------------------------------------------------------------------------
Private Sub ObjectControl_Activate()
Set mobjCtx = GetObjectContext()
End Sub
Private Function ObjectControl_CanBePooled() As Boolean
ObjectControl_CanBePooled = False
End Function
Private Sub ObjectControl_Deactivate()
Set mobjCtx = Nothing
End Sub
----------------------------------------------------------------------------------------------

4. Assume the clsInterfaceUser has two functions, namely GenerateSQL and OpenRecordSet.
----------------------------------------------------------------------------------------------
‘GenerateSQL
Public Function GenerateSQL(…) As String
Dim strSQL As String
If Len(Trim(strProperties)) > 0 Then
strSQL = IIf(blnDistinct, "SELECT DISTINCT " & strProperties & " FROM " & strDataSource, _
"SELECT " & strProperties & " FROM " & strDataSource)
Else
strSQL = IIf(blnDistinct, "SELECT DISTINCT * FROM " & strDataSource, "SELECT * FROM " & strDataSource)
End If
If Len(Trim(strFilter)) > 0 Then
strSQL = strSQL & " WHERE " & strFilter
End If
If Len(Trim(strGroup)) > 0 Then
strSQL = strSQL & " GROUP BY " & strGroup
End If
If Len(Trim(strSort)) > 0 Then
strSQL = strSQL & " ORDER BY " & strSort
End If
GenerateSQL = strSQL
If gblnCOMPLUSFlag Then
mobjCtx.SetComplete
End If
End Function

‘OpenRecordSet
Public Function OpenRecordset(…) As ADODB.Recordset
Dim rstQuery As ADODB.Recordset
Dim strConnection As String
Dim cnnQuery As ADODB.Connection
Dim strSQLStmt As String
Const cstrIsolationDirtyRead As String = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
mstrConnect = GetDBConnectString(vstrThisPropertyCode)
strConnection = mstrConnect
Set rstQuery = New ADODB.Recordset
With rstQuery
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.CursorType = adOpenStatic
End With
Set cnnQuery = New ADODB.Connection
With cnnQuery
.ConnectionTimeout = 150 'default 15
.CommandTimeout = 300 'default 30
.CursorLocation = adUseClient
If cnnQuery.State = adStateOpen Then
cnnQuery.Close
End If
.Open strConnection
Set rstQuery = cnnQuery.Execute(strSQLStmt)
End With
Set rstQuery.ActiveConnection = Nothing
Set OpenRecordset = rstQuery
Set rstQuery = Nothing
If cnnQuery.State = adStateOpen Then
cnnQuery.Close
End If
Set cnnQuery = Nothing
If gblnCOMPLUSFlag Then
mobjCtx.SetComplete
End If
End Function
----------------------------------------------------------------------------------------------

5. Assume a business layer create the instance of clsInterfaceUser called objInterfaceUserDAL and assign the mobjOperator property User Name to “htkok”.
----------------------------------------------------------------------------------------------
Set objInterfaceUserDAL = CreateObject("clsInterfaceUser")
Set objInterfaceUserDAL.mobjOperator.SetLoginBag(“ht kok”)
----------------------------------------------------------------------------------------------

6. The problem is when calling two functions, namely GenerateSQL and OpenRecordSet, the mobjOperator property User Name of the second function will become empty string.
------------------------------------------------------------------------------------------------
strSql = objInterfaceUserDAL.GenerateSQL(…) à UserName = “htkok”
Set rs = objInterfaceUserDAL.OpenRecordset(…) à UserName = “” [Empty]
----------------------------------------------------------------------------------------------

7. The situation only happen when the gblnCOMPLUSFlag flag set to True. If the flag set to true, SetComplete and SetAport will be process.
----------------------------------------------------------------------------------------------
If gblnCOMPLUSFlag Then
mobjCtx.SetComplete
End If
--------------------------------------------------------------------------------------------

Hello,

AS OF MY KNOWLEDGE,

When you use, SetComplete, the object is deactivited. So it loses its state.

So, what I say is, at the POINT:6 (what you have)

A> strSql = objInterfaceUserDAL.GenerateSQL(…) à UserName = “htkok”
B> Set rs = objInterfaceUserDAL.OpenRecordset(…) à UserName = “” [Empty]

A, is needed before Executing B. So why dont you call A in the B itsself
and try to USE "SetComplete" once by mixing both of them. Here after executing
A, you are calling SetComplete and in B also you are calling SetComplete, I guess
its like a over work on MTS,

Try making it as a batch, and mix up A and B, USE one SetComplete, and check,
might be this will help in fixing.

FOR EX:-
Consider you open a connection object with DB
then you will have to fetch the data from DB,
So you use Recordset, then you will have some
data need to inserted into 2 or three tables
so you use the same connection object with
command object. Here you finish up the work
and finally you close the connection.

Its not like open the connection for fetching
the data, then close it then open again to
insert the data to one table and close it
and open it again for inserting to another
table... and so on.

Its like a batch, open ones finish up the
work and close it.

So do the same with mtsobject, open once
get all the data (what ever you want, i mean
input params,) call A and B one after the other
and use that "SetComplete" once. (like a batch
execution)

This was just an fare idea of mine.

Hope this helps.

With Regards,
Raghavendra Mudugal

Thanks for you opinion.
Actually what I am showing is just part of my system.
Besides of the code shown, still got few part cause the same problem.
It is imposible for me to restudy whole system again and modify the code.

Anyway, I found a solution from Microsoft by using Share Property Group.
http://support.microsoft.com/kb/191235/en-us
http://support.microsoft.com/kb/267844/en-us

The share property group quite useful, but it usage is for share static property. What I need is dynamic property.
Now still facing problem on how to dynamically set the share property group for different section.

Hope someone can give me an answer.

Thanks.

Okay, GOTCHA !!!

Chech this link
http://www.geocities.com/raghavendra_mudugal/mtschm.zip
(its an help on MTS SDK, which has two files .chm and .chi)

Open that .CHM file, in menu, select "GO" and "URL..."
a window appears which has a textbox in there. Copy the below
url and click "OK"
=========
mk:@MSITStore:C:\Program%20Files\Microsoft%20Visua l%20Studio\MSDN\2001JUL\1033\MTS.Chm::/hh/html/mtxpg08vb_4hnp.htm
=========

It will take to the "Sharing State" topic. If you give a deeper look,
you may find a way to fix the problem.

Hope this helps.


With Regards,
Raghavendra Mudugal