UpdateBatch 
Author Message
 UpdateBatch

Paul,

First of all check if you hake primary key in both tables. If yes, then
probably provider cannot build proper SQL statement for updating values. In
that case you would need to execute UPDATE SQL statement to update values in
database, rather then using recordset. It happens in case if your recordset
opened based on joined SQL statement

--
Val Mazur
Microsoft MVP


Quote:
> I get this error:

> Run-time error '-2147467259(80004005)'
> Insufficient key column information for updating or
> refreshing.

> When I do this

> With rs
> .CursorType = adOpenStatic
> .CursorLocation = adUseClient
> .LockType = adLockBatchOptimistic
> .Open "SELECT table1.*, table2.* FROM table1 t1 INNER JOIN
> table2 t2 ON t1.ID = t2.ID", conn
> End With

> rs.Fields("table2_Field") = "Test"
> rs.Update 'this updates the recordset ok
> rs.UpdateBatch 'this line generates the error

> I don't receive an error if i update the parent table,
> only when i update the joined table.

> any ideas.

> Thanks

> Paul



Sun, 08 May 2005 02:55:16 GMT  
 UpdateBatch
Couple of points I'd look at:
1. Updatebatch won't work if you are only updating a single field in a
single record. The minimum required is 2 fields in a single record.
2. The syntax:
rs.Fields("table2_Field") = "Test"
-may need looking at. Reference the table and the field
rs.Fields("table2.FieldName").Value = "Test"
D.
Quote:

> I get this error:

> Run-time error '-2147467259(80004005)'
> Insufficient key column information for updating or
> refreshing.

> When I do this

> With rs
> .CursorType = adOpenStatic
> .CursorLocation = adUseClient
> .LockType = adLockBatchOptimistic
> .Open "SELECT table1.*, table2.* FROM table1 t1 INNER JOIN
> table2 t2 ON t1.ID = t2.ID", conn
> End With

> rs.Fields("table2_Field") = "Test"
> rs.Update 'this updates the recordset ok
> rs.UpdateBatch 'this line generates the error

> I don't receive an error if i update the parent table,
> only when i update the joined table.

> any ideas.

> Thanks

> Paul



Sun, 08 May 2005 03:55:23 GMT  
 UpdateBatch

I get this error:

Run-time error '-2147467259(80004005)'
Insufficient key column information for updating or
refreshing.

When I do this

With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open "SELECT table1.*, table2.* FROM table1 t1 INNER JOIN
table2 t2 ON t1.ID = t2.ID", conn
End With

rs.Fields("table2_Field") = "Test"
rs.Update 'this updates the recordset ok  
rs.UpdateBatch 'this line generates the error

I don't receive an error if i update the parent table,
only when i update the joined table.

Yes, this behavior is by-design. However, I believe you can use the MSDataShape
provider and Shape command to build hierarchical Recordsets that are updateable:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q189657


Microsoft MVP (Visual Basic)



Sun, 08 May 2005 05:17:43 GMT  
 UpdateBatch
Yes, a shaped recordset works for updating the parent and
choild tables. but I can only update the parent table
after I disconnect the recordset.

This works:

With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open "SHAPE {select * from table1} AS t1 APPEND ({select
* from table2}  AS t2 RELATE 't1_id' TO 't2_id') AS
t2_row", conn
'Set .ActiveConnection = Nothing
End With

Set rsT2 = rs.Fields("t2_row").Value
rs.Fields("t1_field").Value = "Test1"
rsT2.Fields("t2_field").Value = "Test2"
rs.Update
rsStat.Update
rs.UpdateBatch
rsStat.UpdateBatch

The code below does not work: Only the T1 field is updated
and not the T2 field.

With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open "SHAPE {select * from table1} AS t1 APPEND ({select
* from table2}  AS t2 RELATE 't1_id' TO 't2_id') AS
t2_row", conn
Set .ActiveConnection = Nothing
End With

conn.close 'I do not set the connection to nothing

Set rsT2 = rs.Fields("t2_row").Value
rs.Fields("t1_field").Value = "Test1"
rsT2.Fields("t2_field").Value = "Test2"
rs.Update
rsStat.Update
conn.Open
rs.ActiveConnection = conn
rs.UpdateBatch
rsStat.UpdateBatch

I can only get data in the child table to update if I do
not disconnect the recordset. How do I update the child
table after reconnecting from a disconnected recordset.

Thanks

Paul

Quote:
>-----Original Message-----
>On Tue, 19 Nov 2002 10:27:31 -0800, "Paul Baptista"

