Solved

ADO returns empty recordset from SQL SP

Posted on 2006-11-06
4
534 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:Wilbat
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17882205
ALTER  PROCEDURE sp_PagedConflictCheckRecords

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

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

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17882210
Sorry  wrong post
0
 
LVL 16

Accepted Solution

by:
TSmooth earned 500 total points
ID: 17882566
With ADO, the "RecordCount" property is not valid until the recordset is traversed. Since ADO uses a cursor type method to access the data as opposed to a disconnected DataSet that ADO.NET generally uses, initially, all the recordset knows is that there are records available.

Everytime you use cccRS .MoveNext, the RecordCount property get's incremented. There are a few options to get the count using ADO. One of them is with an output parameter just like your @RecCnt  parameter. Just add another output parameter that holds the count you are looking for.

Another option is to retrieve the recordset, use the MoveLast() method of your recordset, and then look at your RecordCount property. If you opened the recordset using a dynamic cursor you could then use MoveFirst() to go back to the beginning and begin any looping through the recordset you need to do.

-Tom
0
 

Author Comment

by:Wilbat
ID: 17882715
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!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now