Batch update for multiple tables using single ADO Recordset (VB ) 
Author Message
 Batch update for multiple tables using single ADO Recordset (VB )

Hi All,

Thanks for reading my post.

My environment is VB6.0 Client-Server, Oracle 9i, MDAC 2.5,
Provider=OraOLEDB.Oracle.1 and COM+

I have two tables
 Table Person  having fields
        PersonID - Primary Key
        LastName
        FirstName
        MI

 Table Phone having fields
        PhoneID -Primary Key
        PersonIDFK-Foreign key referring PersonID column of Person
table
        PhoneNumber
        Extension

At the maximum there will be only one record in Phone table for a
person.
Sometimes a person in person table may not have phone info in phone
table

Data structure of the two tables looks as follows

Person table
PersonID LastName FirstName MI
1        Last1    First1    M
2        Last2    First2    N

Phone table
PhoneID PersonIDFK PhoneNumber Extension
1       2          123

Kindly note that for PersonID=1 there is no record in Phone table

I need to update both the tables by ADO Batchupdate method with a
single recordset

I use the following query to get the recordset

SELECT a.personid, a.lastname, a.FirstName, a.MI, b.PhoneID,
b.PersonIDFK, b.PhoneNumber, b.Extension FROM Person a , Phone b
where a.personID=1 and  a.PersonID =b.PersonIDFK(+)

The values recordset I am getting is

a.personid=1
a.lastname=Last1
a.FirstName=First1
a.MI=M
b.PhoneID=Null
b.PersonIDFK=Null
b.PhoneNumber=Null
b.Extension=null

In the front end i am changing/assigning the values, the data of the
recordset getting changed like this

a.personid=1
a.lastname=Last1Modified
a.FirstName=First1Modified
a.MI=K
b.PhoneID=Null
b.PersonIDFK=Null
b.PhoneNumber=123
b.Extension=987

Using sequence i assigned value to primary key field of Phone table, i
also set the PhoneIDFK field in Phone table programmatically, so the
data of the recordset looks like this

a.personid=1
a.lastname=Last1Modified
a.FirstName=First1Modified
a.MI=K
b.PhoneID=2
b.PersonIDFK=1
b.PhoneNumber=123
b.Extension=987

I use the following syntax for batch update

With myrst
        .ActiveConnection = adoconnection
        .MarshalOptions = adMarshalModifiedOnly
        .UpdateBatch adAffectAllChapters
End With

When calling  recordset.updatebatch method the following error occurs.
err.Number:-2147217887
err.Description:Multiple-step operation generated errors. Check each
status value.

What will be the reason for this error?. Can anybody help me?

But if there is a matching value in phone table (ie if the data is
available as follows) this update happens without any errors.

Person table
PersonID LastName FirstName MI
1        Last1    First1    M
2        Last2    First2    N

Phone table
PhoneID PersonIDFK PhoneNumber Extension
1       2          123
2       1          456                                                                          

I suggested to use separate recordsets for each table, but my techlead
says using separate recordset is an inefficient way and he has done
this kind of multi table update using single recordset before.

Hope to see some help guys.

Thanks in advance
Das Nair



Sat, 22 Oct 2005 04:59:19 GMT  
 Batch update for multiple tables using single ADO Recordset (VB )
This can happen if the data base have incompatible fields

this KB is more in details click here

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269495

--
Shivprasad Koirala

Griffin Marine IT Department
syngrou 5 athens
After all, on a twenty-four hour basis....
Marine Travel is our business
visit our website:  www.griffintravel.com

mobile (030) - 6944 392 362


Quote:
> Hi All,

> Thanks for reading my post.

> My environment is VB6.0 Client-Server, Oracle 9i, MDAC 2.5,
> Provider=OraOLEDB.Oracle.1 and COM+

> I have two tables
>  Table Person  having fields
>         PersonID - Primary Key
>         LastName
>         FirstName
>         MI

>  Table Phone having fields
>         PhoneID -Primary Key
>         PersonIDFK-Foreign key referring PersonID column of Person
> table
>         PhoneNumber
>         Extension

> At the maximum there will be only one record in Phone table for a
> person.
> Sometimes a person in person table may not have phone info in phone
> table

> Data structure of the two tables looks as follows

> Person table
> PersonID LastName FirstName MI
> 1 Last1    First1    M
> 2 Last2   First2    N

> Phone table
> PhoneID PersonIDFK PhoneNumber Extension
> 1       2          123

> Kindly note that for PersonID=1 there is no record in Phone table

> I need to update both the tables by ADO Batchupdate method with a
> single recordset

> I use the following query to get the recordset

> SELECT a.personid, a.lastname, a.FirstName, a.MI, b.PhoneID,
> b.PersonIDFK, b.PhoneNumber, b.Extension FROM Person a , Phone b
> where a.personID=1 and  a.PersonID =b.PersonIDFK(+)

> The values recordset I am getting is

> a.personid=1
> a.lastname=Last1
> a.FirstName=First1
> a.MI=M
> b.PhoneID=Null
> b.PersonIDFK=Null
> b.PhoneNumber=Null
> b.Extension=null

> In the front end i am changing/assigning the values, the data of the
> recordset getting changed like this

> a.personid=1
> a.lastname=Last1Modified
> a.FirstName=First1Modified
> a.MI=K
> b.PhoneID=Null
> b.PersonIDFK=Null
> b.PhoneNumber=123
> b.Extension=987

> Using sequence i assigned value to primary key field of Phone table, i
> also set the PhoneIDFK field in Phone table programmatically, so the
> data of the recordset looks like this

> a.personid=1
> a.lastname=Last1Modified
> a.FirstName=First1Modified
> a.MI=K
> b.PhoneID=2
> b.PersonIDFK=1
> b.PhoneNumber=123
> b.Extension=987

> I use the following syntax for batch update

> With myrst
>         .ActiveConnection = adoconnection
>         .MarshalOptions = adMarshalModifiedOnly
>         .UpdateBatch adAffectAllChapters
> End With

> When calling  recordset.updatebatch method the following error occurs.
> err.Number:-2147217887
> err.Description:Multiple-step operation generated errors. Check each
> status value.

> What will be the reason for this error?. Can anybody help me?

> But if there is a matching value in phone table (ie if the data is
> available as follows) this update happens without any errors.

> Person table
> PersonID LastName FirstName MI
> 1 Last1    First1    M
> 2 Last2   First2    N

> Phone table
> PhoneID PersonIDFK PhoneNumber Extension
> 1       2          123
> 2 1          456

> I suggested to use separate recordsets for each table, but my techlead
> says using separate recordset is an inefficient way and he has done
> this kind of multi table update using single recordset before.

> Hope to see some help guys.

> Thanks in advance
> Das Nair



Sat, 22 Oct 2005 15:42:13 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Batch update for multiple tables using single ADO Recordset (VB )

2. Oracle multiple table update with one ADO recordset

3. newbie: using ADO and Recordsets with a multiple-table database

4. ADO, SQL Server Updating Recordset using 2 Tables

5. SQL - multiple recordsets and a single table questions

6. Multiple recordsets for a single ADO connection???

7. Multiple Recordsets for a single ADO connection???

8. Using disconnected recordset and batch update

9. PROBLEM USING BATCH UPDATE ON DISCONNECTED (HIERARCHAL) RECORDSET

10. Open multiple recordsets using single parameterized command object

11. Q: How to use batch updates on acces using ado jet provider 3.51

12. multiple table dynaset vs multiple table recordsets

 

 
Powered by phpBB® Forum Software