如何编写更新查询以使用 SQL 数据源更新两个表? [英] How to write update query to update two tables with SQL Data Source?

问题描述

是否可以使用 SQL 数据源和 ASP.NET 网格视图更新两个表?对于 Select 语句，我有以下 SQL 查询.

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

但是我不知道如何在 SQL 数据源中编写更新和删除语句

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

更新

所以我写了存储过程.

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

但我得到 过程或函数 UpdateTwoTable 指定的参数太多.

更新

我遵循了本指南，现在问题解决了.感谢所有帮助过的人！

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/

下面是我的 SQL 数据源.

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>

推荐答案

是的，这是可能的.以下是如何以安全的方式执行此操作:

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.

更多关于 Transactions 的使用，请访问:http://www.4guysfromrolla.com/webtech/080305-1.shtml

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

基本的DELETE语句是:DELETE FROM WHERE <条件>

这篇关于如何编写更新查询以使用 SQL 数据源更新两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！