Ado, Transaction, Multiline statements?

From: Rick (rick_at_tariffnet.com)
Date: 01/23/05 

Date: Sun, 23 Jan 2005 11:37:51 -0500

Hi everybody, I just kind of stumbled into this little problem.

Setup: Delphi 7 Pro, Windows 2000 Server, MSDE ('SQL Server, Lite'), using
the ADO Components.

Basically I insert a record into the master table (and get the Identity).
Then I need to insert 10-30 detail lines into another table using the
identity as the FK to the master table. Now I thought that if I just put a
string of SQL Insert statements together in a StringList object, it would
execute much faster. One call to the database server instead of 20-30.
Well, to test this I just threw the tables together, and set about writting
some quick code. However I set the CustID in the detail table to 'Indexed
(no duplicates)'. Of course when the Inserts ran on the detail table, only
one record was inserted ---> But, and here is the problem ---> no error was
raised and the transaction committed?

Note: After I changed the Detail Table to allow Duplicate values in the
CustID field (it is a FK), code worked. However, I need to know if there is
an error in a multiline statement.

My concern is
1) is it good form to do multi line inserts like this?
2) Why didn't I get an exception or can I read an error from someplace? My
program thinks everything was inserted and there were no errors; this is
VERY bad.

Simplified code:
oSQLText.Clear // Simple TStringList object
AdoConn.BeginTrans;
try
    AdoDataSet1.CommandText := 'Insert Into TestTable (LastName, FirstName)
Values (''Bobo'', ''Tom''); Select Scope_Identity() As CustID;';
    iCustID := AdoDataSet1.FieldByName('CustID').AsInteger;
    for x := 0 to 10 do
        oSQLText.Add( 'Insert Into TableB ( CustID, ItemNumber ) VALUES (' +
iCustID +', 890 ); );
    AdoCommand.CommandText := oSQLText.Text;
    AdoCommand.ExecuteSQL;
    AdoConn.CommitTrans;
except
    AdoConn.RollBackTrans;
end;

Thank you
Rick