How to write update query to update two tables wit

问题:

Is it possible to update two tables using SQL Data Source and ASP.NET Grid View? I have the following SQL Query for the Select statement.

SELECT 
   tbl_user_login.ID, tbl_user_login.UserID, 
   tbl_user_login.Pass, tbl_user_login.Enabled, 
   tbl_user_login.Permission, tbl_user_login.Rank, 
   tbl_user_profile.ID AS Expr1, tbl_user_profile.FName,
   tbl_user_profile.LName, tbl_user_profile.Phone, 
   tbl_user_profile.Email1, tbl_user_profile.Email2 
FROM 
   tbl_user_login 
INNER JOIN 
   tbl_user_profile ON tbl_user_login.ID = tbl_user_profile.ID

But I've no idea how do I write the update and delete statement in SQL Data Source

UPDATE

So I wrote the store procedure.

CREATE PROCEDURE UpdateTwoTable 
(
    @ID int, 
    @UserID varchar(10), 
    @Pass varchar(50), 
    @Enabled int, 
    @Permission int,
    @Rank int,
    @FName varchar(50),
    @LName varchar(50),
    @Phone varchar(50),
    @Email1 varchar(50),
    @Email2 varchar(50)
) AS

BEGIN TRANSACTION

UPDATE tbl_user_login SET UserID = @UserID, Pass = @Pass, Enabled = @Enabled, Permission = @Permission, Rank = @Rank WHERE ID = @ID

IF @@ERROR <> 0
BEGIN
    ROLLBACK
    RETURN
END

UPDATE tbl_user_profile SET FName = @FName, LName = @LName, Phone = @Phone, Email1 = @Email1, Email2 = @Email2 WHERE ID = @ID

IF @@ERROR <> 0
BEGIN
    ROLLBACK
    RETURN
END

COMMIT

But I get the Procedure or function UpdateTwoTable has too many arguments specified.

UPDATE

I followed this guide and now the problem is solved. Thanks to everyone who helped!

http://www.whitworth.org/2006/01/16/how-to-troubleshoot-procedure-or-function-has-too-many-arguments-specified-in-aspnet-20/

Below is my SQL Data Source.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DBConnString %>" 
        SelectCommand="SELECT tbl_user_login.ID, tbl_user_login.UserID, tbl_user_login.Pass, tbl_user_login.Enabled, tbl_user_login.Permission, tbl_user_login.Rank, tbl_user_profile.ID AS Expr1, tbl_user_profile.FName,
tbl_user_profile.LName, tbl_user_profile.Phone, tbl_user_profile.Email1, tbl_user_profile.Email2 FROM tbl_user_login INNER JOIN tbl_user_profile ON tbl_user_login.ID = tbl_user_profile.ID" 
        UpdateCommand="UpdateTwoTable" UpdateCommandType="StoredProcedure"
        OldValuesParameterFormatString="Original_{0}">
        <UpdateParameters>
            <asp:Parameter Name="ID" />
            <asp:Parameter Name="UserID"/>
            <asp:Parameter Name="Pass"/>
            <asp:Parameter Name="Enabled"/>
            <asp:Parameter Name="Permission"/>
            <asp:Parameter Name="Rank"/>
            <asp:Parameter Name="FName"/>
            <asp:Parameter Name="LName"/>
            <asp:Parameter Name="Phone"/>
            <asp:Parameter Name="Email1"/>
            <asp:Parameter Name="Email2"/>
        </UpdateParameters>        
    </asp:SqlDataSource>

回答1:

Yes, it's possible. Here is how to do it in a safe way:

CREATE PROCEDURE UpdateUser (@ID int, @Pass varchar(15), @Email varchar(75)) AS

BEGIN TRANSACTION

    UPDATE tbl_user_login SET Pass = @Pass WHERE ID = @ID

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK
        RETURN
    END

    UPDATE tbl_user_profile SET Email1 = @Email WHERE ID = @ID

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK
        RETURN
    END

COMMIT

In this way you don't need to be worried in case there is some error raised by any of the updates. Which means: If any of them will fail, the whole operation will be canceled and you will not have inconsistent data in your DB.

More about the use of Transactions here: http://www.4guysfromrolla.com/webtech/080305-1.shtml

The basic DELETE statement is: DELETE FROM <tablename> WHERE <condition>

回答2:

CREATE PROC UpdateUser
(
    @ID int
    ,@Pass varchar(15)
    ,@Email varchar(75)
   ) AS
BEGIN

Update
   tbl_user_login
Set
   Pass = @Pass
Where
   ID = @ID

Update
  tbl_user_profile
Set
  Email1 = @Email
Where
  ID = @ID

END

You can write multiple sql commands into a single sproc as I have above. Expand updates as needed. Deletes work the same way...

回答3:

<asp:SqlDataSource runat="server":
    SelectCommand="SELECT ... "
    UpdateCommand="UPDATE a; UPDATE b;"
    DeleteCommand="DELETE FROM a; DELETE FROM b;" />

Doesn't it work?