|
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:
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? |
|
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. To really understand your question, more details would be helpfull: can you show the table definitions and the DML statements you execute?
(22 Nov '12, 07:50)
Reimer Pods
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.
(22 Nov '12, 08:55)
Breck Carter
|
|
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. |
|
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". 2
@@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.
(22 Nov '12, 10:03)
Breck Carter
|
Do you have a short code sample where you can easily reproduce this error? Can you post the details here?