Very Computer

by **Oraclit** » Wed, 13 Nov 2002 15:58:29

Hi,

moving from SQL to Oracle.

Getting recordset using pl/sql packages and for reports
it's ok (read only recordset).

But when I try to update recordset, either using MSDORA or
OraOLEDB provider, I am getting error
message "Insufficient base table information for updating
or refreshing." (MSDORA) or "Multiple-step operation
generated errors.......".

Any help would be much appreciated

Oraclito

by **Georg Schulte Althof** » Wed, 13 Nov 2002 16:51:11

Hi Oraclito,

how do you generate the content of your recordset? Is it a complex query
with more
than one table? To update a single table is not a problem (UPDATE tab SET
...).
But what about this: UPDATE (SELECT ... FROM taba, tabb, tabc WHERE ...)?

I do not have any experience with recordsets gotten from stored procedures,
but this
may be a reason too. Try do do your update directly with another procedure.

Hope this helps

Georg

Quote:> Hi,

> moving from SQL to Oracle.

> Getting recordset using pl/sql packages and for reports
> it's ok (read only recordset).

> But when I try to update recordset, either using MSDORA or
> OraOLEDB provider, I am getting error
> message "Insufficient base table information for updating
> or refreshing." (MSDORA) or "Multiple-step operation
> generated errors.......".

> Any help would be much appreciated

> Oraclito

by **Oraclit** » Wed, 13 Nov 2002 18:52:42

Thanks Georg,

I am trying to utilise existing busines object (dll) where
there are routines for updating disconnected recordset.
Most of packages are simple (like select * from table
order by..). So I get recordset using syntax
rs.open command object,,kyeset,... then disconnect,
process recordset and then update.

But funny thing is that it is failing when I try to set
the field in recordset even before I issue .update command?

Maybe instead of using command object I should use simple
sql call to oracle package? don't know.

Oraclito

>-----Original Message-----
>Hi Oraclito,

>how do you generate the content of your recordset? Is it
a complex query
>with more
>than one table? To update a single table is not a problem
(UPDATE tab SET
>....).
>But what about this: UPDATE (SELECT ... FROM taba, tabb,
tabc WHERE ...)?

>I do not have any experience with recordsets gotten from
stored procedures,
>but this
>may be a reason too. Try do do your update directly with
another procedure.

>Hope this helps

>Georg

>> Hi,

>> moving from SQL to Oracle.

>> Getting recordset using pl/sql packages and for reports
>> it's ok (read only recordset).

>> But when I try to update recordset, either using MSDORA
or
>> OraOLEDB provider, I am getting error
>> message "Insufficient base table information for
updating
>> or refreshing." (MSDORA) or "Multiple-step operation
>> generated errors.......".

>> Any help would be much appreciated

>> Oraclito

>.

by **ajay** » Wed, 13 Nov 2002 19:20:42

Hi,
You need to connect to database again while interacting with database
activity like update....

> Thanks Georg,

> I am trying to utilise existing busines object (dll) where
> there are routines for updating disconnected recordset.
> Most of packages are simple (like select * from table
> order by..). So I get recordset using syntax
> rs.open command object,,kyeset,... then disconnect,
> process recordset and then update.

> But funny thing is that it is failing when I try to set
> the field in recordset even before I issue .update command?

> Maybe instead of using command object I should use simple
> sql call to oracle package? don't know.

> Oraclito

> >-----Original Message-----
> >Hi Oraclito,

> >how do you generate the content of your recordset? Is it
> a complex query
> >with more
> >than one table? To update a single table is not a problem
> (UPDATE tab SET
> >....).
> >But what about this: UPDATE (SELECT ... FROM taba, tabb,
> tabc WHERE ...)?

> >I do not have any experience with recordsets gotten from
> stored procedures,
> >but this
> >may be a reason too. Try do do your update directly with
> another procedure.

> >Hope this helps

> >Georg

> >> Hi,

> >> moving from SQL to Oracle.

> >> Getting recordset using pl/sql packages and for reports
> >> it's ok (read only recordset).

