我在更新表时遇到了麻烦，但它对插入和删除工作得很好plzz帮助我 [英] i am having trouble while updating table but it worked well for insertion and deletion plzz help me

问题描述

protected void Button1_Click(object sender, EventArgs e)
    {
        string constr = "server=.;database=swachdb;user id=sa;pwd=sa";
        SqlConnection con = new SqlConnection(constr);
        string cmdStr = "update tab set name='" + txt1.Text + "'where id='" + s + "'";
        

        SqlCommand cmd = new SqlCommand(cmdStr, con);

        
        cmd.Parameters.AddWithValue("@name", txt1.Text);
        cmd.Parameters.AddWithValue("@id",s);
       
        cmd.Parameters.AddWithValue("@address", txt2.Text);
        cmd.Parameters.AddWithValue("@city", drpdwn.SelectedItem.ToString());
        cmd.Parameters.AddWithValue("@email", txt4.Text);
        cmd.Parameters.AddWithValue("@contact", txt5.Text);

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();

        }
        catch (SqlException ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            if (con.State != System.Data.ConnectionState.Closed)
            {
                con.Close();
                txt1.Text = "";
                lbl.Text = "";
                
                txt2.Text = "";
                drpdwn.SelectedValue = "Select City";
                txt4.Text = "";
                txt5.Text = "";
                Response.Write("Data successfully updated");

            }
        }
       
    }

推荐答案

请，做你自己一个忙。

不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。



并且不要将sa用户用于您的SQL - 这是一个可怕的安全风险，尤其是当您将密码保留为默认值时。 ..



如果您实际上没有使用它们，为什么要创建命令并添加参数？即使您确实参考了查询中的参数 - 您没有参考 - 这仍然会给您留下四个参数，甚至没有提到......



I认为你需要回到一个阶段，看看SQL应该如何工作，并重新思考你的方法：看起来你的插入和删除查询更多的是运气而不是判断，这总是很危险...



如果我是你的用户，我收到一条错误消息，然后我的字段被清空，然后告诉数据成功更新，当它不是时，我'我可能想打你。相当难。



最后 - 为你的文本框使用合理的名字！ txt1，txt2，...其中哪一个拥有什么？下周你需要知道吗？下个月？不，你不会......

Please, do yourself a favour.
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

And don't use the "sa" user for your SQL - it's a horrible security risk, particularly when you leave the password as default...

Why the heck are you creating a command and adding parameters if you don't actually use them? Even if you did reference the parameters in your query - which you don't - that would still leave you four parameters you don't even mention...

I think you need to go back a stage, look at how SQL is supposed to work, and re-think your approach: it would look like your insert and delete queries worked more by luck than by judgement, and that's always dangerous...

And if I was your user, and I got an error message followed by my fields being emptied and then told "Data successfully updated" when it wasn't, I'd probably want to hit you. Quite hard.

And finally - use sensible names for your textboxes! txt1, txt2, ... which one of those holds what? Will you know next week when you have to maintain this? Next month? no, you won't...

使用如下参数

use parameters as below

string cmdStr = "update tab set name=@name where id=@id";
using(SqlConnection con = new SqlConnection(constr))
using(SqlCommand cmd = new SqlCommand(cmdStr, con))
{
   cmd.Parameters.AddWithValue("@name", txt1.Text);
   cmd.Parameters.AddWithValue("@id",s);
   con.Open();
   cmd.ExecuteNonQuery();
}

这篇关于我在更新表时遇到了麻烦，但它对插入和删除工作得很好plzz帮助我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！