ADO Recordset Empty After Running SP
m.p.fl...@googlemail.com
I am sort of going borderline insane with a problem I am currently
facing. I have written a stored procedure on SQL SERVER (EXPRESS) which
takes 3 parameters and returns a single column. The expected returned
results is a single row. The SP runs successfully and the result is
correctly returned when I run the SP directly on the server. Using the
following code however (irrelevant bits removed) the returned recordset
is EMPTY:
***CODE START
'Connection Objects
Dim sqlConn As ADODB.Connection
Dim sqlCmd As ADODB.Command
Dim sqlRS As ADODB.Recordset
' Establish connection.
Set sqlConn = New ADODB.Connection
sqlConn.ConnectionString = CurrentProject.Connection
sqlConn.Open
sqlConn.CursorLocation = adUseClient 'Cache data locally
Set sqlCmd = New ADODB.Command
' Open recordset.
With sqlCmd
.ActiveConnection = sqlConn
.CommandText = "sp_UI_AddServiceRelationship"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ParentRId").Value =
ReturnRelationshipID(destinationnode.Key)
.Parameters("@ServiceName").Value = draggednode.Text
.Parameters("@Sequence").Value = intNewSeqID
End With
Set sqlRS = sqlCmd.Execute
'!!!! At this point sqlRS recordset should not be EMPTY FFS!!!
'Close the connection to the server
sqlConn.Close
Set sqlConn = Nothing
'Close remaining connections
sqlRS.Close
Set sqlRS = Nothing
Set sqlCmd = Nothing
***CODE END
My current solution to this problem was to write a second SP which
returns the value that 'sp_UI_AddServiceRelationship' should be
returning in the first place as follows:
***CODE START
'Connection Objects
Dim sqlConn As ADODB.Connection
Dim sqlCmd As ADODB.Command
Dim sqlRS As ADODB.Recordset
' Establish connection.
Set sqlConn = New ADODB.Connection
sqlConn.ConnectionString = CurrentProject.Connection
sqlConn.Open
sqlConn.CursorLocation = adUseClient 'Cache data locally
Set sqlCmd = New ADODB.Command
' Open recordset.
With sqlCmd
.ActiveConnection = sqlConn
.CommandText = "sp_UI_AddServiceRelationship"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ParentRId").Value =
ReturnRelationshipID(destinationnode.Key)
.Parameters("@ServiceName").Value = draggednode.Text
.Parameters("@Sequence").Value = intNewSeqID
End With
Set sqlRS = sqlCmd.Execute
'!!!! At this point sqlRS recordset should not be EMPTY FFS!!!
With sqlCmd
.ActiveConnection = sqlConn
.CommandText = "sp_UI_GetLastAddedRID"
.CommandType = adCmdStoredProc
End With
Set sqlRS = sqlCmd.Execute
'!!!! sqlRS recordset now contains the required data!!!
'Close the connection to the server
sqlConn.Close
Set sqlConn = Nothing
'Close remaining connections
sqlRS.Close
Set sqlRS = Nothing
Set sqlCmd = Nothing
***CODE END
Could anyone be so kind and point out the problem??
Kind regards,
Fletch