> >> But when I try to update recordset, either using MSDORA
> or
> >> OraOLEDB provider, I am getting error
> >> message "Insufficient base table information for
> updating
> >> or refreshing." (MSDORA) or "Multiple-step operation
> >> generated errors.......".

> >> Any help would be much appreciated

> >> Oraclito

> >.

by **Georg Schulte Althof** » Wed, 13 Nov 2002 21:27:47

Quote:> Thanks Georg,

> I am trying to utilise existing busines object (dll) where
> there are routines for updating disconnected recordset.
> Most of packages are simple (like select * from table
> order by..). So I get recordset using syntax
> rs.open command object,,kyeset,... then disconnect,
> process recordset and then update.

I guess you use a client cursor (otherwise disconnect should fail).
Have you tried to change the value "keyset" to another?
I usually open my recordsets like this:
sSQL = "..."
rec.open sSQL, ...

Quote:> But funny thing is that it is failing when I try to set
> the field in recordset even before I issue .update command?

Can you post the code where the error occurs and the detailed
error message?

Quote:> Maybe instead of using command object I should use simple
> sql call to oracle package? don't know.

> Oraclito

Georg

by **Val Mazu** » Wed, 13 Nov 2002 21:52:52

Hi,

Check if you have primary key in that table, which invoved into SELECT
statement. If there is no PK, then provider could fail to build proper
update. Also, if result returned from SP, then it is possible that provider
canoot do it at all. Also check what is LockType property value after you
opened recordset, not before

--
Val Mazur
Microsoft MVP

> Thanks Georg,

> I am trying to utilise existing busines object (dll) where
> there are routines for updating disconnected recordset.
> Most of packages are simple (like select * from table
> order by..). So I get recordset using syntax
> rs.open command object,,kyeset,... then disconnect,
> process recordset and then update.

> But funny thing is that it is failing when I try to set
> the field in recordset even before I issue .update command?

> Maybe instead of using command object I should use simple
> sql call to oracle package? don't know.

> Oraclito

> >-----Original Message-----
> >Hi Oraclito,

> >how do you generate the content of your recordset? Is it
> a complex query
> >with more
> >than one table? To update a single table is not a problem
> (UPDATE tab SET
> >....).
> >But what about this: UPDATE (SELECT ... FROM taba, tabb,
> tabc WHERE ...)?

> >I do not have any experience with recordsets gotten from
> stored procedures,
> >but this
> >may be a reason too. Try do do your update directly with
> another procedure.

> >Hope this helps

> >Georg

> >> Hi,

> >> moving from SQL to Oracle.

> >> Getting recordset using pl/sql packages and for reports
> >> it's ok (read only recordset).

> >> But when I try to update recordset, either using MSDORA
> or
> >> OraOLEDB provider, I am getting error
> >> message "Insufficient base table information for
> updating
> >> or refreshing." (MSDORA) or "Multiple-step operation
> >> generated errors.......".

> >> Any help would be much appreciated

> >> Oraclito

> >.

by **Oraclit** » Tue, 19 Nov 2002 14:25:49

Thanks guys,

it looks like Val is on the right track.

1. No primary key(s) at all, just unique index.
2. Resultset is, actually refcursor returned from SP from
within a package.
3. Resultset properties after opening using command object
are:

CursorLocation : adUseClientBatch
CursorType : adOpenStatic
LockType : adLockOptimistic

Any thoughts on this?

If it can not be done using existing routines for updating
disconnected recordset, than I suppose the best way would
be writing new SPs for updating related tables (something
I am trying to avoid).

PS: Same routines are working fine in SQL 7/2000. DB
structure in Oracle is the same as SQL Server source DB ?!

Thanks again

Oraclito

Quote:>-----Original Message-----
>Hi,

>Check if you have primary key in that table, which
invoved into SELECT
>statement. If there is no PK, then provider could fail to
build proper
>update. Also, if result returned from SP, then it is

possible that provider

>canoot do it at all. Also check what is LockType property
value after you
>opened recordset, not before

>--
>Val Mazur
>Microsoft MVP

>> Thanks Georg,

