Command Object not returning the correct number of record

Juan Velasquez
Juan Velasquez used Ask the Experts™
on
I am using a stored procedure to populate a forms recordset.  When I execute the procedure from ssms, I get the correct number of records (in this case one record) .  When I execute it via the following code from an Access front end, all the records are returned. It must be something minor but I'm missing it and I could use another pair of eyes

Private Sub ApplyReviewStatusFilter(strSelectedReviewData As String, strSelectedStatusData As String)
Dim objCmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter


Set cnn = New ADODB.Connection

cnn.ConnectionString = cStrOLEDBConnectionString
cnn.ConnectionTimeout = 0
cnn.Open
Dim x As Integer

Set objCmd = New ADODB.Command
cnn.CursorLocation = adUseClient

    With objCmd
        .ActiveConnection = cnn
        .CommandText = "[dbo].[spRetrieveByReviewOrStatus]"
        .CommandType = adCmdStoredProc
       
        If strSelectedReviewData = "" Then
             Set prm = .CreateParameter("pReview", adVarChar, adParamInput, 500, strSelectedReviewData)
        Else
            Set prm = .CreateParameter("pReview", adVarChar, adParamInput, 100, strSelectedReviewData)
        End If
       .Parameters.Append prm
       
        If strSelectedStatusData = "" Then
            Set prm = .CreateParameter("pStatus", adVarChar, adParamInput, 100, strSelectedStatusData)
        Else
            Set prm = .CreateParameter("pStatus", adVarChar, adParamInput, 100, strSelectedStatusData)
        End If
        .Parameters.Append prm
        .Execute
       
        .Parameters.Delete ("pReview")
        .Parameters.Delete ("pStatus")
       
    End With
        Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseClient
        rst.CursorType = adOpenDynamic
        rst.Open objCmd
       
        MsgBox rst.RecordCount
       
       
   
   
   Set Me.Recordset = rst
   Me.Requery
   
 
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Set objCmd = Nothing
   


   
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Your second IF statement that creates the pStatus param doesn't make much sense, since it creates exactly the same Param regardless of the logic flow. That could be the issue, but it's hard to say without knowing what is contained in the Stored Proc.

Other than that, your code looks okay.

Author

Commented:
I went ahead and commented the second IF statement out.  I was going to use it when dealing with nulls. I'm still getting the same results as before
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Please see this:
 
That could be the issue, but it's hard to say without knowing what is contained in the Stored Proc

Obviously we don't know what's in your stored proc, so we can't really comment much further. If you can show us the SQL of that sp we might be able to help further.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Here is the code for the stored procedure

ALTER PROC [dbo].[spRetrieveByReviewOrStatus]
@Review AS varchar(max) = NULL,
@Status as varchar(max) = NULL
AS
DECLARE @Delimiter As varchar(10)
SET @Delimiter = ','
SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised]

WHERE

(
      @Review is null OR
      Review1 in (select value from dbo.parmstolist(@Review,',')) OR
      Review2 in (select value from dbo.parmstolist(@Review,',')) OR
      Review3 in (select value from dbo.parmstolist(@Review,',')) OR
      Review4 in (select value from dbo.parmstolist(@Review,',')) OR
      Review5 in (select value from dbo.parmstolist(@Review,',')) OR
      Review6 in (select value from dbo.parmstolist(@Review,',')) OR
      Review7 in (select value from dbo.parmstolist(@Review,',')) OR
      Review8 in (select value from dbo.parmstolist(@Review,','))
)
AND (
      @Status is null OR
      Status1 in (select Value from dbo.parmstolist(@Status,',')) OR
      Status2 in (select Value from dbo.parmstolist(@Status,',')) OR
      Status3 in (select Value from dbo.parmstolist(@Status,',')) OR
      Status4 in (select Value from dbo.parmstolist(@Status,',')) OR
      Status5 in (select Value from dbo.parmstolist(@Status,',')) OR
      Status6 in (select Value from dbo.parmstolist(@Status,',')) OR
      Status7 in (select Value from dbo.parmstolist(@Status,',')) OR
      Status8 in (select Value from dbo.parmstolist(@Status,','))


)

When I execute it is SSMS via the following, I retreive 340 rows which was what I expected.  When I executed via ado it is returning all 1951 records

EXEC [dbo].[spRetrieveByReviewOrStatus] 'Bankruptcy, complaints','Senior Review Complete'

Author

Commented:
It's almost as if the parameters are not being sent to the stored procedure.  That would explain why all the rows are returned
I've found the problem.  I was setting rst objcmd after the parameters had been deleted.  When I changed the code to the following, the record count came out correct


Dim objCmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter


Dim rstTest As ADODB.Recordset
Set rstTest = New ADODB.Recordset
        rstTest.CursorLocation = adUseClient
        rstTest.CursorType = adOpenDynamic


Set cnn = New ADODB.Connection

cnn.ConnectionString = cStrOLEDBConnectionString
cnn.ConnectionTimeout = 0
cnn.Open
Dim x As Integer

Set objCmd = New ADODB.Command
cnn.CursorLocation = adUseClient
 Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseClient
        rst.CursorType = adOpenDynamic
       
    With objCmd
        .ActiveConnection = cnn
        .CommandText = "[dbo].[spRetrieveByReviewOrStatus]"
        .CommandType = adCmdStoredProc
       
'        If strSelectedReviewData = "" Then
'             Set prm = .CreateParameter("pReview", adVarChar, adParamInput, 500, strSelectedReviewData)
'        Else
            Set prm = .CreateParameter("pReview", adVarChar, adParamInput, 500, strSelectedReviewData)
        'End If
       .Parameters.Append prm
     
       
'        If strSelectedStatusData = "" Then
'            Set prm = .CreateParameter("pStatus", adVarChar, adParamInput, 500, strSelectedStatusData)
'        Else
            Set prm = .CreateParameter("pStatus", adVarChar, adParamInput, 500, strSelectedStatusData)
'        End If
        .Parameters.Append prm
       
        Set rst = .Execute
     
        .Parameters.Delete ("pReview")
        .Parameters.Delete ("pStatus")
       
    End With
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Good catch. Since your last comment was the solution, you should accept that as the answer to close out your question.

Author

Commented:
I was able to find the solution on my own

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial