ADO recordset update problem

[Tonatiuh]

This segment of code gives me the follow error:

'fgrGaran' is FlexiGrid Control and 'rsFianza' is an ADO 2.1 recordset.

With fgrGaran
   For X = 1 To rsFianza.RecordCount
      rsFianza.AbsolutePosition = X
      .Col = 0: rsFianza!concepto = .Text
      .Col = 1: rsFianza!numfianz = .Text
      .Col = 2: rsFianza!importe = Val(Format(.Text, "####.##"))
      .Col = 3: rsFianza!afianzad = .Text
      .Col = 4: If VBDateVal(.Text) Then rsFianza!ffianza = DateVBToFox(.Text)
      .Col = 5: If VBDateVal(.Text) Then rsFianza!fini = DateVBToFox(.Text)
      .Col = 6: If VBDateVal(.Text) Then rsFianza!ffin = DateVBToFox(.Text)
      rsFianza.Update
   Next
End With

Run-time error '-2147467259 (800004005)':
Insufficent or incorrect key information; too many row affected by update.

When I used RDO this code works perfectly.

Any idea?

Thanks!

Ulises

[This message has been edited by Tonatiuh (edited 10-01-1999).]

[JHausmann]

Only thing I can guess, and that's all it is with me and ADO, is that maybe you're trying to change a key value?

Wish I could help more.

[Tonatiuh]

That table doesn't have a key field.

Ulises

[Vit]

Make sure that database fields types correspond to types of values that you try to save.

[airbaal]

Since this is a 4 month + old post, I doubt somebody is waiting for the response. I just wanted to write down my experience with this VERY frustrating error in hopes that it helps somebody in the future deal with this problem and avoid suicide or anything else due to complete frustration :-).

I was having the exact same problem with the "too many rows affected by update", etc. But I was just updating 1 record, not a bunch of records as this original poster was doing.

I had a recordset based on an invoice number, but did not have the invoice number in the recordset. I had something like:

strsql = "SELECT item_num, product, descrip FROM 
myTable WHERE invoice_num = '145'"
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

Then lets say I was on item_num = 34, and I would try to update the recordset by

with rs
	!product = txtProduct.text
	!descrip = txtDescript.text
	.update
end with

I would get the dreaded –2147467259 error, even though I was updating two fields for just one record.

The solution was very simple and bizarre at the same time. I just included in the invoice_num field in the recordset, i.e.:

strsql = "SELECT invoice_num, item_num, product, descrip FROM 
myTable WHERE invoice_num = '145'"
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

I am using VB6sp4, ADO 2.6, and Access 2000. My only thought is that for some reason by not having the invoice_num (which is NOT anything more than just a normal field without indexing, not a primary key, etc.), ADO gets “lost” and can’t find the one record to update.

Just my thoughts…

[Clunietp]

If your recordset/table doesn't have a key field, ADO doesn't know what to specify as the proper record identifier. From what I remember, it will try to update the table, but if there was more than 1 record affected by the update, the chage was rollled back and an error generated

so basically just add a key field to your table and include it in your recordset

[airbaal]

Clunietp --

Thanks for the input. That makes so much sense on why it didn't work with this one table (with no primary key) but it works fine on all the other tables (with primary keys). Funny how sometimes you don't notice the obvious because you are too busy beating your head into the wall.... :-)

I added an autonumber field to the table, make it the primary key, and it works perfectly.

Thanks again

Eric