> I get this error:
>
> Run-time error '-2147467259(80004005)'
> Insufficient key column information for updating or
> refreshing.
>
> When I do this
>
> With rs
> .CursorType = adOpenStatic
> .CursorLocation = adUseClient
> .LockType = adLockBatchOptimistic
> .Open "SELECT table1.*, table2.* FROM table1 t1 INNER
JOIN
> table2 t2 ON t1.ID = t2.ID", conn
> End With
>
> rs.Fields("table2_Field") = "Test"
> rs.Update 'this updates the recordset ok  
> rs.UpdateBatch 'this line generates the error
>
> I don't receive an error if i update the parent table,
> only when i update the joined table.
>

>Yes, this behavior is by-design. However, I believe you

can use the MSDataShape
Quote:
>provider and Shape command to build hierarchical

Recordsets that are updateable:

- Show quoted text -

Quote:

>http://support.microsoft.com/default.aspx?scid=KB;en-
us;q189657


>Microsoft MVP (Visual Basic)
>.



Sun, 08 May 2005 07:46:50 GMT  
 UpdateBatch
Sorry,

I left out this line of code after reconnecting to the
recordset.

Set rsT2 = rs.Fields("t2_row").Value

Everything works ok now.

Thanks Again.

Quote:
>-----Original Message-----
>Yes, a shaped recordset works for updating the parent and
>choild tables. but I can only update the parent table
>after I disconnect the recordset.

>This works:

>With rs
>..CursorType = adOpenStatic
>..CursorLocation = adUseClient
>..LockType = adLockBatchOptimistic
>..Open "SHAPE {select * from table1} AS t1 APPEND
({select
>* from table2}  AS t2 RELATE 't1_id' TO 't2_id') AS
>t2_row", conn
>'Set .ActiveConnection = Nothing
>End With

>Set rsT2 = rs.Fields("t2_row").Value
>rs.Fields("t1_field").Value = "Test1"
>rsT2.Fields("t2_field").Value = "Test2"
>rs.Update
>rsStat.Update
>rs.UpdateBatch
>rsStat.UpdateBatch

>The code below does not work: Only the T1 field is
updated
>and not the T2 field.

>With rs
>..CursorType = adOpenStatic
>..CursorLocation = adUseClient
>..LockType = adLockBatchOptimistic
>..Open "SHAPE {select * from table1} AS t1 APPEND
({select
>* from table2}  AS t2 RELATE 't1_id' TO 't2_id') AS
>t2_row", conn
>Set .ActiveConnection = Nothing
>End With

>conn.close 'I do not set the connection to nothing

>Set rsT2 = rs.Fields("t2_row").Value
>rs.Fields("t1_field").Value = "Test1"
>rsT2.Fields("t2_field").Value = "Test2"
>rs.Update
>rsStat.Update
>conn.Open
>rs.ActiveConnection = conn
>rs.UpdateBatch
>rsStat.UpdateBatch

>I can only get data in the child table to update if I do
>not disconnect the recordset. How do I update the child
>table after reconnecting from a disconnected recordset.

>Thanks

>Paul
>>-----Original Message-----
>>On Tue, 19 Nov 2002 10:27:31 -0800, "Paul Baptista"

>> I get this error:
>>
>> Run-time error '-2147467259(80004005)'
>> Insufficient key column information for updating or
>> refreshing.
>>
>> When I do this
>>
>> With rs
>> .CursorType = adOpenStatic
>> .CursorLocation = adUseClient
>> .LockType = adLockBatchOptimistic
>> .Open "SELECT table1.*, table2.* FROM table1 t1 INNER
>JOIN
>> table2 t2 ON t1.ID = t2.ID", conn
>> End With
>>
>> rs.Fields("table2_Field") = "Test"
>> rs.Update 'this updates the recordset ok  
>> rs.UpdateBatch 'this line generates the error
>>
>> I don't receive an error if i update the parent table,
>> only when i update the joined table.
>>

>>Yes, this behavior is by-design. However, I believe you
>can use the MSDataShape
>>provider and Shape command to build hierarchical
>Recordsets that are updateable:

>>http://support.microsoft.com/default.aspx?scid=KB;en-
>us;q189657


>>Microsoft MVP (Visual Basic)
>>.

>.



Sun, 08 May 2005 07:54:36 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. UpdateBatch

2. Inserting into multi-table recordset with .updatebatch

3. updatebatch and textboxes

4. Suggestion: Stored Procedure or UpdateBatch?

5. Help with UpdateBatch Method when some records already exist in Database

6. ADO, SQL and UpdateBatch

7. RecordSet.Updatebatch

8. ADO method UpdateBatch

9. ADO UpdateBatch problem

10. Help with UpdateBatch Method when some records already exist in Database

11. Progressbar w/ updatebatch ADO, is this even possible?

12. non-reentrant method -> UpdateBatch

 

 
Powered by phpBB® Forum Software