VB & Database, Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B)

Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B)

Author	Message
MCLin #1 / 3	Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B) We are using ADO to access SQL Server 7 from VB6. We open a keyset recordset ----------------------- code ---------------------------- Dim cnn As New ADODB.Connection Dim rec As New ADODB.Recordset cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ADAS;Data Source=RENOIR" rec.CursorType = adOpenKeyset rec.CursorLocation = adUseServer rec.LockType = adLockPessimistic rec.Open "SELECT * FROM Person", cnn ----------------------- code ---------------------------- THEN WE ADD A ROW ----------------------- code ---------------------------- rec.AddNew rec("Name") = "TemporaryName" rec.Update ----------------------- code ---------------------------- THEN WE TRY TO MODIFY A FIELD ----------------------- code ---------------------------- id = rec("PersonId") rec("Code") = "Code" & id rec.Update ----------------------- code ---------------------------- at the rec("Code") = "Code" & id statement the execution breaks with error "The provider is unable to determine identity for newly inserted rows (0x80040E1B)" IF WE TRY A RESYNC ADO returns a generic error "Errors occurred (0x80040E21)" the only way to continue working on the record is either to issue a bookmark positioning statement ----------------------- code ---------------------------- rec.Bookmark = rec.Bookmark ----------------------- code ---------------------------- or to reopen the recordset ----------------------- code ---------------------------- id = rec("PersonId") rec.Close rec.Open "SELECT * FROM Person WHERE PersonId = " & id, cnn rec("Code") = "Code" & id rec.Update ----------------------- code ---------------------------- Even i f I have tiese two workaround I would like to understand the casue of the problem. If anyone has any suggestion , I would appreciate hearing back. Thanks Lorenzo Saladini
Sat, 05 Jul 2003 02:01:54 GMT

Kirk Grave #2 / 3	Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B) make sure the table has a primary key, and that you are including that primary key column in your recordset. Have you considered using Insert and Update SQL statements to modify your data? Quote: > We are using ADO to access SQL Server 7 from VB6. > We open a keyset recordset > ----------------------- code ---------------------------- > Dim cnn As New ADODB.Connection > Dim rec As New ADODB.Recordset > cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial > Catalog=ADAS;Data Source=RENOIR" > rec.CursorType = adOpenKeyset > rec.CursorLocation = adUseServer > rec.LockType = adLockPessimistic > rec.Open "SELECT * FROM Person", cnn > ----------------------- code ---------------------------- > THEN WE ADD A ROW > ----------------------- code ---------------------------- > rec.AddNew > rec("Name") = "TemporaryName" > rec.Update > ----------------------- code ---------------------------- > THEN WE TRY TO MODIFY A FIELD > ----------------------- code ---------------------------- > id = rec("PersonId") > rec("Code") = "Code" & id > rec.Update > ----------------------- code ---------------------------- > at the > rec("Code") = "Code" & id > statement the execution breaks with error > "The provider is unable to determine identity for newly inserted rows > (0x80040E1B)" > IF WE TRY A RESYNC > ADO returns a generic error > "Errors occurred (0x80040E21)" > the only way to continue working on the record is either to issue a bookmark > positioning statement > ----------------------- code ---------------------------- > rec.Bookmark = rec.Bookmark > ----------------------- code ---------------------------- > or to reopen the recordset > ----------------------- code ---------------------------- > id = rec("PersonId") > rec.Close > rec.Open "SELECT * FROM Person WHERE PersonId = " & id, cnn > rec("Code") = "Code" & id > rec.Update > ----------------------- code ---------------------------- > Even i f I have tiese two workaround I would like to understand the casue of > the problem. > If anyone has any suggestion , I would appreciate hearing back. > Thanks > Lorenzo Saladini
Sat, 05 Jul 2003 02:23:55 GMT

Max #3 / 3	Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B) Ok, I've just recently had such a problem (see message like this in this conference by Max). I Inserte a row with Recordset AddNew/Update and in another function i delete rows with Connection Execute executing SQL statement DELETE. I get this error ("The provider is unable...") only when inserting new row with AddNew after calling the deleting function. I FIXED THIS ERROR IN SUCH A WAY - I deleted with Recordset Delete and ufter deleting all i need i called UpdateBatch. ADVICE - try to call UpdateBatch everywhere if you want to modify your recordset later in your code. Quote: > We are using ADO to access SQL Server 7 from VB6. > We open a keyset recordset > ----------------------- code ---------------------------- > Dim cnn As New ADODB.Connection > Dim rec As New ADODB.Recordset > cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial > Catalog=ADAS;Data Source=RENOIR" > rec.CursorType = adOpenKeyset > rec.CursorLocation = adUseServer > rec.LockType = adLockPessimistic > rec.Open "SELECT * FROM Person", cnn > ----------------------- code ---------------------------- > THEN WE ADD A ROW > ----------------------- code ---------------------------- > rec.AddNew > rec("Name") = "TemporaryName" > rec.Update > ----------------------- code ---------------------------- > THEN WE TRY TO MODIFY A FIELD > ----------------------- code ---------------------------- > id = rec("PersonId") > rec("Code") = "Code" & id > rec.Update > ----------------------- code ---------------------------- > at the > rec("Code") = "Code" & id > statement the execution breaks with error > "The provider is unable to determine identity for newly inserted rows > (0x80040E1B)" > IF WE TRY A RESYNC > ADO returns a generic error > "Errors occurred (0x80040E21)" > the only way to continue working on the record is either to issue a bookmark > positioning statement > ----------------------- code ---------------------------- > rec.Bookmark = rec.Bookmark > ----------------------- code ---------------------------- > or to reopen the recordset > ----------------------- code ---------------------------- > id = rec("PersonId") > rec.Close > rec.Open "SELECT * FROM Person WHERE PersonId = " & id, cnn > rec("Code") = "Code" & id > rec.Update > ----------------------- code ---------------------------- > Even i f I have tiese two workaround I would like to understand the casue of > the problem. > If anyone has any suggestion , I would appreciate hearing back. > Thanks > Lorenzo Saladini
Mon, 07 Jul 2003 14:51:07 GMT

Page 1 of 1

[ 3 post ]