Search results

[WenYuan Wang [MSFT]]

Hello Manjree,
Thanks for your feedback.

Regarding to the issue about "Missing the DataColumn 'supplierRef' in the
DataTable 'samplesTable' for the SourceColumn 'supplierRef'."

It seems like that you use the wrong DBdatapter to update Semples Table.
Would you please let me know what is the update command in sqlDb -> adapter?

Based on my experience, such problem could occur when updating the samples
Table by the wrong SQL command which is used for Supplier table. Therefore,
adapter cannot found "Supplier.supplierRef" column in SamplesTable.

I suggest you can check the sqlDb -> adapter->updateCommand->CommandText
before updating samples by this DBDataAdapter.
I suspect it was "update Supplier .... ".

Hope this helps. Please let me know if you face any futher issue. We are
glad to assist you.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hello Wen

Thanks for your reply.

I realised that in case of update the data set should contain only one
table. My dataset contains all the tables, that's why this problem arised.

Well! Now I am using stored procedure to update the sampleID of Sample table
which is primary key of this table and foreign key of SamplePrep. The
procedure is:


ALTER PROCEDURE dbo.ModifySampID
AS
BEGIN TRANSACTION

UPDATE Sample
SET sampleID = 'ddddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 10
END


UPDATE SamplePrep
SET sampleID = 'ddddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 11
END

COMMIT TRANSACTION

RETURN


But it is not working either throwing the exception:

"Update statement conflicted with the REFERENCE constrained
FK_SamplePrep_Sample. Conflict occured in table SamplePrep column sampleID."

I guess Begin Transaction is not working.

Cheers

Manjree

 

[WenYuan Wang [MSFT]]

Hello Manjree,
Thanks for your reply.

The error always be thrown SQL Server, even though we put the UPDATE query
in Transaction.
By design, the error message will be thrown after we executes the procedure.

In your case, throwing the exception doesn't mean "Begin Transaction" is
not working. This is by design.

If you step into this procedure, you will notice the exception is throwed
after the line (return 11)

IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 11 // after this line.

Therefore, I think your stored precedure works fine.

We have to try-catch() the exception in code.

Hope this helps.let me know if there is anything unclear. We are glad to
assist you.
Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi Wen

Thanks for your reply. The procedure is not modifying the value of sampleID.
I tried it in SQL server and getting the following messages:

Msg 547, Level 16, State 0, Line 4
The UPDATE statement conflicted with the REFERENCE constraint
"FK_SamplePrep_Sample". The conflict occurred in database "Protien", table
"dbo.SamplePrep", column 'sampleID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 13
The UPDATE statement conflicted with the FOREIGN KEY constraint
"FK_SamplePrep_Sample". The conflict occurred in database "Protien", table
"dbo.Sample", column 'sampleID'.
The statement has been terminated.
Msg 3903, Level 16, State 1, Line 18
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 3902, Level 16, State 1, Line 22
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

 

[WenYuan Wang [MSFT]]

Hello Manjree,
Thanks for your reply.

According to your description, your issue is that you cannot execute the
procedure and receive the error message about the update statement
Conflicted with FK constraint. Please don't hesitate to correct me, if I
misunderstood anything here.

From the error message and your pre-reply, there is a relationship between
Sample and SamplePref by sampleID. The Sample table is the Parent table and
SamplePref is the Child table. Please let me know if I'm wrong.

Regarding to the first update statement:

UPDATE Sample
SET sampleID = 'ddddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)

Have you checked if there is any row (which sampleID is 'bbb01' ) in the
SamplePref table?

Due to the FK constraint, each row of Sample has to link to the Parent
Table (SampleRref).
If there is any row whose sampleID is 'bbb01' in SamplePref, we cannot
change the parent row from "bbb01" to 'ddddd01'. Otherwise, the FK
constraint error will be thrown.

You may check the following document.
http://msdn2.microsoft.com/en-us/library/ms175464.aspx
[FOREIGN KEY Constraints]

Although the main purpose of a FOREIGN KEY constraint is to control the
data that can be stored in the foreign key table, it also controls changes
to data in the primary key table. For example, if the row for a salesperson
is deleted from the Sales.SalesPerson table, and the salesperson's ID is
used for sales orders in the Sales.SalesOrderHeader table, the relational
integrity between the two tables is broken; the deleted salesperson's sales
orders are orphaned in the SalesOrderHeader table without a link to the
data in the SalesPerson table.
A FOREIGN KEY constraint prevents this situation.*THE CONSTRAINT ENFORCES
REFERENTIAL INTEGRITY BY GUARANTEEING THAT CHANGES CANNOT BE MADE TO DATA
IN THE PRIMARY KEY TABLE IF THOSE CHANGES INVALIDATE THE LINK TO DATA IN
THE FOREIGN KEY TABLE. IF AN ATTEMPT IS MADE TO DELETE THE ROW IN A PRIMARY
KEY TABLE OR TO CHANGE A PRIMARY KEY VALUE, THE ACTION WILL FAIL WHEN THE
DELETED OR CHANGED PRIMARY KEY VALUE CORRESPONDS TO A VALUE IN THE FOREIGN
KEY CONSTRAINT OF ANOTHER TABLE*. To successfully change or delete a row in
a FOREIGN KEY constraint, you must first either delete the foreign key data
in the foreign key table or change the foreign key data in the foreign key
table, which links the foreign key to different primary key data.

