Member Avatar for jaasaria

this is the error i have...

key column information is insufficient or incorrect. to many rows were affected by update.


thx for viewing...

  • 5 Contributors
  • 8 Replies
  • 105 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by jaasaria

Dani AI

Generated

A few focused checks that fix this exact error most of the time.

: the ADO/recordset error you saw most commonly means ADO could not form a unique WHERE clause for the row you tried to commit. That can happen for three distinct reasons; isolate which one by running these quick tests.

  • Confirm the table actually has a primary key and that the primary-key column is included in the recordset you open. If the SELECT omits the key (or uses DISTINCT, aggregates, joins or calculated columns) the provider cannot identify a single row to update.
  • Verify the value placed into the key is not Null or a duplicate (and if the key is an auto-number you should not try to set it yourself).
  • Check for usage of client-side batch updates. If you opened the recordset for batch updates you must call UpdateBatch (or use a Command/INSERT) to persist; Update alone may not commit with adLockBatchOptimistic/adUseClient.

Additional practical checks that often catch the real cause:

  • Open the database file directly with the database tool after your program writes a record. If the record is not there, your program never committed to the file. If it is there but your program shows none on next run, check the file path in the connection string—development environments sometimes copy a template DB into the build folder so you inspect the wrong file.
  • Turn on error reporting and dump the ADO Errors collection to get provider-level details.

If you want to avoid recordset metadata problems, use a parameterized INSERT via ADODB.Command instead of AddNew/Update:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cn
  .CommandText = "INSERT INTO YourTable (Field1,Field2,Field3) VALUES (?,?,?)"
  .CommandType = adCmdText
  .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 50, txtA.Text)
  .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, txtB.Text)
  .Parameters.Append .CreateParameter("p3", adVarChar, adParamInput, 255, txtC.Text)
  .Execute
End With

Tie the above to suggestions by , , and : confirm field names/primary key, choose the right commit method (UpdateBatch vs Update vs Execute), and verify the actual database file is being updated.

Recommended Answers

All 8 Replies

Member Avatar for ryan_vietnow

i think it is in your .update.you have less fields that you have updated.Please post your code.

Member Avatar for jaasaria

hi my friend... this my saverecord procedure...

Sub saverecord()

rssupplier.AddNew
rssupplier.Fields("CompanyCode") = txt1.Text
rssupplier.Fields("CompanyName") = txt2.Text
rssupplier.Fields("ContactPerson") = txt3.Text
rssupplier.Fields("Position") = txt4.Text
rssupplier.Fields("TelephoneNo") = txt5.Text
rssupplier.Fields("Address") = txt6.Text
rssupplier.Update

End Sub


thxxxxxxxxx...

Member Avatar for jaasaria

after an 2hour nothing have been change in my form..pls help

after i save a record the data was been save.
but when i end the run..
and run again the program..
the datagrid became null..

the record that i save was lost and only white lines left..
after i enter the command button continuesly the error appeared.

plssssssss plssssssss.......... somebody help me.........

Member Avatar for jonc

"key column information is insufficient or incorrect" error appears to suggest that your primary key field (whatever that may be) has either been left empty, or the contents of it is the same as a record already in your database.

Edit: on a google search, it is possible you have not included the Primary Key field in your recordset. This can also cause a similar error.

Member Avatar for Mbt925

use this metod instead Update

UpdateBatch adAffectCurrent
Member Avatar for jireh

Please try to look again in your datagrid maybe you missed about the name of the fields, may you lack in declaring some fields.

and about your code update...

rssupplier.AddNew
rssupplier.Fields("CompanyCode") = txt1.Text
rssupplier.Fields("CompanyName") = txt2.Text
rssupplier.Fields("ContactPerson") = txt3.Text
rssupplier.Fields("Position") = txt4.Text
rssupplier.Fields("TelephoneNo") = txt5.Text
rssupplier.Fields("Address") = txt6.Text
rssupplier.Update

I dnt see any rpoblem here but if this cause a problem then review your fieldnames.
1. how many fieldnames.
2. spelling of the fieldnames...

hope this will help you...

Member Avatar for jaasaria

thxxxx for the reply thxx alot when i solved this i will give to all of you a reputation. heheh^_^

Member Avatar for jaasaria

i already check my table field but its all the same.

could somebody give me a site on error detection?
i will check it.
plsssssss give me any idea.. ]


thxxxxx .. thxxxxxxxxx

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.