Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    India
    Posts
    22

    Unanswered: SELECT COUNT(*) gives an error

    Dear All,

    I am opening an ado recordset using MSDAORA provider.
    rs.open "SELECT COUNT(*) FROM CUSTOMER", OBJCONNECTION
    The Oracle database has 120000 records in CUSTOMER table. But when I try to get the value from rs, it thows an error

    Error Code: 3021
    Error Description: Either BOF or EOF is True or the current record has been deleted. Requested operation requires a current record

    Here is the code

    rs.open "SELECT COUNT(*) FROM CUSTOMER", OBJCONNECTION
    MSGBOX "Opened"
    msgbox rs.fields(0).value --Here is gives an error


    Can anybody help me put?

    Thanks,
    Regards,
    Jigar Bhavsar

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You may find that your client side language is trying to create a field for your record set called "count(*)". This is invariably an illegal identifier.

    Try changing your select to SELECT COUNT(*) AS RCOUNT FROM CUSTOMER.

    I don't deal with ADO so I'm just guessing here, but I've come across this problem with other connection types & clients.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Feb 2004
    Location
    India
    Posts
    22
    I have tried by giving alias name, still it gives the same error.
    Regards,
    Jigar Bhavsar

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    1. looks like you are at the BOF of recordset... am not sure of syntax, but u have to open the RS .. u can fetch values from only when its neither BOF nor EOF..

    2. as billm suggested, try using the field name.

    3. Is it fields(0) or fields[0] ?
    Oracle can do wonders !

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Sorry I can't be of more help, as I say I don't deal with ADO. Some other thoughts though, make sure the recordset is readonly, try the SQL statement through SqlPlus (using the same connection info) etc.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Feb 2004
    Location
    India
    Posts
    22
    1. SELECT COUNT(*) must return value. So there is no chance of EOF or BOF
    2. I have tried using alias as well.
    3. Field(0) , this is for visual basic, it works fine with my other code
    Regards,
    Jigar Bhavsar

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Did you try doing this..

    PHP Code:
    rs.open "SELECT COUNT(*) FROM CUSTOMER"OBJCONNECTION
    MSGBOX 
    "Opened"

    IF rs.EOF and rs.BOF then
       MSGBOX 
    "no data"
    else
       
    msgbox rs.fields(0).value 
    end 
    if 
    Oracle can do wonders !

  8. #8
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    also, try this..

    PHP Code:
    rs.open "SELECT COUNT(*) FROM CUSTOMER"OBJCONNECTION
    MSGBOX 
    "Opened"
    rs.movefirst
    msgbox rs
    .fields(0).value 
    Oracle can do wonders !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •