MSSQL update multiple tables

[met0555]

Hi,

I would be more than happy, if someone can provide me a simple example of stored procedure to update 2 tables in one stored procedure.

thanks

[GaryMazzone]

You simply place two different update statements (1 for each table ) in the SP

[met0555]

i never used a SP, i used directly the commands... from my sqldatasource.... select * .... , how will it look like in the SP, because there are some addtional code in it.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

[sapator]

You could just google it.
Here is the first link i get.
http://webforumz.com/databases/45639...-procedure.htm

[dee-u]

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
	INSERT INTO Table1 (column1,column2) VALUES(value1,Value2)
	INSERT INTO Table2 (column1,column2) VALUES(value1,Value2)
END
GO

To insert records in multiple tables you can just place the t-sql there.

[met0555]

Hi,

I tried to make one, well it updates the first table bot not the second one.
My code is as follow:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE updatereservation2(	@PassengerId bigint,	@FirstName varchar(50),@LastName varchar(50),@E_mail varchar(50),@Mobile varchar(50),
@VehicleInfoId bigint,	@VehicleType varchar(100),@ServiceDateTime datetime,@PickupLocation text,@DropLocation text, @ExtraInfo text
)


AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
UPDATE    dbo.TblPassangerInformation
SET              FirstName = @FirstName, LastName = @LastName, E_mail = @E_mail, Mobile = @Mobile
FROM         dbo.TblPassangerInformation 
WHERE     (dbo.TblPassangerInformation.PassengerId = @PassengerId)

UPDATE    dbo.TblVehicleInformation
SET              VehicleType = @VehicleType, ServiceDateTime = @ServiceDateTime, PickupLocation = @PickupLocation, DropLocation = @DropLocation, ExtraInfo = @ExtraInfo
                 FROM                  dbo.TblVehicleInformation
WHERE     (dbo.TblVehicleInformation.VehicleInfoId = @VehicleInfoId)



END
GO

[sapator]

Are you sure you got lines on the table?
You must have a row in order to update it.

[met0555]

yes i do, i can see it in my grid view

[sapator]

Hi.
Better forget gridview and such for a moment.
Just do this on sql server.
Ok so you pass VehicleInfoId=@VehicleInfoId. What is the @VehicleInfoId value?
Don't answer that.Just do a
select * from TblVehicleInformation
Where TblVehicleInformation.VehicleInfoId=
and provide the @VehicleInfoId
Do you get a row?

[met0555]

Hi,

I just test it and yes i got a row. Maybe in the gridview in the select command i joined 3 tables, shud i do the same in the update?

SELECT     dbo.TblPassangerInformation.PassengerId, dbo.TblPassangerInformation.FirstName, dbo.TblPassangerInformation.LastName, dbo.TblPassangerInformation.E_mail, 
                      dbo.TblReservationInvoice.Approved, dbo.TblVehicleInformation.VehicleType, dbo.TblVehicleInformation.ServiceDateTime, 
                      dbo.TblPassangerInformation.ReservationId, dbo.TblVehicleInformation.PickupLocation, dbo.TblVehicleInformation.DropLocation, dbo.TblVehicleInformation.ExtraInfo, 
                      dbo.TblPassangerInformation.Mobile, dbo.TblVehicleInformation.VehicleInfoId
FROM         dbo.TblReservationInvoice INNER JOIN
                      dbo.TblPassangerInformation ON dbo.TblReservationInvoice.ReservationId = dbo.TblPassangerInformation.ReservationId INNER JOIN
                      dbo.TblVehicleInformation ON dbo.TblPassangerInformation.ReservationId = dbo.TblVehicleInformation.ReservationId
ORDER BY dbo.TblPassangerInformation.ReservationId DESC

thx

[sapator]

Well in the sql you posted if you put before order by,
Where dbo.TblVehicleInformation.VehicleInfoId="your value" do you get a row?
If so then do so in the update.

[met0555]

sorry i didn't get what you mean, do i have to use order by in the update command too?

[sapator]

No not in the update.
I meant if this

   
      SELECT     dbo.TblPassangerInformation.PassengerId, dbo.TblPassangerInformation.FirstName, dbo.TblPassangerInformation.LastName, dbo.TblPassangerInformation.E_mail,
   
                            dbo.TblReservationInvoice.Approved, dbo.TblVehicleInformation.VehicleType, dbo.TblVehicleInformation.ServiceDateTime,
   
                            dbo.TblPassangerInformation.ReservationId, dbo.TblVehicleInformation.PickupLocation, dbo.TblVehicleInformation.DropLocation, dbo.TblVehicleInformation.ExtraInfo,
   
                            dbo.TblPassangerInformation.Mobile, dbo.TblVehicleInformation.VehicleInfoId
   
      FROM         dbo.TblReservationInvoice INNER JOIN
   
                            dbo.TblPassangerInformation ON dbo.TblReservationInvoice.ReservationId = dbo.TblPassangerInformation.ReservationId INNER JOIN
   
                            dbo.TblVehicleInformation ON dbo.TblPassangerInformation.ReservationId = dbo.TblVehicleInformation.ReservationId
   Where dbo.TblVehicleInformation.VehicleInfoId="your value" 
      ORDER BY dbo.TblPassangerInformation.ReservationId DESC

will give you a row ("your value of course being the value you need, u use for update later).

[met0555]

that's what i 've been doing, but the grid view is not updating it, will just show back previous value, now it don't even update the first table.

[sapator]

Please do EVERYTHING in sql and then we will see what is the gridview problem.
I have to sleep in a while...

[met0555]

ok, i executed the commands in MSSQL

when i run the select command in SQL i get rows....
when i run the update query in sql ... the table get updated

UPDATE    dbo.TblVehicleInformation
SET              VehicleType = 'test'
WHERE     (VehicleInfoId = 75)

when i execute the SP nothing happens just says

Command(s) completed successfully.

thanks

[sapator]

I did not understand.
You get rows and data...And then?

[met0555]

i used the id i got from the result in the update commands

[sapator]

Sorry.What id?The pk id?
And for what u used it?
Can you be a little more specific?
If your update works then what is the problem?

[met0555]

When i use my SP in my grid view it's not working, the results do not get updated.

[sapator]

Hmm.
If the sp works on sql then clearly the problem is on how you pass your data on the gridview.
Are you using standard ado or DAL?
Maybe a new post on the on vb.net or here would be better because i think the problem is on how you integrate your sp to the grid.
If you are using datasets try to set the auto increment property to "-1".