hi how to update two tables record together using stored procedure ??or simple query?

Question

in my project in aspx page while saving am inserting to two tables one is tblcurrencysale and tblAssets
tblcurrencysale(csID(pk,autoincrement),csPurchaseAmount,csSoldamt,csprofit,csDate)
tblAssets(aID(pk,autoincre),aCash,aDate)
and in tblAssets am adding aCash from csprofit and aDate from csDate..
this is the saving procedure..and i created sp and its working fine..
but while updating ,updating only in tblcurrencysale table.i gave sp..

Collapse | Copy Code

UpdateAllPL objPL=new UpdateAllPL();  objPL.PurchaseAmount=tbPurchase.Text;  objPL.SoldAmount=tbSold.Text;  objPL.Profit=tbProfit.Text;  objPL.Date=tbDate.Text;  UpdateAllBLL objBLL=new UpdateAllBLL();  objBLL.UpdateCurrencySale(objPL);  objPL.aCash=tbProfit.Text;  objPL.aDate=tbDate.Text;  objBLL.UpdateAssets(objPL);  lblMessage.Text="Updated SuccessFully...";

here is my sp am callin from class file..but Assets table is not updating ..

so can u suggest me wat have to do?

really it will help for me..thanks

and here is the sp which i created
create proc ups_UpdateCurrencySale @PurchaseAmount varchar(50), @SalesAmount varchar(50), @Profit varchar(50), @Date varchar(50) AS BEGIN Update tblCurrencySale set csPurchaseAmount=@PurchaseAmount,csSalesAmount=@SalesAmount,csProfit=@Profit,csDate=@Date where csPurchaseAmount=@PurchaseAmount end

sp for assets table
create procedure usp_UpdateAssets @Cash varchar(50), @Date varchar(50) AS BEGIn update tblAssets set aCash=@Cash,aDate=@Date where aCash=@Cash end

Kunal Vaishya · Answer

Dear you sp i dont thing is there any bug you should confirm that waht is date field data type if DateTime then you can trash there may be its possible Second thing now need to update aCash in tblAssets table and csPurchaseAmount in tblCurrencySale remove that field from update query Update tblAssets Set aDate = @Date Where aCash = @Cash Update tblCurrencySale Set csSalesAmount=@SalesAmount,csProfit=@Profit,csDate=@Date where csPurchaseAmount=@PurchaseAmount

Jignesh Trivedi · Answer

Hi, I think problem with your SP. your SP for asset table Look.. create procedure usp_UpdateAssets @Cash varchar(50), @Date varchar(50) AS BEGIn update tblAssets set aCash=@Cash,aDate=@Date where aCash=@Cash end In this sp if your @cash has only update value that not present in DB. Example. suppose acash value is 500 db now its update to 501 now you pass this value to SP but 501 does not original record. hope we both are on same track.

Kunal Vaishya · Answer

Dear check sp is proper or not becoze sql run query what we give to sql nothing else if you getting still bug then give me page source and parameter so we can help you

hi how to update two tables record together using stored procedure ??or simple query?

Answers (3)