VB & Database, UpdateBatch

UpdateBatch

Author	Message
Val Mazu #1 / 5	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

Dave War #2 / 5	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

Paul Clemen #3 / 5	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

Paul Baptist #4 / 5	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: - Hide quoted text - - 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

Paul Baptist #5 / 5	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

Page 1 of 1

[ 5 post ]