Hope this helps, please let me know if this is the root cause of your
issue. I will follow up. It's my pleasure to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hello Wen

Sorry for thr delay as I was away on Friday.

You understood the problem quite well.
Sample is the parent table and SamplePrep is the child table. sampleID is
the key field. Both the tables have records for sampleID = bbb01 and I am
changing it to sampleID = dddd01. To do that I am using Begin Transaction as
you mentioned that it does not alow to change the key field if there is a
record in the child table. The stored procedure code is

BEGIN TRY
BEGIN TRANSACTION

UPDATE Sample
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 10
END


UPDATE SamplePrep
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 11
END

COMMIT TRANSACTION

RETURN
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH


It is giving the following erroe message:

"Transaction count after EXECUTE indicates that a COMMINT or ROLLBACK
TRANSACTION statement is missing. Previous count = 0 , current count=1."

And it is not changing the sampleID value.

Thanks for yoyr help.

Manjree.

Hello Manjree,
Thanks for your reply.

According to your description, your issue is that you cannot execute the
procedure and receive the error message about the update statement
Conflicted with FK constraint. Please don't hesitate to correct me, if I
misunderstood anything here.

From the error message and your pre-reply, there is a relationship between
Sample and SamplePref by sampleID. The Sample table is the Parent table and
SamplePref is the Child table. Please let me know if I'm wrong.

Regarding to the first update statement:

UPDATE Sample
SET sampleID = 'ddddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)

Have you checked if there is any row (which sampleID is 'bbb01' ) in the
SamplePref table?

Due to the FK constraint, each row of Sample has to link to the Parent
Table (SampleRref).
If there is any row whose sampleID is 'bbb01' in SamplePref, we cannot
change the parent row from "bbb01" to 'ddddd01'. Otherwise, the FK
constraint error will be thrown.

You may check the following document.
http://msdn2.microsoft.com/en-us/library/ms175464.aspx
[FOREIGN KEY Constraints]

Although the main purpose of a FOREIGN KEY constraint is to control the
data that can be stored in the foreign key table, it also controls changes
to data in the primary key table. For example, if the row for a salesperson
is deleted from the Sales.SalesPerson table, and the salesperson's ID is
used for sales orders in the Sales.SalesOrderHeader table, the relational
integrity between the two tables is broken; the deleted salesperson's sales
orders are orphaned in the SalesOrderHeader table without a link to the
data in the SalesPerson table.
A FOREIGN KEY constraint prevents this situation.*THE CONSTRAINT ENFORCES
REFERENTIAL INTEGRITY BY GUARANTEEING THAT CHANGES CANNOT BE MADE TO DATA
IN THE PRIMARY KEY TABLE IF THOSE CHANGES INVALIDATE THE LINK TO DATA IN
THE FOREIGN KEY TABLE. IF AN ATTEMPT IS MADE TO DELETE THE ROW IN A PRIMARY
KEY TABLE OR TO CHANGE A PRIMARY KEY VALUE, THE ACTION WILL FAIL WHEN THE
DELETED OR CHANGED PRIMARY KEY VALUE CORRESPONDS TO A VALUE IN THE FOREIGN
KEY CONSTRAINT OF ANOTHER TABLE*. To successfully change or delete a row in
a FOREIGN KEY constraint, you must first either delete the foreign key data
in the foreign key table or change the foreign key data in the foreign key
table, which links the foreign key to different primary key data.

Hope this helps, please let me know if this is the root cause of your
issue. I will follow up. It's my pleasure to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Hello Manjree,

Because the Update Query is in TRY-Catch, "RollBack Tran" in (if@@error<>0)
have no chance to be execute. When there is an error in the transaction,
stored procedure will pump to the Catch.

The correct way is that we should have to rollback the transaction in Catch.
The following code snippet will achieve what you need.

alter proc test_proc
as
BEGIN TRY
BEGIN TRANSACTION
Declare @returnMessage varchar(50)

set @returnMessage = '10'
UPDATE Sample
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'

set @returnMessage = '11'
UPDATE SamplePrep
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'

COMMIT TRANSACTION
Return 1

End Try
BEGIN CATCH
SELECT
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
If @errormessage = '10'
begin
Rollback tran
return 10
end
else
begin
rollback tran
return 11
end
END CATCH


Hope this helps. Please try the above method and let me know if this method
works for you. I'm glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hello Wen

Unfortunately, the procedure is not working. When I tried it in SQL server
it's giving the following error message same as I mentioned earlier:

The UPDATE statement conflicted with the REFERENCE constraint
"FK_SamplePrep_Sample". The conflict occurred in database "Protien", table
"dbo.SamplePrep", column 'sampleID'.

I don't get it. If I am using Begin Transaction then why it's conflicting
with the REFERENCE constraint?

Thanks for your help.

Manjree

 

[WenYuan Wang [MSFT]]

Hello Manjree,

Would you please send me a Screen shooting of the Error and the exact
Stored Procedure which you tried in SQL server?
I'm a little confused. the exception has been caught in procedure. It
should not be thrown when you executed it in SQL Server.
I need more detailed information such as the Screen shooting. Many thanks.

My alias is (e-mail address removed)

If you have any more concern, please feel free to let me know.

Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.