数据未更新,尽管 SQL Server 存储过程返回正结果。

huangapple go评论73阅读模式
英文:

Data is not updated despite SQL Server stored procedure returns positive result

问题

Izmena_Korisnika 方法的代码看起来没有明显的错误。如果在编辑用户页面 edituser.aspx 中的数据没有更新,而在 myaccount.aspx 页面中却能正常工作,这可能是由于以下原因之一:

  1. 数据绑定问题: 请确保在 edituser.aspx 页面中,你在页面加载时正确地绑定了用户的数据到文本框和下拉框中。检查页面加载事件,确保你从 Session["EditMail"] 中获取了正确的用户邮箱,并将相关数据填充到页面控件中。

  2. 按钮点击事件问题: 请确保“保存更改”按钮的点击事件 btnSaveChanges_Click 被正确触发。你可以通过在按钮上设置断点或在事件方法中添加日志来进行调试。

  3. 数据库连接问题: 确保数据库连接字符串 wqbConfig 是正确的,而且数据库连接没有问题。在 Izmena_Korisnika 方法中,你已经打开和关闭了数据库连接,但要确保这一部分没有问题。

  4. SQL 存储过程问题: 确保你的 SQL 存储过程 dbo.Korisnik_Izmeni 是正确的,它在 myaccount.aspx 中工作,但可能在 edituser.aspx 中存在问题。

  5. 异常处理:Izmena_Korisnika 方法中,检查异常处理部分,确保没有异常被抛出,因为这可能会导致更新失败但不显示错误消息。

如果你仍然无法找到问题,建议添加更多的调试输出,以便更详细地了解代码在哪里失败。这可能包括输出日志、控制台消息或使用调试工具来检查变量的值和代码的执行路径。

英文:

I am making a web store in ASP.NET with a SQL Server database. I'm currently working on a "edit users" part of control panel. I have list of all users and "edit" and "delete" buttons for each row of a list. When edit button is clicked I put mail of a clicked user in Session["EditMail"] and redirect to edituser.aspx where I load user's data in textboxes and his role in dropdown. In the bottom is "save changes" button. When button is clicked it activate this method:

protected void btnSaveChanges_Click(object sender, EventArgs e)
{
        string name = txtName.Text;
        string surname = txtSurname.Text;
        string username = txtUsername.Text;
        string email = txtEmail.Text;
        string password = txtPassword.Text;
        string country = txtCountry.Text;
        string city = txtCity.Text;
        int postCode = Convert.ToInt32(txtPostCode.Text);
        string address = txtAddress.Text;

        int user_role = Convert.ToInt32(DropDownListRole.SelectedItem.Value);

        WebShop moj_nalog1 = new WebShop();
        int izmena_korisnika = moj_nalog1.Izmena_Korisnika(name, surname, username, password,     mail, country, city, postCode, address, user_role);

        if (izmena_korisnika == 0)
        {
            Session["EditMail"] = email;
            Response.Redirect("edituser.aspx");
        }
        else
        {
            lblError.Visible = false;
            lblError.Text = "Error!";
            lblError.CssClass = "text-danger";
            lblError.Visible = true;
        }
}

This is Izmena_Korisnika (Edit user in english) method in WebShop.cs:

public int Izmena_Korisnika(string name, string surname, string username, string password, string email, string country, string city, int post_code, string address, int role_id)
{
        conn.ConnectionString = wqbConfig;
        int result;

        comm.Connection = conn;
        comm.CommandType = CommandType.StoredProcedure;
        comm.CommandText = "dbo.Korisnik_Izmeni";
        // kolekcija Parameters
        comm.Parameters.Add(new SqlParameter("@ime", SqlDbType.NVarChar, 100,                   ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, name));
        comm.Parameters.Add(new SqlParameter("@prezime", SqlDbType.NVarChar, 100,        ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, surname));
        comm.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, username));
        comm.Parameters.Add(new SqlParameter("@lozinka", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, password));
        comm.Parameters.Add(new SqlParameter("@email", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, email));
        comm.Parameters.Add(new SqlParameter("@drzava", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, country));
        comm.Parameters.Add(new SqlParameter("@grad", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, city));
        comm.Parameters.Add(new SqlParameter("@postanski_br", SqlDbType.Int, 5, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, post_code));
        comm.Parameters.Add(new SqlParameter("@adresa", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, address));
        comm.Parameters.Add(new SqlParameter("@uloga_korisnika_id", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, role_id));
        comm.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, true, 0, 0, "", DataRowVersion.Current, null));

        conn.Open();
        comm.ExecuteNonQuery();
        conn.Close();

        int Ret;

        Ret = (int)comm.Parameters["@RETURN_VALUE"].Value;

        if (Ret == 0)
        {
            result = 0;
        }
        else
        {
            result = 1;
        }

        return result;
}

And this is the SQL Server stored procedure dbo.Korisnik_Izmeni:

CREATE PROCEDURE Korisnik_Izmeni
    @ime nvarchar(100),
    @prezime nvarchar(100),
    @username nvarchar(30),
    @lozinka nvarchar(255),
    @email nvarchar(50),
    @drzava nvarchar(100),
    @grad nvarchar(100),
    @postanski_br int,
    @adresa nvarchar(255),
    @uloga_korisnika_id int
AS
    SET LOCK_TIMEOUT 3000;

