Inserting row through .NET ODBC driver

[Former Member]

Hello

I have a problem with trying to insert rows from multiple tables in Sybase Server Anywhere 9.0. I have two tables: "document" which contains the document header, and "document_items" which contains document items. Now i'm trying to insert them both from a DataSet, but i get an error:

ERROR [23000] [Sybase][ODBC Driver][Adaptive Server Anywhere]No primary key value for foreign key 'fk_document_ref_255_document_items' in table 'document_items'

The problem is that after updating the DataRow in table "Document" my primary key is not updated (although the row is inserted in the database). To insert the rows i use System.Data.Common.DbCommand object with UpdatedRowSource set to UpdateRowSource.FirstReturnedRecord. Now normally this should update the primary key value of my inserted DataRow, but in Sybase it doesn't seem to work. Does anyone have any ideas?

[jeff_albion]

Do you have a short code sample where you can easily reproduce this error? Can you post the details here?

[Former Member]

I'm fully aware that the error is about the "document_items" foreign key. My problem is that the primary key value of the "documents" datarow is not updated after inserting it to the database.

[reimer_pods]

To really understand your question, more details would be helpfull: can you show the table definitions and the DML statements you execute?

Does the row inserted into "document" include a value for the primary key or is the primary key automatically generated (e.g. default "autoincrement"). In the latter case you will need to get that value (SELECT @@IDENTITY) and add it as foreign key value to the row that is to be inserted in 'document_items'.

[Breck_Carter]

Please explain the statement "the primary key value of the "documents" datarow is not updated after inserting it to the database"... inserts don't typically do "updates".

Please show us the actual code... without that, we are just wandering around in the dark.

[Former Member]

Hi Yes the column is autoincremented and also the new row in the database has a correct new primary key value. I was thinking about using @@IDENTITY, but it could retrieve the last inserted primary key value from other transactions (unfortunately i don't have an exclusive access to the database, and i can't create procedures on it). Here's my inserting code:

DbCommandBuilder MyCB = CreateCommandBuilder(pDA);
DbCommand MyCmdSrc = MyCB.GetInsertCommand();
DbCommand MyCmdDest = CreateCommand(MyCmdSrc.CommandText);

if (m_PrimaryKeyRequest && pDt.PrimaryKey[0].AutoIncrement)
    MyCmdDest.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
else
    MyCmdDest.UpdatedRowSource = UpdateRowSource.None;

int MyParamCount = MyCmdSrc.Parameters.Count;
DbParameter[] MyP = new DbParameter[MyParamCount];

MyCmdSrc.Parameters.CopyTo(MyP, 0);
while (MyCmdSrc.Parameters.Count > 0)
MyCmdSrc.Parameters.Remove(MyCmdSrc.Parameters[MyCmdSrc.Parameters.Count - 1]);

for (int i = 0; i < MyParamCount; i++)
    MyCmdDest.Parameters.Add(MyP[i]);

pDA.InsertCommand = MyCmdDest;
pDA.InsertCommand.Transaction = m_Trans;

It works well in databases like MSSQL, Firebird or Oracle, but in Sybase it doesn't update my DataRow object. Meaning i have a datarow with it's primary key value set as "0", and after inserting the row into the database it remains as "0".

[Breck_Carter]

@@IDENTITY is local to the current connection, so it's safe to use as long as you retrieve the value before inserting any OTHER autoincremening rows on the same connection.

The get_identity() function can also be called to allocate and return a value that you can provide as an explicit value in the inserts.

[Former Member]

You attempted to insert or update a row that has a foreign key for another table. The value for the foreign key is not NULL and there is not a corresponding value in the primary key.