Write and read uncommitted entry within transaction?

draken

Hi,

The following simplified code is trying to read an uncommitted record, from Navision 4, using ADO:

CREATE(adoConn);  // Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
CREATE(adoRS);    // Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset

adoConn.IsolationLevel(256);  // Set Uncommitted Read

adoConn.Open(_strConnString);

adoConn.BeginTrans;

adoConn.Execute('INSERT INTO Item (Name) VALUES ('Item Name')', intRecAffected);  // intRecAffected returns 1 if the records was inserted

// ... and now for the troubled part...
adoRS := adoConn.Execute('SELECT [ID] FROM Item WHERE Name = 'Item Name'');

adoRS.MoveFirst;
intCount := adoRS.RecordCount;  // Returns -1

adoConn.RollbackTrans;

// ... Close and Clean up

The item table I'm dealing with is an external application which synchronises with Navision items. When an item is inserted in this application, an identity column provides the primary key, which I need to capture in Navision. Both Navision and the external app have SQL 2000 backends.

Am I missing something or can I not do this?

draken

jsnayberk

Message deleted. Sorry.

kine

And what is the problem?

draken

The problem is that I can not retrieve the record I inserted.
Put another way, in query analyzer I can do the following:

BEGIN TRANSACTION
INSERT INTO Item ([Name]) VALUES ('Item Name')
SELECT [ID] FROM Item WHERE [Name] = 'Item Name'
ROLLBACK TRANSACTION

The SELECT statement will return the ID of the just inserted record. When I try to do this in Navision through ADO automation, the select statement returns nothing.

kine

Have you tried to set the isolation level after opening the session or beginning the transaction?

draken

Thanks Kine, I've tried setting the isolation level pretty much anywhere in the code. no success

trag

Try this, probably this works:

INSERT INTO Item ([Name]) VALUES ('Item Name')
SELECT @@IDENTITY As ItemId

draken

I found that even though adoRS.RecordCount returns -1, the following statement returns a value:

adoRS.Fields.Item(0).Value)

No doubt some subtlety just beyond my realm of perception. Anyway, problem no more, thanks for your input.

draken