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?

asked 20 Nov '12, 08:39

Sebastian%20Ptak's gravatar image

Sebastian Ptak
31113
accept rate: 0%

retagged 22 Nov '12, 09:02

Nica%20_SAP's gravatar image

Nica _SAP
866722

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

(21 Nov '12, 12:04) Jeff Albion
Comment Text Removed

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.

permanent link

answered 22 Nov '12, 03:31

Sebastian%20Ptak's gravatar image

Sebastian Ptak
31113
accept rate: 0%

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

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

permanent link

answered 22 Nov '12, 00:17

sybaserays's gravatar image

sybaserays
1
accept rate: 0%

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

permanent link

answered 22 Nov '12, 09:19

Sebastian%20Ptak's gravatar image

Sebastian Ptak
31113
accept rate: 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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×99
×56

question asked: 20 Nov '12, 08:39

question was seen: 1,678 times

last updated: 22 Nov '12, 10:03