Link to home
Start Free TrialLog in
Avatar of Wilbat
WilbatFlag for United States of America

asked on

ADO returns empty recordset from SQL SP

I have this stored procedure in SQL Server:

ALTER  PROCEDURE sp_PagedConflictCheckRecords

@RecCnt int OUTPUT,
@Page int,
@Size int,
@Sort int

AS

DECLARE @Start int, @End int

SET @Start = (((@Page - 1) * @Size) + 1)
SET @End = (@Start + @Size - 1)

SET NOCOUNT ON

CREATE TABLE #TemporaryTable (
      [RowID] [int] identity(1,1) NOT NULL,
      [chkID] [int],
      [chkLockedFor] [int],
      [chkLockedDate] [datetime],
      [chkLockedBy] [nvarchar] (20),
      [empFirstName] [nvarchar] (255),
      [empLastName] [nvarchar] (255)
)

INSERT INTO #TemporaryTable (chkID, chkLockedBy, chkLockedDate, chkLockedFor, empLastName, empFirstName)
      SELECT chkID, chkLockedBy, chkLockedDate, chkLockedFor, empLastName, empFirstName
      FROM ConflictCheckData LEFT JOIN Employee ON chkLockedBy = empNetID
      WHERE chkLockedFor = 1 OR chkLockedFor = 2

SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
ORDER BY
      CASE WHEN @Sort = 1 THEN empLastName ELSE NULL END DESC,
      CASE WHEN @Sort = 2 THEN chkLockedDate ELSE NULL END DESC,
      CASE WHEN @Sort = 3 THEN chkLockedFor ELSE NULL END DESC

SELECT @RecCnt = (SELECT COUNT(*) FROM #TemporaryTable)

DROP TABLE #TemporaryTable

SET NOCOUNT OFF

and when I run it, it returns a correct recordset in Query Analyzer.

However, when I call it via ADO in an ASP page I keep getting an empty recordset.  Here's the code:

      iPage = IIf(Request.Form("page")<>"", page, 1)
      iSize = IIf(Request.Form("size")<>"", size, 15)
      iSort = IIf(Request.Form("sort")<>"", sort, 2)
      
      Set cccCMD = Server.CreateObject("ADODB.Command")
      Set TotalRecs = cccCMD.CreateParameter("reccnt", adInteger, adParamOutput)
      cccCMD.Parameters.Append TotalRecs
      
      With cccCMD
            .ActiveConnection = cnn
            .CommandType = adCmdStoredProc
            .Parameters.Append       .CreateParameter("page", adInteger, adParamInput, 16, iPage)
            .Parameters.Append       .CreateParameter("size", adInteger, adParamInput, 16, iSize)
            .Parameters.Append       .CreateParameter("sort", adInteger, adParamInput, 16, iSort)
            .CommandText = "sp_PagedConflictCheckRecords"
      End With      
      
      Set cccRS = cccCMD.Execute      

At this point if I do:
      Response.Write(cccRS.RecordCount & "<br>")
      cccRS.Close
      Response.Write(cccCMD(0) & "<br>")
I get:
      0 for the recordCount (should be 15 with the parameters I sent) &
      16 for cccCMD(0) (correctly returns 16 which is the total number of records in my Db)

Anyone know what Im doing wrong?
Avatar of Aneesh
Aneesh
Flag of Canada image

ALTER  PROCEDURE sp_PagedConflictCheckRecords

@RecCnt int OUTPUT,
@Page int,
@Size int,
@Sort int

AS
SET NOCOUNT ON -------------- add this
DECLARE @Start int, @End int

Sorry  wrong post
ASKER CERTIFIED SOLUTION
Avatar of TSmooth
TSmooth

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Wilbat

ASKER

MoveLast and MoveFirst returned an error.  But I tried this instead and it worked fine.

      x = 0
      Do While Not cccRS.EOF
            x = x + 1
            If cccRS("chkLockedFor") = 1 Then
                  Response.Write("<tr><td width='460'><font size='2'><a class='default' href='ccdetails.asp?chkID=" & cccRS("chkID") & "' style='color: lime;'>" & BuildString(cccRS("chkID")) & "</a></td>" & vbCRLF)
            Else
                  Response.Write("<tr><td width='460'><font size='2'><a class='default' href='ccdetails.asp?chkID=" & cccRS("chkID") & "' style='color: red;'>" & BuildString(cccRS("chkID")) & "</a></td>" & vbCRLF)
            End If
            Response.Write("<td width='100'><font size='2'>" & GetEmpName(cccRS("chkLockedBy")) & "</td>" & vbCRLF)
            Response.Write("<td width='160'><font size='2'>" & cccRS("chkLockedDate") & "</font></td></tr>" & vbCRLF)
            cccRS.MoveNext
      Loop
      Response.Write("<tr><td align='left' style='border-bottom-style: none;border-bottom-width: 0px;'><a class='default' href=""return goBack();"">&lt;&lt; Previous Page</a></td>")
      Response.Write("<td colspan='2' align='right' style='border-bottom-style: none;border-bottom-width: 0px;'><a class='default' href=""return goForward();"">Next Page &gt;&gt;</a></td></tr>")
      If x < 1 Then
            Response.Write("<tr><td colspan='3'><font size='2'>There are no closed Conflict Checks at this time.</td></tr>" & vbCRLF)
      End If


FYI, you can you the RecordCount property without traversing the recordset first if you use the right cursor type.  I regularly use the adOpenKeyset cursor type with an RS.Open command and then refrence the RecourdCount property.

Thanks for putting me on the right path!