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)

Post by MCLin » Wed, 17 Jan 2001 03:01:54



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

 
 
 

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

Post by Kirk Grave » Wed, 17 Jan 2001 03:23:55


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


 
 
 

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

Post by Max » Fri, 19 Jan 2001 15:51:07


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

 
 
 

1. 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

2. More than 16 args to postgres

3. The provider is unable to determine identity for newly inserted rows

4. Sorting a DBGrid

5. Identity cannot be determined for newly inserted rows

6. Downgrade from 2.6 SP1

7. DBGrid question

8. Determining newly inserted identity value in DB2 v6

9. Identify cannot be determined for newly inserted rows

10. How to get Identity column values for newly inserted row

11. get back the identity of a newly inserted row thru ODBC

12. Getting IDENTITY value of newly inserted row