BEGIN TRY
   	IF EXISTS (SELECT TOP 1 ime FROM Korisnici
	           WHERE email = @email)
	BEGIN
    	UPDATE Korisnici 
        SET ime = @ime, 
            prezime = @prezime, 
            username = @username, 
            lozinka = @lozinka, 
            drzava = @drzava, 
            grad = @grad, 
            postanski_br = @postanski_br, 
            adresa = @adresa, 
            uloga_korisnika_id = @uloga_korisnika_id 
        WHERE email = @email

		RETURN 0;
	END
	RETURN -1;
END TRY
BEGIN CATCH
	RETURN @@ERROR;
END CATCH

When I click the button data does not update. No error or exception is shown, lblError also does not become visible which means that izmena_korisnika in btnSaveChanges_Click is equal to 0. How is it then possible that data does not update

I have almost same code (minus dropdown for role) in myaccount.aspx page that is also for editing user's data. It works there but do not work in edituser.aspx.

答案1

得分: 0

在TSQL和.NET中,不要使用返回值来指示成功或失败。在.NET中使用异常,在TSQL中使用错误(THROW/RAISERROR)。

即使存储过程在错误时返回非零值,也不应依赖存储过程的返回值来确定成功或失败。相反,应该抛出错误,并通过没有错误来确定成功。客户端无法正确解释返回值的风险太高。

使用存储过程的返回值来确定成功或失败是一个旧的模式,早于TSQL TRY/CATCH,并且从未建议从客户端代码中使用。所以在CATCH块中使用THROW或完全省略TRY/CATCH。

英文:

In both TSQL and .NET DO NOT use return values to indicate success or failure. Use Exceptions in .NET and Errors (THROW/RAISERROR) in TSQL.

And even if the stored procedure returns a non-zero value on error, you should never rely on stored procedure return values to determine success or failure. Instead throw errors, and determine success by the lack of an error. The risk that the client won't correctly interpret the return value is too high.

Using stored procedure return values to determine success or failure is an old pattern that predates TSQL TRY/CATCH, and was never recommended from client code. So just THROW in the CATCH block or omit the TRY/CATCH entirely.

答案2

得分: 0

以下是您要的代码的中文翻译:

好的,你开始在这里创建一个“混乱”。

让我们整理一下。

试着这样说:

public int Izmena_Korisnika(string name, 
    string surname, 
    string username, 
    string password, 
    string email,
    string country,
    string city,
    int post_code, 
    string address, 
    int role_id)

{
    int iResult = 0;
    using (SqlConnection conn = new SqlConnection(wqbConfig)) {
        using (SqlCommand comm = new SqlCommand("", conn))
        {
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "dbo.Korisnik_Izmeni";

            // 参数集合
            comm.Parameters.Add("@ime", SqlDbType.NVarChar).Value = name;
            comm.Parameters.Add("@prezime", SqlDbType.NVarChar).Value = surname;
            comm.Parameters.Add("@username", SqlDbType.NVarChar).Value = username;
            comm.Parameters.Add("@lozinka", SqlDbType.NVarChar).Value = password;
            comm.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
            comm.Parameters.Add("@drzava", SqlDbType.NVarChar).Value = country;
            comm.Parameters.Add("@grad", SqlDbType.NVarChar).Value = city;
            comm.Parameters.Add("@postanski_br", SqlDbType.Int).Value = post_code;
            comm.Parameters.Add("@adresa", SqlDbType.NVarChar).Value = address;
            comm.Parameters.Add("@uloga_korisnika_id", SqlDbType.Int).Value = role_id;
            comm.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.Output;

            comm.ExecuteNonQuery();
            iResult = (int)comm.Parameters["@RETURN_VALUE"].Value;
        }
    }

    return iResult;
}

请注意,翻译中尽力保持了代码的格式和结构。

英文:

Ok, you starting to create a "mess" here.

Let's clean this up.

Try say like this:

    public int Izmena_Korisnika(string name, 
        string surname, 
        string username, 
        string password, 
        string email,
        string country,
        string city,
        int post_code, 
        string address, 
        int role_id)

    {
        int iResult = 0;
        using (SqlConnection conn = new SqlConnection(wqbConfig)) {
            using (SqlCommand comm = new SqlCommand("", conn))
            {
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = "dbo.Korisnik_Izmeni";

                // kolekcija Parameters
                comm.Parameters.Add("@ime", SqlDbType.NVarChar).Value = name;
                comm.Parameters.Add("@prezime", SqlDbType.NVarChar).Value = surname;
                comm.Parameters.Add("@username", SqlDbType.NVarChar).Value = username;
                comm.Parameters.Add("@lozinka", SqlDbType.NVarChar).Value = password;
                comm.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
                comm.Parameters.Add("@drzava", SqlDbType.NVarChar).Value = country;
                comm.Parameters.Add("@grad", SqlDbType.NVarChar).Value = city;
                comm.Parameters.Add("@postanski_br", SqlDbType.Int).Value = post_code;
                comm.Parameters.Add("@adresa", SqlDbType.NVarChar).Value = address;
                comm.Parameters.Add("@uloga_korisnika_id", SqlDbType.Int).Value = role_id;
                comm.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.Output;

                comm.ExecuteNonQuery();
                iResult = (int)comm.Parameters["@RETURN_VALUE"].Value;
            }
        }

        return iResult;
    }

huangapple
  • 本文由 发表于 2023年5月22日 02:01:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76301243.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定