S
Syed Mudussir Husain
Hello.
We are developing a web application using ASP 3.0, VB6 (SP5), ADO 2.5 and
MSSQL2000 (SP2). Middle-tier consists of a MTS-based dll that is deployed as
component services under Windows 2000 server.
The application runs smoothly without any error when I test it with single
user. But when multiple users access it, numerous runtime and MTS errors
start to emerge. Most of the errors are caused by one problem which disturbs
entire flow of execution.
It happens when middle-teir dll executes a stored procedure and access an
output parameter. The stored procedure executes sucessfully and returns a
recordset and an output parameter. When dll retrieves the value output
parameter, VB raises runtime error 'Invalid use of null' which indicates the
output parameter is either not properly set or ADO is unable to access its
value.
I am using client-side static recordset. Stored procedure is executed
through ADO command object where I set input and output parameters. Stored
procedure is executed under MTS transaction and hit several times during
application usage.
Here is the code snippet.,
'Command properties are set here.
cmd.ActiveConnection = <connection_string>
cmd.CommandText = <stored_procedure>
cmd.CommandTimeout = 30
cmd.ActiveConnection.Execute "SET TRANSACTION ISOLATION LEVEL READ
COMITTED"
cmd.ActiveConnection.Execute "SET LOCK_TIMEOUT 1"
'Recordset properties are set here.
rstTemp.CursorLocation = adUseClientCursor
rstTemp.CursorType = adOpenStatic
rstTemp.LockType = adLockBatchOptimistic
rstTemp.CacheSize = 10
'Here input and output parameters are set.
cmd.Parameters.Append cmd.CreateParameter("@pTranRefNo", adVarChar,
adParamInput, 16, strTransRefNo)
cmd.Parameters.Append cmd.CreateParameter("@pRetVal", adInteger,
adParamOutput, 4)
'Stored procedure is called sucessfully.
rstTemp.Open cmd
'At this point, I get 'Invalid use of Null' error when multiple users
are using the application. It works fine when run with
'single user or in debugging mode.
lResult = CLng(cmd.Parameters("@pRetVal").Value)
'Connection is destroyed.
Set cmd.ActiveConnection = Nothing
This is a weired problem. I am unable to find anything useful from MSDN.
Although I have collected some articles from net that show the behaviour of
ADO when it reads output parameters. They indicate two things.,
1. If you are using server-side cursor then it must be closed before output
parameters can be
read. It is not required in case of client-side cursor. I am using
client-side cursor.
2. ADO connection properties collection contains a property named 'Output
Parameter Availability'
which indicates at what stage output parameters are available. It can
have one of following
values i.e.,
1 - Indicates that output parameters are not supported.
2 - Indicates that parameters are available after command has been
executed.
4 - Indicates that parameters are available after recordset has been
closed.
When I checked this property after opening the connection, it returned 4
which means that we can read output parameters only when recordset has been
closed. But we are not doing that and still able to read output parameter
when running the application with single-user or in debugging mode.
If anyone has experienced and resolved such a problem, please help me out!!!
Thanks
We are developing a web application using ASP 3.0, VB6 (SP5), ADO 2.5 and
MSSQL2000 (SP2). Middle-tier consists of a MTS-based dll that is deployed as
component services under Windows 2000 server.
The application runs smoothly without any error when I test it with single
user. But when multiple users access it, numerous runtime and MTS errors
start to emerge. Most of the errors are caused by one problem which disturbs
entire flow of execution.
It happens when middle-teir dll executes a stored procedure and access an
output parameter. The stored procedure executes sucessfully and returns a
recordset and an output parameter. When dll retrieves the value output
parameter, VB raises runtime error 'Invalid use of null' which indicates the
output parameter is either not properly set or ADO is unable to access its
value.
I am using client-side static recordset. Stored procedure is executed
through ADO command object where I set input and output parameters. Stored
procedure is executed under MTS transaction and hit several times during
application usage.
Here is the code snippet.,
'Command properties are set here.
cmd.ActiveConnection = <connection_string>
cmd.CommandText = <stored_procedure>
cmd.CommandTimeout = 30
cmd.ActiveConnection.Execute "SET TRANSACTION ISOLATION LEVEL READ
COMITTED"
cmd.ActiveConnection.Execute "SET LOCK_TIMEOUT 1"
'Recordset properties are set here.
rstTemp.CursorLocation = adUseClientCursor
rstTemp.CursorType = adOpenStatic
rstTemp.LockType = adLockBatchOptimistic
rstTemp.CacheSize = 10
'Here input and output parameters are set.
cmd.Parameters.Append cmd.CreateParameter("@pTranRefNo", adVarChar,
adParamInput, 16, strTransRefNo)
cmd.Parameters.Append cmd.CreateParameter("@pRetVal", adInteger,
adParamOutput, 4)
'Stored procedure is called sucessfully.
rstTemp.Open cmd
'At this point, I get 'Invalid use of Null' error when multiple users
are using the application. It works fine when run with
'single user or in debugging mode.
lResult = CLng(cmd.Parameters("@pRetVal").Value)
'Connection is destroyed.
Set cmd.ActiveConnection = Nothing
This is a weired problem. I am unable to find anything useful from MSDN.
Although I have collected some articles from net that show the behaviour of
ADO when it reads output parameters. They indicate two things.,
1. If you are using server-side cursor then it must be closed before output
parameters can be
read. It is not required in case of client-side cursor. I am using
client-side cursor.
2. ADO connection properties collection contains a property named 'Output
Parameter Availability'
which indicates at what stage output parameters are available. It can
have one of following
values i.e.,
1 - Indicates that output parameters are not supported.
2 - Indicates that parameters are available after command has been
executed.
4 - Indicates that parameters are available after recordset has been
closed.
When I checked this property after opening the connection, it returned 4
which means that we can read output parameters only when recordset has been
closed. But we are not doing that and still able to read output parameter
when running the application with single-user or in debugging mode.
If anyone has experienced and resolved such a problem, please help me out!!!
Thanks