[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADO returns empty recordset from SQL SP

Posted on 2006-11-06
4
Medium Priority
?
575 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 1500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

872 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