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

[mask]

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

[debasisdas]

ensure that there is some record in the database table.

[mask]

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

[debasisdas]

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

if rs.bof =false then
rs.movefirst
.....
....
 

[mask]

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?

[mask]

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?

[debasisdas]

That could be the least likely cause.

Is that table in scott schema itself.

and what is the type of cursor you are using.

[mask]

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

[vikas000000a]

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.

[debasisdas]

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 ?

[mask]

i am using adcmdtable , i.e. select * from tablename , and the same query i am executing from sql prompt which is returning resultset.
thx

[debasisdas]

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 ?

[mask]

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

 
Private Sub Command1_Click()
e = Trim(Text1.Text)
'Opening Connection
 
Set adoconn = New ADODB.Connection
adoconn.ConnectionString = "provider =MSDAORA; user id=scott; password=tiger;"
adoconn.CursorLocation = adUseClient
adoconn.Open
'Opening record set
Set adorsexp = Nothing
Set adorsexp = New ADODB.Recordset
 
adorsexp.CursorType = adOpenDynamic
adorsexp.LockType = adLockOptimistic
st = " select * from emp where empno = " + e
 
'emp is already made table in oracle
'************* working fine with emp table
 
‘have tried both the following 
'st = " select * from scott.hrd_office where exp= " + e
'st = " select * from hrd_office where exp= " + e
 
'hrd_office is a table with 2 columns eno  (pk ) and exp both number type
 
adorsexp.Open st, adoconn, , , adCmdText
MsgBox (st)
MsgBox (adorsexp.EOF)
MsgBox (adorsexp.BOF)
Do While (Not (adorsexp.EOF))
MsgBox (adorsexp.Fields("empno").Value)
adorsexp.MoveNext
Loop
 
'Set MSFlexGrid1.DataSource = adorsexp
'flexgrid being associated with recordset
'****** error coming on above line - object variable or with block variable not set
 
End Sub
 

[debasisdas]

try to use as following sample code.

Sql = "select * from scott.hrd_office where exp= " & e
rs.Open Sql, con, adOpenForwardOnly, adLockReadOnly
Set MSHFG.Recordset = rs
 

[mask]

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

[debasisdas]

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.

[mask]

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

[debasisdas]

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.

[mask]

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

[cvraghavan1979]

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