Wilbat
asked on
ADO returns empty recordset from SQL SP
I have this stored procedure in SQL Server:
ALTER PROCEDURE sp_PagedConflictCheckRecor ds
@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("re ccnt", 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_PagedConflictCheckReco rds"
End With
Set cccRS = cccCMD.Execute
At this point if I do:
Response.Write(cccRS.Recor dCount & "<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?
ALTER PROCEDURE sp_PagedConflictCheckRecor
@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")<>
iSize = IIf(Request.Form("size")<>
iSort = IIf(Request.Form("sort")<>
Set cccCMD = Server.CreateObject("ADODB
Set TotalRecs = cccCMD.CreateParameter("re
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_PagedConflictCheckReco
End With
Set cccRS = cccCMD.Execute
At this point if I do:
Response.Write(cccRS.Recor
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?
Sorry wrong post
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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("chkLocke dBy")) & "</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();""><< 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 >></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!
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=
Else
Response.Write("<tr><td width='460'><font size='2'><a class='default' href='ccdetails.asp?chkID=
End If
Response.Write("<td width='100'><font size='2'>" & GetEmpName(cccRS("chkLocke
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
Response.Write("<td colspan='2' align='right' style='border-bottom-style
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!
@RecCnt int OUTPUT,
@Page int,
@Size int,
@Sort int
AS
SET NOCOUNT ON -------------- add this
DECLARE @Start int, @End int