>> I am trying to utilise existing busines object (dll)
where
>> there are routines for updating disconnected recordset.
>> Most of packages are simple (like select * from table
>> order by..). So I get recordset using syntax
>> rs.open command object,,kyeset,... then disconnect,
>> process recordset and then update.

>> But funny thing is that it is failing when I try to set
>> the field in recordset even before I issue .update
command?

>> Maybe instead of using command object I should use
simple
>> sql call to oracle package? don't know.

>> Oraclito

>> >-----Original Message-----
>> >Hi Oraclito,

>> >how do you generate the content of your recordset? Is
it
>> a complex query
>> >with more
>> >than one table? To update a single table is not a
problem
>> (UPDATE tab SET
>> >....).
>> >But what about this: UPDATE (SELECT ... FROM taba,
tabb,
>> tabc WHERE ...)?

>> >I do not have any experience with recordsets gotten
from
>> stored procedures,
>> >but this
>> >may be a reason too. Try do do your update directly
with
>> another procedure.

>> >Hope this helps

>> >Georg

>> >> Hi,

>> >> moving from SQL to Oracle.

>> >> Getting recordset using pl/sql packages and for
reports
>> >> it's ok (read only recordset).

>> >> But when I try to update recordset, either using
MSDORA
>> or
>> >> OraOLEDB provider, I am getting error
>> >> message "Insufficient base table information for
>> updating
>> >> or refreshing." (MSDORA) or "Multiple-step operation
>> >> generated errors.......".

>> >> Any help would be much appreciated

>> >> Oraclito

>> >.

>.

by **Val Mazu** » Tue, 19 Nov 2002 21:53:11

Hi Oraclito,

If you have possibility to use another SP, then I would suggest to do that.
Even if it will work now, then small changes in SP in future could lead to
another problem. Just create another SP, which will update/insert records.

--
Val Mazur
Microsoft MVP

> Thanks guys,

> it looks like Val is on the right track.

> 1. No primary key(s) at all, just unique index.
> 2. Resultset is, actually refcursor returned from SP from
> within a package.
> 3. Resultset properties after opening using command object
> are:

> CursorLocation : adUseClientBatch
> CursorType : adOpenStatic
> LockType : adLockOptimistic

> Any thoughts on this?

> If it can not be done using existing routines for updating
> disconnected recordset, than I suppose the best way would
> be writing new SPs for updating related tables (something
> I am trying to avoid).

> PS: Same routines are working fine in SQL 7/2000. DB
> structure in Oracle is the same as SQL Server source DB ?!

> Thanks again

> Oraclito

> >-----Original Message-----
> >Hi,

> >Check if you have primary key in that table, which
> invoved into SELECT
> >statement. If there is no PK, then provider could fail to
> build proper
> >update. Also, if result returned from SP, then it is
> possible that provider
> >canoot do it at all. Also check what is LockType property
> value after you
> >opened recordset, not before

> >--
> >Val Mazur
> >Microsoft MVP

> >> Thanks Georg,

> >> I am trying to utilise existing busines object (dll)
> where
> >> there are routines for updating disconnected recordset.
> >> Most of packages are simple (like select * from table
> >> order by..). So I get recordset using syntax
> >> rs.open command object,,kyeset,... then disconnect,
> >> process recordset and then update.

> >> But funny thing is that it is failing when I try to set
> >> the field in recordset even before I issue .update
> command?

> >> Maybe instead of using command object I should use
> simple
> >> sql call to oracle package? don't know.

> >> Oraclito

> >> >-----Original Message-----
> >> >Hi Oraclito,

> >> >how do you generate the content of your recordset? Is
> it
> >> a complex query
> >> >with more
> >> >than one table? To update a single table is not a
> problem
> >> (UPDATE tab SET
> >> >....).
> >> >But what about this: UPDATE (SELECT ... FROM taba,
> tabb,
> >> tabc WHERE ...)?

> >> >I do not have any experience with recordsets gotten
> from
> >> stored procedures,
> >> >but this
> >> >may be a reason too. Try do do your update directly
> with
> >> another procedure.

> >> >Hope this helps

> >> >Georg

> >> >> Hi,

> >> >> moving from SQL to Oracle.

