469,575 Members | 1,268 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

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

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
19 3614
debasisdas
8,127 Expert 4TB
ensure that there is some record in the database table.
Dec 20 '07 #2
mask
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
8,127 Expert 4TB
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
mask
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
mask
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
8,127 Expert 4TB
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
mask
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
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
8,127 Expert 4TB
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
mask
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
8,127 Expert 4TB
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
mask
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
8,127 Expert 4TB
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
mask
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
8,127 Expert 4TB
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
mask
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
8,127 Expert 4TB
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
mask
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
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.

Similar topics

13 posts views Thread by Chris Botha | last post: by
4 posts views Thread by BookerW | last post: by
5 posts views Thread by Martin Solveig | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.