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.

lcDisplayCatLis t = "Y"
Set oConn = Server.CreateOb ject("ADODB.Con nection")
oConn.Open Session("strCon n")
Set oCmd = Server.CreateOb ject("ADODB.Com mand")
set oCmd.ActiveConn ection = oConn
oCmd.CommandTex t = "spCategoryList "
oCmd.commandtyp e = AdCmdStoredProc
oCmd.Parameters .Append oCmd.CreatePara meter("@CatID",
adInteger, adParamInput)
oCmd.Parameters ("@CatID") = lnCatID
set oRsCatList = oCmd.Execute
if oRsCatList.EOF then
lcDisplayCatLis t = "N"
end if

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

TIA
-Bruce

[Chris Hohmann]

Re: Empty Recordset???


"Bruce Duncan" <anonymous@disc ussions.microso ft.com> wrote in message
news:8f4901c3ea 7b$c9612610$a30 1280a@phx.gbl.. .[color=blue]
> 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.
>
> lcDisplayCatLis t = "Y"
> Set oConn = Server.CreateOb ject("ADODB.Con nection")
> oConn.Open Session("strCon n")
> Set oCmd = Server.CreateOb ject("ADODB.Com mand")
> set oCmd.ActiveConn ection = oConn
> oCmd.CommandTex t = "spCategoryList "
> oCmd.commandtyp e = AdCmdStoredProc
> oCmd.Parameters .Append oCmd.CreatePara meter("@CatID",
> adInteger, adParamInput)
> oCmd.Parameters ("@CatID") = lnCatID
> set oRsCatList = oCmd.Execute
> if oRsCatList.EOF then
> lcDisplayCatLis t = "N"
> end if
>
> Even when it doesn't return any rows, lcDisplayCatLis t is
> still = "Y"
> Can anyone help point me in the right direction?
>
> TIA
> -Bruce[/color]

SET NOCOUNT ON

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

[Bruce Duncan]

Re: Empty Recordset???

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

CREATE PROCEDURE spCategoryList @CatID integer AS

set nocount on

select qcncategory.cat egoryid, qcncategory.cat egoryname,
qcnproduct.prod uctid, qcnproduct.prod uctname,
qcnproduct.prod picture,
qcnproditem.qcn price, qcnproditem.sup ply
from qcncategory
left join qcnproduct
inner join qcnproditem on
qcnproduct.prod uctid = qcnproditem.pro ductid
on qcncategory.cat egoryid = qcnproduct.cate goryid
where qcncategory.cat egoryid = @CatID
GO

-Bruce

[color=blue]
>-----Original Message-----
>
>"Bruce Duncan" <anonymous@disc ussions.microso ft.com>[/color]
wrote in message[color=blue]
>news:8f4901c3e a7b$c9612610$a3 01280a@phx.gbl. ..[color=green]
>> 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.
>>
>> lcDisplayCatLis t = "Y"
>> Set oConn = Server.CreateOb ject("ADODB.Con nection")
>> oConn.Open Session("strCon n")
>> Set oCmd = Server.CreateOb ject("ADODB.Com mand")
>> set oCmd.ActiveConn ection = oConn
>> oCmd.CommandTex t = "spCategoryList "
>> oCmd.commandtyp e = AdCmdStoredProc
>> oCmd.Parameters .Append oCmd.CreatePara meter("@CatID",
>> adInteger, adParamInput)
>> oCmd.Parameters ("@CatID") = lnCatID
>> set oRsCatList = oCmd.Execute
>> if oRsCatList.EOF then
>> lcDisplayCatLis t = "N"
>> end if
>>
>> Even when it doesn't return any rows, lcDisplayCatLis t[/color][/color]
is[color=blue][color=green]
>> still = "Y"
>> Can anyone help point me in the right direction?
>>
>> TIA
>> -Bruce[/color]
>
>SET NOCOUNT ON
>http://www.aspfaq.com/show.asp?id=2246
>
>
>.
>[/color]

[Guest]

Re: Empty Recordset???

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

CREATE PROCEDURE spCategoryList @CatID integer AS

begin
set nocount on

select qcncategory.cat egoryid, qcncategory.cat egoryname,
qcnproduct.prod uctid, qcnproduct.prod uctname,
qcnproduct.prod picture,
qcnproditem.qcn price, qcnproditem.sup ply
from qcncategory
left join qcnproduct
inner join qcnproditem on
qcnproduct.prod uctid = qcnproditem.pro ductid
on qcncategory.cat egoryid = qcnproduct.cate goryid
where qcncategory.cat egoryid = @CatID

end
GO

