Empty Recordset???

[Bruce Duncan]

I'm calling a SQL stored procedure with the folowing
code. It seems to work fine but I want to know if it
doesn't return any rows and the oRsCatList.eof is not
working.

lcDisplayCatList = "Y"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Session("strConn")
Set oCmd = Server.CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oConn
oCmd.CommandText = "spCategoryList"
oCmd.commandtype = AdCmdStoredProc
oCmd.Parameters.Append oCmd.CreateParameter("@CatID",
adInteger, adParamInput)
oCmd.Parameters("@CatID") = lnCatID
set oRsCatList = oCmd.Execute
if oRsCatList.EOF then
lcDisplayCatList = "N"
end if

Even when it doesn't return any rows, lcDisplayCatList is
still = "Y"
Can anyone help point me in the right direction?

TIA
-Bruce

 

[Chris Hohmann]

I'm calling a SQL stored procedure with the folowing
code. It seems to work fine but I want to know if it
doesn't return any rows and the oRsCatList.eof is not
working.

lcDisplayCatList = "Y"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Session("strConn")
Set oCmd = Server.CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oConn
oCmd.CommandText = "spCategoryList"
oCmd.commandtype = AdCmdStoredProc
oCmd.Parameters.Append oCmd.CreateParameter("@CatID",
adInteger, adParamInput)
oCmd.Parameters("@CatID") = lnCatID
set oRsCatList = oCmd.Execute
if oRsCatList.EOF then
lcDisplayCatList = "N"
end if

Even when it doesn't return any rows, lcDisplayCatList is
still = "Y"
Can anyone help point me in the right direction?

TIA
-Bruce

SET NOCOUNT ON
http://www.aspfaq.com/show.asp?id=2246

 

[Bruce Duncan]

Here's my SQL Proc: Is this what you meant?

CREATE PROCEDURE spCategoryList @CatID integer AS

set nocount on

select qcncategory.categoryid, qcncategory.categoryname,
qcnproduct.productid, qcnproduct.productname,
qcnproduct.prodpicture,
qcnproditem.qcnprice, qcnproditem.supply
from qcncategory
left join qcnproduct
inner join qcnproditem on
qcnproduct.productid = qcnproditem.productid
on qcncategory.categoryid = qcnproduct.categoryid
where qcncategory.categoryid = @CatID
GO

-Bruce

 

[Guest]

This is the actual code...and it doesn't help.

CREATE PROCEDURE spCategoryList @CatID integer AS

begin
set nocount on

select qcncategory.categoryid, qcncategory.categoryname,
qcnproduct.productid, qcnproduct.productname,
qcnproduct.prodpicture,
qcnproditem.qcnprice, qcnproditem.supply
from qcncategory
left join qcnproduct
inner join qcnproditem on
qcnproduct.productid = qcnproditem.productid
on qcncategory.categoryid = qcnproduct.categoryid
where qcncategory.categoryid = @CatID

end
GO

-Bruce

 

[Bruce Duncan]

FOUND THE PROBLEM...
it was me...of course I get at least one row to
return...that's why it's not empty...it's not supposed to
be empty...instead of checkinf EOF, I need to check if
emtpy(fieldname)...

Thanks for your post Chris...

-Bruce

 

[TomB]

Have you tried executing the stored procedure in Query Analyzer?