MSSQL update multiple tables

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

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

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.

Code:

---

-- ================================================
-- 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

---

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

Code:

---

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.

Hi,

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

Code:

---

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

---

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

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

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?

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?

vb Code:

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

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.

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

No not in the update.
I meant if this

Code:

---

 
      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).

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.

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

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

vb Code:

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

when i execute the SP nothing happens just says

Code:

---

Command(s) completed successfully.

---

thanks

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

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

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?

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

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".