By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,925 Members | 727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,925 IT Pros & Developers. It's quick & easy.

Oracle Error -either eof or bof is true-connecting with vb thru adoddb

P: 17
Hi all,
I have a problem of urgent nature . I am using Oracle and VB6 for an application . i am using ADODB coding
In oracle i have created a table and trying to connect thru vb form. the vb code is giving an error "either eof or bof is true or the current record has been deleted "
When i try to use some other tables already there in oracle , with the same code ,its working fine. Also i have tried to use ADODC in place of ADODB coding ,then also same problem persists with my table, whereas already made tables in oracle work fine.
thx n regards
Dec 20 '07 #1
Share this Question
Share on Google+
19 Replies


debasisdas
Expert 5K+
P: 8,127
ensure that there is some record in the database table.
Dec 20 '07 #2

P: 17
thx for ur prompt reply , there are records in the table and on form load i want to show the first record so i use movefirst of recordset, but still it gives eof or bof is true or current record has been deleted , the same code is working fine with the sample tables already in oracle like emp or dept.
the same code is working with one more table i created , but the difference b/w the 2 tables is that second table doesn't have any field of char type whereas first has. can that be the problem with oracle8, i am trying to use varchar2 in place of that
thx once again
Dec 21 '07 #3

debasisdas
Expert 5K+
P: 8,127
you need to check for BOF and EOF while moving through the recordset.

CHAR OR VARCHAR IS NOT A PROBLEM.

check for BOF is before moving to first record.

try like
Expand|Select|Wrap|Line Numbers
  1. if rs.bof =false then
  2. rs.movefirst
  3. .....
  4. ....
  5.  
Dec 21 '07 #4

P: 17
i have checked that, though there are records in the table , which i am able to see at sql prompt thru a simple sql query , i get rs.eof and rs.bof both as true.
then what cude be the problem?
Dec 21 '07 #5

P: 17
is it related to user rights or privileges etc. problem is coming only with tables i have created, i am using login scott, tiger, shud i login thru some other user?
Dec 21 '07 #6

debasisdas
Expert 5K+
P: 8,127
That could be the least likely cause.

Is that table in scott schema itself.

and what is the type of cursor you are using.
Dec 21 '07 #7

P: 17
ya its in scott user and cursor type i am using is adopendynamic,locktype is adlockoptimistic , later in order to trace the problem ,i have tried to use refresh method of recordset object also but i am not sure its the correct soln or not
thx
Jan 3 '08 #8

P: 46
i have checked that, though there are records in the table , which i am able to see at sql prompt thru a simple sql query , i get rs.eof and rs.bof both as true.
then what cude be the problem?
At SQL prompt, give exactly the query which adodb is passing to the oracle and see whether anything comes out. I guess, problem is that even if there are records in the table, the query you are passing is yielding no rows.

This can be checked as under also:

rs.open "..............."
if rs.recordcount<>0 then
rs.movefirst
end if

Just try it.
Jan 3 '08 #9

debasisdas
Expert 5K+
P: 8,127
ya its in scott user and cursor type i am using is adopendynamic,locktype is adlockoptimistic , later in order to trace the problem ,i have tried to use refresh method of recordset object also but i am not sure its the correct soln or not
thx
is the problem still persisting ?
Jan 3 '08 #10

P: 17
i am using adcmdtable , i.e. select * from tablename , and the same query i am executing from sql prompt which is returning resultset.
thx
Jan 5 '08 #11

debasisdas
Expert 5K+
P: 8,127
i am using adcmdtable , i.e. select * from tablename , and the same query i am executing from sql prompt which is returning resultset.
thx
is that not returning any result from VB ?
Jan 6 '08 #12

P: 17
I have a form in vb, controls on that form are a text box, command button , hflexgrid.

My requirement is that when user enters a number in text box , and clicks on command button , recordset should be opened according to a query matching the value entered in textbox , and flexgrid should be associated with recordset and shud display results.

Now this whole thing is working fine with emp table of oracle, but when I execute same thing with table created by me (login – scott, tiger) , it gives recordset.eof and recordset.bof both as true.
When I execute the same query with my table from sql prompt , it shows records in the table.

I have even tried the simplest case, I have even tried using adcmdtable, i.e not giving any criteria for search, instead all the records of table, but still it gives eof and bof as true.

Also I have tried using adodc, but no use.

This problem is not coming with all students, I am unable to identify what is the cause of the problem.

