Solved

ADO returns empty recordset from SQL SP

Posted on 2006-11-06
4
538 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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