Executing command causes Access adp to lock!

[Zirak]

Hi,
I have a VB code that executes a parametrized stored procedure. The SP has one input and several output parameters. The SP works fine in SQL (Query Analyzer), but when I run it through code, MS Access hangs.
The funny thing is that the same module worked for a while, but suddenly it started locking.

Here is the Code:
=================================

Dim cnn1 As ADODB.Connection
Dim cmd As ADODB.Command
Dim PrimaryNumber As String
Dim PreDirectional As String
Dim StreetName As String
Dim Suffix As String
Dim PostDirectional As String
Dim prmStreetAdrs As ADODB.Parameter
Dim prmPrimaryNumber As ADODB.Parameter
Dim prmPreDirectional As ADODB.Parameter
Dim prmStreetName As ADODB.Parameter
Dim prmSuffix As ADODB.Parameter
Dim prmPostDirectional As ADODB.Parameter
Dim prmEncounteredError As ADODB.Parameter




Set cnn1 = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn1
.CommandText = "dbo.SP_SeperateStreetAdrs"
.CommandType = adCmdStoredProc

Set prmStreetAdrs = .CreateParameter("@StreetAdrs", adVarChar, adParamInput, 1000, StreetAdrs)
.Parameters.Append prmStreetAdrs

Set prmPrimaryNumber = .CreateParameter("@PrimaryNumber", adVarChar, adParamOutput, 50)
.Parameters.Append prmPrimaryNumber

Set prmPreDirectional = .CreateParameter("@PreDirectional", adVarChar, adParamOutput, 50)
.Parameters.Append prmPreDirectional

Set prmStreetName = .CreateParameter("@StreetName", adVarChar, adParamOutput, 200)
.Parameters.Append prmStreetName

Set prmSuffix = .CreateParameter("@Suffix", adVarChar, adParamOutput, 50)
.Parameters.Append prmSuffix

Set prmPostDirectional = .CreateParameter("@PostDirectional", adVarChar, adParamOutput, 50)
.Parameters.Append prmPostDirectional

Set prmEncounteredError = .CreateParameter("@EncounteredError", adVarChar, adParamOutput, 50)
.Parameters.Append prmEncounteredError
.Execute
End With

============================



Thanks

 

[cmmrfrds]

Put a Debug.Print and look at what is happening at certain points.

.Execute
End With
Debug.Print cmd(0)
Debug.Print cmd(1)
etc...

Look in the various parameters to see the content at this point.

Do you expect the stored procedure to return a resultset? If so, you may have to return the resultset before looking at the parameters since they may not be available until after the resultset has been retrieved. You don't show what code is being executed after the cmd.execute. Also, you don't show the connection string. Are you using the ole sql server provider?

 

[Zirak]

Thanks.
i solved the problem