C# 更新 SQL Server 数据库中的数据

Update data in SQL Server database

提问by Ching

I have registration as my database table. I want to update my information that has key in by the user into my SQL Server database. But it won't work, it don't occur any errors but the data key in wouldn't update into my database. Someone please help me if anything wrong with my code? Thanks.

我已注册为我的数据库表。我想将用户键入的信息更新到我的 SQL Server 数据库中。但它不起作用，它不会发生任何错误，但数据键不会更新到我的数据库中。如果我的代码有任何问题，请有人帮助我？谢谢。

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("Data Source=USER-PC;Initial Catalog=webservice_database;Integrated Security=True");

    SqlCommand cmd = new SqlCommand("UPDATE registration SET username = @username, password = @password, retypepassword = @retypepassword, gender = @gender, birth = @birth, address = @address, city = @city, country = @country, postcode = @postcode, email = @email, carno = @carno", con);

    con.Open();

    cmd.Parameters.AddWithValue("@username", TextBoxUsername.Text);
    cmd.Parameters.AddWithValue("@password", TextBoxPassword.Text);
    cmd.Parameters.AddWithValue("@retypepassword", TextBoxRPassword.Text);
    cmd.Parameters.AddWithValue("@gender", DropDownListGender.Text);
    cmd.Parameters.AddWithValue("@birth", DropDownListDay.Text);
    cmd.Parameters.AddWithValue("@address", TextBoxAddress.Text);
    cmd.Parameters.AddWithValue("@city", TextBoxCity.Text);
    cmd.Parameters.AddWithValue("@country", DropDownListCountry.Text);
    cmd.Parameters.AddWithValue("@postcode", TextBoxPostcode.Text);
    cmd.Parameters.AddWithValue("@email", TextBoxEmail.Text);
    cmd.Parameters.AddWithValue("@carno", TextBoxCarno.Text);

    cmd.ExecuteNonQuery();

    con.Close();

    if (IsPostBack)
    {
        Response.Redirect("UpdateSuccess.aspx");
    }

After I click confirm it somehow only update my column genderwhich from male to female, others column of data it won't update.

在我点击确认它以某种方式只更新我的gender从男性到女性的列后，它不会更新其他数据列。

采纳答案by Mats Magnem

It might be because the @username is used both in UPDATE and WHERE. If it changes, the WHERE will be wrong and if it does not change it can be left out of the query.

这可能是因为@username 在 UPDATE 和 WHERE 中都使用了。如果它改变，WHERE 将是错误的，如果它没有改变，它可以被排除在查询之外。

回答by N B

I think in page load you are load again all data and Button1_Click run after page load and you lost are all data you can try your code in page_load method

我认为在页面加载中，您将再次加载所有数据，并且在页面加载后运行 Button1_Click 并且您丢失了所有数据，您可以在 page_load 方法中尝试您的代码

private void Page_Load()
{
    if (IsPostBack)
    {
        SqlConnection con = new SqlConnection("Data Source=USER-PC;Initial Catalog=webservice_database;Integrated Security=True");

    SqlCommand cmd = new SqlCommand("UPDATE registration SET username = @username, password = @password, retypepassword = @retypepassword, gender = @gender, birth = @birth, address = @address, city = @city, country = @country, postcode = @postcode, email = @email, carno = @carno " + "WHERE username = @username", con);

    con.Open();
.
.
.

    }
}

回答by Eric Kassan

I have never seen that type of syntax. Usually if one is using parameters, one is using a stored proc(the best practice). If one is using inline SQL, one builds the SQLstatement as a single text line and executes it. I would recommend recoding for one of those.

我从未见过这种类型的语法。通常，如果使用参数，则使用 a stored proc（最佳实践）。如果使用inline SQL，则将SQL语句构建为单个文本行并执行它。我建议为其中之一重新编码。

If you want to try what you've started, in your SQL, you probably need to declare all the variables first. For example

如果你想尝试你已经开始的东西，在你的SQL, 你可能需要先声明所有的变量。例如

SqlCommand cmd = new SqlCommand("declare @username varchar(100), @password varchar(100), 
@retypepassword varchar(100) @gender varchar(10), @birth date, @address varchar(100), 
@city varchar(100) @country varchar(100), @postcode varchar(10), @email varchar(100), 
@carno varchar(100) UPDATE registration SET username = @username, password = @password, 
retypepassword = @retypepassword, gender = @gender, birth = @birth, address = @address, 
city = @city, country = @country, postcode = @postcode, email = @email, carno = @carno", 
con);

回答by user8324996

sql update

sql更新

SqlCommand cmd = new SqlCommand("update[Testing].[dbo].[student] set name= '" + tb1.Text + "',age='" + tb2.Text + "',mobile='" + tb3.Text+ "' where id = '" + tb4.Text + "'", con);

SqlCommand cmd = new SqlCommand("update[Testing].[dbo].[student] set name='" + tb1.Text + "',age='" + tb2.Text + "',mobile='" + tb3 .Text+ "' where id = '" + tb4.Text + "'", con);

回答by mahmoud saeed

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("Data Source=USER-PC;Initial Catalog=webservice_database;Integrated Security=True");

    SqlCommand cmd = new SqlCommand("SELECT * FROM registration WHERE username = " + TextBoxUsername.Text + "UPDATE registration SET username = @username, password = @password, retypepassword = @retypepassword, gender = @gender, birth = @birth, address = @address, city = @city, country = @country, postcode = @postcode, email = @email, carno = @carno", con);

    con.Open();

    cmd.Parameters.AddWithValue("@username", TextBoxUsername.Text);
    cmd.Parameters.AddWithValue("@password", TextBoxPassword.Text);
    cmd.Parameters.AddWithValue("@retypepassword", TextBoxRPassword.Text);
    cmd.Parameters.AddWithValue("@gender", DropDownListGender.Text);
    cmd.Parameters.AddWithValue("@birth", DropDownListDay.Text);
    cmd.Parameters.AddWithValue("@address", TextBoxAddress.Text);
    cmd.Parameters.AddWithValue("@city", TextBoxCity.Text);
    cmd.Parameters.AddWithValue("@country", DropDownListCountry.Text);
    cmd.Parameters.AddWithValue("@postcode", TextBoxPostcode.Text);
    cmd.Parameters.AddWithValue("@email", TextBoxEmail.Text);
    cmd.Parameters.AddWithValue("@carno", TextBoxCarno.Text);

    cmd.ExecuteNonQuery();

    con.Close();

    if (IsPostBack)
    {
        Response.Redirect("UpdateSuccess.aspx");
    }