Solved

ADO returns empty recordset from SQL SP

Posted on 2006-11-06
4
548 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 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