VB Forum / Database Access / August 2005

Hallo,

I call a SQL Server 2000 SP3 stored procedure in asynchronous mode from my
VB6 application. After completen of the sp I receive an empty recordset in
my client, although the sp returns data. I checked this in Query Analyzer
with the same paraemeters.

*** this is the asynchronous call ***
 Dim cParam As Parameter

 'create command
 Set m_oCmd = New Command
 Set m_oCmd.ActiveConnection = m_oConn
 m_oCmd.CommandText = spCOR_WERTKARTEN_AUFTRAGSDATEN
 m_oCmd.CommandType = adCmdStoredProc
 m_oCmd.CommandTimeout = 0

 'insert parameters
 Set cParam = m_oCmd.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue)
 m_oCmd.Parameters.Append cParam
 'Abgrenzungsdatum
 Set cParam = m_oCmd.CreateParameter(, adDate, adParamInput, ,
DateValue(DTPAktion.Value))
 m_oCmd.Parameters.Append cParam
 'min Konto-PK
 Set cParam = m_oCmd.CreateParameter(, adInteger, adParamInput, ,
CInt(Val(txtMinKonto_PK.Text)))
 m_oCmd.Parameters.Append cParam
 'max Konto-PK
 Set cParam = m_oCmd.CreateParameter(, adInteger, adParamInput, ,
CInt(Val(txtMaxKonto_PK.Text)))
 m_oCmd.Parameters.Append cParam

 ' execute command
 m_oCmd.Execute , , adAsyncExecute

*** event handling procedure, where the sp returns ***
Private Sub m_oConn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal
pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As
ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As
ADODB.Connection)
 If pCommand.CommandText Like ("*" & spCOR_WERTKARTEN_AUFTRAGSDATEN & "*")
Then
   ...
 End If
End Sub

*** it works with this synchronous call and I get the data ***
Dim oConn As New ADODB.Connection
Dim vRet As Variant
oConn.Open m_oConn.ConnectionString
oConn.CommandTimeout = 0
vRet = Execute_SQL(oConn, "exec Cor_Wertkarten_Auftragsdaten_macx_sp
'23.12.2002', 25000, 25010 ")
oConn.Close

Private Function Execute_SQL(oConn As Connection, stSQL As String) As
Variant
 Dim oCmd As Command
 Dim oRS As Recordset
 Set oCmd = New Command
 Set oCmd.ActiveConnection = oConn
 oCmd.CommandText = stSQL
 oCmd.CommandType = adCmdText
 Set oRS = oCmd.Execute
 If Not oRS Is Nothing Then
   If oRS.State <> 0 Then
     If Not oRS.EOF And Not oRS.BOF Then
       Execute_SQL = oRS.GetRows
       oRS.Close
       Set oRS = Nothing
     End If
   End If
 End If
End Function

Does anybody has a hint for me or knows this problem?

Thanks
Ralf