-Bruce
[color=blue]
>-----Original Message-----
>Here's my SQL Proc: Is this what you meant?
>
>CREATE PROCEDURE spCategoryList @CatID integer AS
>
>set nocount on
>
>select qcncategory.cat egoryid, qcncategory.cat egoryname,
>qcnproduct.pro ductid, qcnproduct.prod uctname,
>qcnproduct.pro dpicture,
>qcnproditem.qc nprice, qcnproditem.sup ply
>from qcncategory
> left join qcnproduct
> inner join qcnproditem on
>qcnproduct.pro ductid = qcnproditem.pro ductid
> on qcncategory.cat egoryid = qcnproduct.cate goryid
>where qcncategory.cat egoryid = @CatID
>GO
>
>-Bruce
>
>[color=green]
>>-----Original Message-----
>>
>>"Bruce Duncan" <anonymous@disc ussions.microso ft.com>[/color]
>wrote in message[color=green]
>>news:8f4901c3 ea7b$c9612610$a 301280a@phx.gbl ...[color=darkred]
>>> 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.
>>>
>>> lcDisplayCatLis t = "Y"
>>> Set oConn = Server.CreateOb ject("ADODB.Con nection")
>>> oConn.Open Session("strCon n")
>>> Set oCmd = Server.CreateOb ject("ADODB.Com mand")
>>> set oCmd.ActiveConn ection = oConn
>>> oCmd.CommandTex t = "spCategoryList "
>>> oCmd.commandtyp e = AdCmdStoredProc
>>> oCmd.Parameters .Append oCmd.CreatePara meter("@CatID",
>>> adInteger, adParamInput)
>>> oCmd.Parameters ("@CatID") = lnCatID
>>> set oRsCatList = oCmd.Execute
>>> if oRsCatList.EOF then
>>> lcDisplayCatLis t = "N"
>>> end if
>>>
>>> Even when it doesn't return any rows,[/color][/color][/color]
lcDisplayCatLis t[color=blue]
>is[color=green][color=darkred]
>>> still = "Y"
>>> Can anyone help point me in the right direction?
>>>
>>> TIA
>>> -Bruce[/color]
>>
>>SET NOCOUNT ON
>>http://www.aspfaq.com/show.asp?id=2246
>>
>>
>>.
>>[/color]
>.
>[/color]

[Bruce Duncan]

Empty Recordset???

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
[color=blue]
>-----Original Message-----
>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.
>
>lcDisplayCatLi st = "Y"
>Set oConn = Server.CreateOb ject("ADODB.Con nection")
>oConn.Open Session("strCon n")
>Set oCmd = Server.CreateOb ject("ADODB.Com mand")
>set oCmd.ActiveConn ection = oConn
>oCmd.CommandTe xt = "spCategoryList "
>oCmd.commandty pe = AdCmdStoredProc
>oCmd.Parameter s.Append oCmd.CreatePara meter("@CatID",
>adInteger, adParamInput)
>oCmd.Parameter s("@CatID") = lnCatID
>set oRsCatList = oCmd.Execute
>if oRsCatList.EOF then
> lcDisplayCatLis t = "N"
>end if
>
>Even when it doesn't return any rows, lcDisplayCatLis t[/color]
is[color=blue]
>still = "Y"
>Can anyone help point me in the right direction?
>
>TIA
>-Bruce
>.
>[/color]

[TomB]

Re: Empty Recordset???

Have you tried executing the stored procedure in Query Analyzer?



<anonymous@disc ussions.microso ft.com> wrote in message
news:9a6501c3ea 82$b44366b0$a60 1280a@phx.gbl.. .[color=blue]
> This is the actual code...and it doesn't help.
>
> CREATE PROCEDURE spCategoryList @CatID integer AS
>
> begin
> set nocount on
>
> select qcncategory.cat egoryid, qcncategory.cat egoryname,
> qcnproduct.prod uctid, qcnproduct.prod uctname,
> qcnproduct.prod picture,
> qcnproditem.qcn price, qcnproditem.sup ply
> from qcncategory
> left join qcnproduct
> inner join qcnproditem on
> qcnproduct.prod uctid = qcnproditem.pro ductid
> on qcncategory.cat egoryid = qcnproduct.cate goryid
> where qcncategory.cat egoryid = @CatID
>
> end
> GO
>
> -Bruce
>[color=green]
> >-----Original Message-----
> >Here's my SQL Proc: Is this what you meant?
> >
> >CREATE PROCEDURE spCategoryList @CatID integer AS
> >
> >set nocount on
> >
> >select qcncategory.cat egoryid, qcncategory.cat egoryname,
> >qcnproduct.pro ductid, qcnproduct.prod uctname,
> >qcnproduct.pro dpicture,
> >qcnproditem.qc nprice, qcnproditem.sup ply
> >from qcncategory
> > left join qcnproduct
> > inner join qcnproditem on
> >qcnproduct.pro ductid = qcnproditem.pro ductid
> > on qcncategory.cat egoryid = qcnproduct.cate goryid
> >where qcncategory.cat egoryid = @CatID
> >GO
> >
> >-Bruce
> >
> >[color=darkred]
> >>-----Original Message-----
> >>
> >>"Bruce Duncan" <anonymous@disc ussions.microso ft.com>[/color]
> >wrote in message[color=darkred]
> >>news:8f4901c3 ea7b$c9612610$a 301280a@phx.gbl ...
> >>> 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.
> >>>
> >>> lcDisplayCatLis t = "Y"
> >>> Set oConn = Server.CreateOb ject("ADODB.Con nection")
> >>> oConn.Open Session("strCon n")
> >>> Set oCmd = Server.CreateOb ject("ADODB.Com mand")
> >>> set oCmd.ActiveConn ection = oConn
> >>> oCmd.CommandTex t = "spCategoryList "
> >>> oCmd.commandtyp e = AdCmdStoredProc
> >>> oCmd.Parameters .Append oCmd.CreatePara meter("@CatID",
> >>> adInteger, adParamInput)
> >>> oCmd.Parameters ("@CatID") = lnCatID
> >>> set oRsCatList = oCmd.Execute
> >>> if oRsCatList.EOF then
> >>> lcDisplayCatLis t = "N"
> >>> end if
> >>>
> >>> Even when it doesn't return any rows,[/color][/color]
> lcDisplayCatLis t[color=green]
> >is[color=darkred]
> >>> still = "Y"
> >>> Can anyone help point me in the right direction?
> >>>
> >>> TIA
> >>> -Bruce
> >>
> >>SET NOCOUNT ON
> >>http://www.aspfaq.com/show.asp?id=2246
> >>
> >>
> >>.
> >>[/color]
> >.
> >[/color][/color]