> >> >> Getting recordset using pl/sql packages and for
> reports
> >> >> it's ok (read only recordset).

> >> >> But when I try to update recordset, either using
> MSDORA
> >> or
> >> >> OraOLEDB provider, I am getting error
> >> >> message "Insufficient base table information for
> >> updating
> >> >> or refreshing." (MSDORA) or "Multiple-step operation
> >> >> generated errors.......".

> >> >> Any help would be much appreciated

> >> >> Oraclito

> >> >.

> >.

by **Oraclit** » Wed, 20 Nov 2002 11:42:23

Thanks Val,

it looks like having separate "update" sp is the best
solution.

Regards

Oraclito

Quote:>-----Original Message-----
>Hi Oraclito,

>If you have possibility to use another SP, then I would
suggest to do that.
>Even if it will work now, then small changes in SP in

future could lead to

Quote:>another problem. Just create another SP, which will

update/insert records.

>--
>Val Mazur
>Microsoft MVP

>> Thanks guys,

>> it looks like Val is on the right track.

>> 1. No primary key(s) at all, just unique index.
>> 2. Resultset is, actually refcursor returned from SP
from
>> within a package.
>> 3. Resultset properties after opening using command
object
>> are:

>> CursorLocation : adUseClientBatch
>> CursorType : adOpenStatic
>> LockType : adLockOptimistic

>> Any thoughts on this?

>> If it can not be done using existing routines for
updating
>> disconnected recordset, than I suppose the best way
would
>> be writing new SPs for updating related tables
(something
>> I am trying to avoid).

>> PS: Same routines are working fine in SQL 7/2000. DB
>> structure in Oracle is the same as SQL Server source
DB ?!

>> Thanks again

>> Oraclito

>> >-----Original Message-----
>> >Hi,

>> >Check if you have primary key in that table, which
>> invoved into SELECT
>> >statement. If there is no PK, then provider could fail
to
>> build proper
>> >update. Also, if result returned from SP, then it is
>> possible that provider
>> >canoot do it at all. Also check what is LockType
property
>> value after you
>> >opened recordset, not before

>> >--
>> >Val Mazur
>> >Microsoft MVP

>> >> Thanks Georg,

>> >> I am trying to utilise existing busines object (dll)
>> where
>> >> there are routines for updating disconnected
recordset.
>> >> Most of packages are simple (like select * from table
>> >> order by..). So I get recordset using syntax
>> >> rs.open command object,,kyeset,... then disconnect,
>> >> process recordset and then update.

>> >> But funny thing is that it is failing when I try to
set
>> >> the field in recordset even before I issue .update
>> command?

>> >> Maybe instead of using command object I should use
>> simple
>> >> sql call to oracle package? don't know.

>> >> Oraclito

>> >> >-----Original Message-----
>> >> >Hi Oraclito,

>> >> >how do you generate the content of your recordset?
Is
>> it
>> >> a complex query
>> >> >with more
>> >> >than one table? To update a single table is not a
>> problem
>> >> (UPDATE tab SET
>> >> >....).
>> >> >But what about this: UPDATE (SELECT ... FROM taba,
>> tabb,
>> >> tabc WHERE ...)?

>> >> >I do not have any experience with recordsets gotten
>> from
>> >> stored procedures,
>> >> >but this
>> >> >may be a reason too. Try do do your update directly
>> with
>> >> another procedure.

>> >> >Hope this helps

>> >> >Georg

>> >> >> Hi,

>> >> >> moving from SQL to Oracle.

>> >> >> Getting recordset using pl/sql packages and for
>> reports
>> >> >> it's ok (read only recordset).

>> >> >> But when I try to update recordset, either using
>> MSDORA
>> >> or
>> >> >> OraOLEDB provider, I am getting error
>> >> >> message "Insufficient base table information for
>> >> updating
>> >> >> or refreshing." (MSDORA) or "Multiple-step
operation
>> >> >> generated errors.......".

>> >> >> Any help would be much appreciated

>> >> >> Oraclito

>> >> >.

>> >.

>.

Very Computer

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6

Urgent: Update Oracle disconnected recordset using ADO 2.6 and VB6