Empty Recordset???

Discussion in 'ASP General' started by Bruce Duncan, Feb 3, 2004.

  1. Bruce Duncan

    Bruce Duncan Guest

    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
    Bruce Duncan, Feb 3, 2004
    #1
    1. Advertising

  2. "Bruce Duncan" <> wrote in message
    news:8f4901c3ea7b$c9612610$...
    > 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
    Chris Hohmann, Feb 3, 2004
    #2
    1. Advertising

  3. Bruce Duncan

    Bruce Duncan Guest

    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


    >-----Original Message-----
    >
    >"Bruce Duncan" <>

    wrote in message
    >news:8f4901c3ea7b$c9612610$...
    >> 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, Feb 3, 2004
    #3
  4. Bruce Duncan

    Guest 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

    >-----Original Message-----
    >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
    >
    >
    >>-----Original Message-----
    >>
    >>"Bruce Duncan" <>

    >wrote in message
    >>news:8f4901c3ea7b$c9612610$...
    >>> 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
    >>
    >>
    >>.
    >>

    >.
    >
    Guest, Feb 3, 2004
    #4
  5. Bruce Duncan

    Bruce Duncan Guest

    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

    >-----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.
    >
    >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
    >.
    >
    Bruce Duncan, Feb 3, 2004
    #5
  6. Bruce Duncan

    TomB Guest

    Have you tried executing the stored procedure in Query Analyzer?



    <> wrote in message
    news:9a6501c3ea82$b44366b0$...
    > 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
    >
    > >-----Original Message-----
    > >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
    > >
    > >
    > >>-----Original Message-----
    > >>
    > >>"Bruce Duncan" <>

    > >wrote in message
    > >>news:8f4901c3ea7b$c9612610$...
    > >>> 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
    > >>
    > >>
    > >>.
    > >>

    > >.
    > >
    TomB, Feb 3, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John

    empty/non-empty element

    John, Jul 15, 2003, in forum: XML
    Replies:
    1
    Views:
    1,009
    Klaus Johannes Rusch
    Jul 16, 2003
  2. RufusGoofus

    binding empty recordset to datagrid

    RufusGoofus, Jan 22, 2005, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    105
    RufusGoofus
    Jan 22, 2005
  3. Hung Huynh
    Replies:
    8
    Views:
    285
    Bob Barrows
    Sep 24, 2003
  4. J P Singh

    Recordset Empty Error

    J P Singh, Jan 16, 2004, in forum: ASP General
    Replies:
    4
    Views:
    156
    J P Singh
    Jan 16, 2004
  5. Eli Sidwell

    Returning RecordSet to ASP is empty

    Eli Sidwell, Feb 10, 2004, in forum: ASP General
    Replies:
    4
    Views:
    209
    Eli Sidwell
    Feb 11, 2004
Loading...

Share This Page