VB6 Stored proc problem :-(

NutJob

Ok heres some over simplified sample code code of my problem

  SELECT @CMD = ' SELECT user from Users'
  EXECUTE( @CMD)

Works great returns 3 records. Works in crystal reports job done i thought


Here's the problem i try the following and i haven't touched vb6 in years.

    Dim sSQL As String
    Dim oCmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

    With oCmd
        .ActiveConnection = dbCon                               'Allow the command object to use the currently open db connection
        .CommandType = adCmdStoredProc                          'Set the command type to be the stored procedure
        .CommandText = "Rpt__STORED_PROC"      'Set the stored procedure to be used
        .Parameters.Append .CreateParameter("@SearchType", adVarChar, adParamInput, 50, SearchType)
    
       
    Set rs = .Execute               
    End With
    
    'rs.Open oCmd, ,adOpenForwardOnly,adLockReadOnly,
    'rs.Open oCmd,,adOpenStatic,adLockReadOnly,


     'rs.CursorLocation = adUseClient
     'rs.Open sSQL, dbCon, adOpenForwardOnly, adLockReadOnly
    
    'Set rs.ActiveConnection = Nothing
    Set GetFUMRSearchResults = rs

Any ideas why vb returns a closed recordset. i cant even get a snapshot and iv re-jigged the code a few times now

Evil Phil

I'm assuming SearchType is a parameter passed into the function, if its not then that might be your problem. I can't remember vb6 either but if you have sql profiler you may want to look at what the call is doing on the DB side.

NutJob

Took yesterday off sick:(



The solution.
http://support.microsoft.com/default.aspx/kb/235340

Added following to stored proc

SET NOCOUNT ON

bonkey

Glad you found your solution.

You'll notice, incidentally, that the "simplified" example you gave shouldn't actually cause this problem.