Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 12109
Home > Forums > Sql Server >

Problem with Update statement

Posted by Lakhangarg under Sql Server on 11/26/2009 | Views : 2433 | Status : [Member] [Moderator] | Replies : 12
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
Hello Friends-

I have faced a problem related to Update Statement in SQL Server.
My SQL Statement is:
DECLARE @OrgID INT
SET @OrgID=10
UPDATE MYTable Set OrgID=@OrgID WHERE CountryID=@CountryID, StateID=@StateID and OrgName=@OrgName

@CountryID,@StateID,@OrgName are the three parameters that i need to pass from application.

Suppose i have one Column Name UserID in MyTable and that is having Foreign key relation with the User Table.
the above statement is working fine when record exists for the above given condition. But when there is no row exists for the above condition its giving error related to Foreign key relation of UserID with User Table. if i'll add the if exists before Update then its working fine.

But Problem is, when there is no record to update then why its giving me error related to UserID (Foreign key).

Thanks & Regards
Lakhan Pal Garg



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Vuyiswamb on: 11/26/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
can i see your Error ?

Thank you for posting at Dotnetfunda
[Administrator]

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Lakhangarg on: 11/26/2009 [Member] [Moderator] Silver

Up
0
Down
Hi This is the Error Message:

Msg 547, Level 16, State 0, Procedure ProfileInfo_InsertCurrentExp_Trg, Line 45
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProfileInfo_REGISTRATION". The conflict occurred in database "MyDB", table "dbo.tbl_WZQ_REGISTRATION", column 'UserID'.
The statement has been terminated.



Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Abhijit Jana on: 11/26/2009 [Member] [MVP] Bronze

Up
0
Down
If your multiple table having same column, you have to use TableName.Column name, that's why you are getting the error.

Cheers !
Abhijit

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Lakhangarg on: 11/26/2009 [Member] [Moderator] Silver

Up
0
Down
Hi Abhijit Jana-
I already mentioned the query. i am using single table. then how can i got the error related to multiple table.

any how thanks for your reply.

Thanks & Regards
Lakhan Pal Garg

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pandians on: 11/26/2009 [Member] [MVP] Silver

Up
0
Down
Hi Lakhangarg

- Is there any TRIGGER for MYTable table ? (If YES, Pl check the Trigger part)
(or)
- Pl check the REFERENTIAL Integrity on MYTable table(What are the tables are referred by this table, Mailny UserID columns from which table / Used by which column in MYTable).

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Lakhangarg on: 11/26/2009 [Member] [Moderator] Silver

Up
0
Down
HI-
There is No trigger.
:(

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Dbdon on: 11/27/2009 [Member] Starter

Up
0
Down
Hi Lakhan,

Please check and execute thsi query:

DECLARE @OrgID INT
SET @OrgID=10
UPDATE MYTable Set OrgID=@OrgID WHERE CountryID=@CountryID AND StateID=@StateID AND OrgName=@OrgName

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Lakhangarg on: 11/27/2009 [Member] [Moderator] Silver

Up
0
Down
Hi DBDon-

I Have Execute the Same query. that you have written. buy mistake i read comma (,) instead of And in my query.


Thanks & Regards
Lakhan Pal Garg

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Dbdon on: 11/27/2009 [Member] Starter

Up
0
Down
One more thing you need to try is, check if OrgId is a primary key in your table and if it had any foreign key in some other table.

If you have such references and want to update any key value, you are bound to get such foreign key voilation errors. to avaoid it you need to set your "Cascading" property OFF.

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Dbdon on: 11/27/2009 [Member] Starter

Up
0
Down
did that work or not?

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Lakhangarg on: 11/27/2009 [Member] [Moderator] Silver

Up
0
Down
Hi DbOn-

QrgID is not a priamry key. one more thing there is no matching record when i got this error. means nothing to update. when there is a record then its working fine.

Thanks & Regards
Lakhan Pal Garg

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Kumarsudu on: 11/27/2009 [Member] Starter

Up
0
Down
Hi,

You are trying to add a row in
violation of a foreign key. In other words,
MYtable has a column named UserId which is a foreign key to another
table. To insert a UserId , the value must exist in this other table

Please check this,

Hope this may help you to solve your problem

Thanks and Regards,
Kumar

Lakhangarg, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts