Adding new data in two tables [modified]
-
hey guys..i try to insert data in two tables which are related with each other... the first table name is Mark and the columns mark_id and mark_name and the second table name is Model the columns are model_id,Model_name and mark_id and mark_id is FK here now here is the thing i want to do for example i want to add Honda as Mark to mark table and the model Jazz,Civic etc as models that depends on the HOnda..To do that i created a stored Procedure like below Create Procedure sp_TablolaraEkle @marka_ad nvarchar(50), @model_ad nvarchar(50) as Begin Try Begin Transaction Set Nocount on Declare @id int Declare @Marka nvarchar(50) insert into Marka(marka_ad) values (@marka_ad) Select @id=@@Identity Declare @model_adi nvarchar(50) insert into Model(marka_id,model_ad)values (@id,@model_ad) Commit End Try Begin Catch if @@Trancount>0 Rollback End Catch but the problem is for example i added Ferrari to mark table with id is 3 i can add its model too but when i want to add one more Ferrari model then it adds Ferrari model with different mark_id in model table can u help me how it will get rid off the problem...thanks for your help
modified on Tuesday, October 5, 2010 5:28 AM
-
hey guys..i try to insert data in two tables which are related with each other... the first table name is Mark and the columns mark_id and mark_name and the second table name is Model the columns are model_id,Model_name and mark_id and mark_id is FK here now here is the thing i want to do for example i want to add Honda as Mark to mark table and the model Jazz,Civic etc as models that depends on the HOnda..To do that i created a stored Procedure like below Create Procedure sp_TablolaraEkle @marka_ad nvarchar(50), @model_ad nvarchar(50) as Begin Try Begin Transaction Set Nocount on Declare @id int Declare @Marka nvarchar(50) insert into Marka(marka_ad) values (@marka_ad) Select @id=@@Identity Declare @model_adi nvarchar(50) insert into Model(marka_id,model_ad)values (@id,@model_ad) Commit End Try Begin Catch if @@Trancount>0 Rollback End Catch but the problem is for example i added Ferrari to mark table with id is 3 i can add its model too but when i want to add one more Ferrari model then it adds Ferrari model with different mark_id in model table can u help me how it will get rid off the problem...thanks for your help
modified on Tuesday, October 5, 2010 5:28 AM
The procedure will always return a success. Since you catch any exception that occurs and rollback the SQL statement to insert data. Your procedure basically inserts a row in the Marka table with the value passed to the stored procedure. Then it attempts to create a new record in Model based upon the identity field of the Marka table (@@identity). There are a few weird things though. You never assign any value to @model_ad when you are using it to create a new record in Model. This could cause an issue. To find out what exactly is going wrong remove the 'begin try', 'end try', 'begin catch' and 'end catch'. This should display the error, but remember to put these things back afterwards.
-
The procedure will always return a success. Since you catch any exception that occurs and rollback the SQL statement to insert data. Your procedure basically inserts a row in the Marka table with the value passed to the stored procedure. Then it attempts to create a new record in Model based upon the identity field of the Marka table (@@identity). There are a few weird things though. You never assign any value to @model_ad when you are using it to create a new record in Model. This could cause an issue. To find out what exactly is going wrong remove the 'begin try', 'end try', 'begin catch' and 'end catch'. This should display the error, but remember to put these things back afterwards.
hii friends..thanks for answer..i did what u said it has given error and i fxed it now...but still my basic problem goes on...is it becasue the id column is identity in Mark table ? when i add a new row it increase by 1 although i have same data already...For example i save a new data MArk as Fiat and the model is Linea it gives number 16 as id in mark table and it goes to model atble and it writes there too...it is ok till now..but when i want to add one more data mark as Fiat this time give number 17 as id and in model table too.. i want it to be 16...how i will manage it ??
-
hii friends..thanks for answer..i did what u said it has given error and i fxed it now...but still my basic problem goes on...is it becasue the id column is identity in Mark table ? when i add a new row it increase by 1 although i have same data already...For example i save a new data MArk as Fiat and the model is Linea it gives number 16 as id in mark table and it goes to model atble and it writes there too...it is ok till now..but when i want to add one more data mark as Fiat this time give number 17 as id and in model table too.. i want it to be 16...how i will manage it ??
Before adding the Mark record (ferarri) you need to check if the is an existing record
Select @ID = MarkID from MarkTable where Model = 'ferarri'
If isnull(@id,0) = 0
begin
Insert both records
end
else
begin
insert FK record only
endNever underestimate the power of human stupidity RAH
-
Before adding the Mark record (ferarri) you need to check if the is an existing record
Select @ID = MarkID from MarkTable where Model = 'ferarri'
If isnull(@id,0) = 0
begin
Insert both records
end
else
begin
insert FK record only
endNever underestimate the power of human stupidity RAH
Alter Procedure sp_TablolaraEkle @marka_ad nvarchar(50), @model_ad nvarchar(50) as Begin Set Nocount on Declare @id int Declare @Marka nvarchar(50) Select @id=id From Marka where marka_ad=@marka_ad IF isnull(@id,0)=0 BEGIN INSERT INTO Marka(marka_ad) values (@marka_ad) SELECT @id=@@Identity insert into Model(marka_id,model_ad)values (@id,@model_ad) END Else BEGIN insert into Model (marka_id,model_ad) values(@id,@model_ad) END End ok man i altered my procedure like it and it works now as i wished..thanks for your help...