I am clueless, and puzzled , plz help me solve this problem early, since students’ project for class xii is stuck bcos of this.


Ps: can it be related to rights or privileges ?

Code

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command1_Click()
  3. e = Trim(Text1.Text)
  4. 'Opening Connection
  5.  
  6. Set adoconn = New ADODB.Connection
  7. adoconn.ConnectionString = "provider =MSDAORA; user id=scott; password=tiger;"
  8. adoconn.CursorLocation = adUseClient
  9. adoconn.Open
  10. 'Opening record set
  11. Set adorsexp = Nothing
  12. Set adorsexp = New ADODB.Recordset
  13.  
  14. adorsexp.CursorType = adOpenDynamic
  15. adorsexp.LockType = adLockOptimistic
  16. st = " select * from emp where empno = " + e
  17.  
  18. 'emp is already made table in oracle
  19. '************* working fine with emp table
  20.  
  21. ‘have tried both the following 
  22. 'st = " select * from scott.hrd_office where exp= " + e
  23. 'st = " select * from hrd_office where exp= " + e
  24.  
  25. 'hrd_office is a table with 2 columns eno  (pk ) and exp both number type
  26.  
  27. adorsexp.Open st, adoconn, , , adCmdText
  28. MsgBox (st)
  29. MsgBox (adorsexp.EOF)
  30. MsgBox (adorsexp.BOF)
  31. Do While (Not (adorsexp.EOF))
  32. MsgBox (adorsexp.Fields("empno").Value)
  33. adorsexp.MoveNext
  34. Loop
  35.  
  36. 'Set MSFlexGrid1.DataSource = adorsexp
  37. 'flexgrid being associated with recordset
  38. '****** error coming on above line - object variable or with block variable not set
  39.  
  40. End Sub
  41.  
Jan 10 '08 #13

debasisdas
Expert 5K+
P: 8,127
try to use as following sample code.
Expand|Select|Wrap|Line Numbers
  1. Sql = "select * from scott.hrd_office where exp= " & e
  2. rs.Open Sql, con, adOpenForwardOnly, adLockReadOnly
  3. Set MSHFG.Recordset = rs
  4.  
Jan 10 '08 #14

P: 17
rs.Open Sql, con, adOpenForwardOnly, adLockReadOnly

same problem persists with the above changes also.

Set MSHFG.Recordset = rs
This also doesn't works , it gives error - no such data member exists
Jan 11 '08 #15

debasisdas
Expert 5K+
P: 8,127
rs.Open Sql, con, adOpenForwardOnly, adLockReadOnly

same problem persists with the above changes also.

Set MSHFG.Recordset = rs
This also doesn't works , it gives error - no such data member exists
The control used in the previous code is a hierarchical flex grid . Try using the same control.
Jan 11 '08 #16

P: 17
The control used in the previous code is a hierarchical flex grid . Try using the same control.
i am using hierarchial flex grid control only
Jan 11 '08 #17

debasisdas
Expert 5K+
P: 8,127
i am using hierarchial flex grid control only
But that piece of code works percectly for me.

Ensure that you have used the SET keyword.
Jan 11 '08 #18

P: 17
flex grid problem solved , small mistake was there in name of control,

one cause of problem i found was name of table having underscore, but after correcting that , now my code is working for my table but only when i use primary key in where clause , when i try to use non pk column and make query ,for that again it is showing previous error.

my table is hrd1 having columns empno (pk) and exp1 (experience), , both number type,



when i give query



st= 'select * from hrd1 where empno=' & text1.text



it works fine ,but for query




st= 'select * from hrd1 where exp1=' & text1.text

it is not working

thx n regards
Jan 15 '08 #19

P: 28
flex grid problem solved , small mistake was there in name of control,

one cause of problem i found was name of table having underscore, but after correcting that , now my code is working for my table but only when i use primary key in where clause , when i try to use non pk column and make query ,for that again it is showing previous error.

my table is hrd1 having columns empno (pk) and exp1 (experience), , both number type,



when i give query



st= 'select * from hrd1 where empno=' & text1.text



it works fine ,but for query




st= 'select * from hrd1 where exp1=' & text1.text

it is not working

thx n regards

hi,
is the field u r verfying is of type number. why can't u try to use
e = val(text1.text)
also check whether u've used any keyword as the fieldname in the table. The fieldname : Name, date like this also will affect the execution of the query.
regards,
vijay
Jan 29 '08 #20

Post your reply

Sign in to post your reply or Sign up for a free account.