如何在给定的代码中更正此语句：cmd.ExecuteNonQuery（）; [英] how to correct this statement in the given code below : cmd.ExecuteNonQuery();

问题描述

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class uploadProject : System.Web.UI.Page
{
    connection con = new connection();
    protected void Page_Load(object sender, EventArgs e)
    {
        fillid();
    }
    public void fillid()
    {
        con.open_connection();
        string str = "select * from celeb order by ID";
        SqlCommand cmd = new SqlCommand(str, con.con_pass());
        SqlDataReader dr = cmd.ExecuteReader();
        int i = 0;
        while (dr.Read())
        {
            int a = 0;
            a = Convert.ToInt32(dr["ID"].ToString());
            ViewState["sid"] = a.ToString();
            i = i + 1;
        }
        if (i > 0)
        {
            int a = Convert.ToInt32(ViewState["sid"].ToString());
            a = a + 1;
            TextBox1.Text = a.ToString();
        }
        else
            TextBox1.Text = "1";
        con.close_connection();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            try
            {
                string filename = System.IO.Path.GetFileName(FileUpload1.FileName);
                FileUpload1.SaveAs(Server.MapPath("~/uploadImage/") + filename);
               // Console.Write(filename);
                Image1.ImageUrl = "~/uploadImage/" + filename;
                Image1.Visible = true;
                // TextBox9.Text = Image1.ImageUrl;
                FileUpload1.Visible = false;
                Button2.Visible = false;
            }
            catch (Exception ex)
            {
                //StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
            }
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        con.open_connection();
        string str = "insert into celeb values('" + TextBox1.Text + "','" + TextBox4.Text + "','" + TextBox3.Text + "','" + Image1.ImageUrl + "','" + System.DateTime.Now.ToString() + "')";
        SqlCommand cmd = new SqlCommand(str, con.con_pass());
         cmd.ExecuteNonQuery(); 
        con.close_connection();
    }
}

推荐答案

不是那样的。

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

特别是基于网络的解决方案，任何人都可以从世界的另一端销毁你的数据库...



并且列出要插入值的列是一个非常非常好的主意 - 如果不这样，那么SQL将尝试按照它们当前在数据库中定义的顺序插入它们 - 它不会尝试智能地匹配它们。因此，如果您首先有一个ID列（并且大多数表都这样做），SQL将尝试将第一个值插入其中...

Not like that.
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.
Particularly with a web based solution, where anyone can destroy your database from the other side of the world...

And it's a very, very good idea to list the columns you want to insert values into - if you don't then SQL will try to insert them in the order they are currently defined in the database - it won't try to intelligently match them up. So if you have an ID column first (and most tables do) SQL will try to insert the first value into that...

string str = "INSERT INTO celeb (Column1Name, Column2Name, Column3Name, Coulmn4Name, Column5Name) VALUES (@C1, @C2, @C3, @C4, @C5)";
SqlCommand cmd = new SqlCommand(str, con.con_pass());
cmd.Parameters.AddWithValue("@C1", TextBox1.Text);
cmd.Parameters.AddWithValue("@C2", TextBox4.Text);
...
cmd.ExecuteNonQuery(); 

您可以使用敏感名称代替C1，C2等等，以使您的代码更具可读性。



机会这也将解决你的问题。



BTW：帮自己一个忙，并停止使用Visual Studio默认名称 - 你可能还记得TextBox8是今天的手机号码，但是当你需要修改它是三个星期的时间，那么你呢？使用描述性名称 - 例如tbMobileNo - 您的代码变得更容易阅读，更自我记录，更易于维护 - 并且编码速度更快，因为Intellisense可以通过三次击键来tbMobile，其中TextBox8需要思考大概和8次击键......

You can use "sensible names" instead of "C1", "C2" and so forth to make your code more readable.

Chances are that this will also fix your problem.

BTW: Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it is three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes...

这篇关于如何在给定的代码中更正此语句：cmd.ExecuteNonQuery（）;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！