删除SQL语句无效 [英] Delete SQL Statement not working

问题描述

我有一个网页表单，上面有一个已保存的按钮和几个文本框。用户可以将数据输入文本框并将其临时保存到数据库中。用户可以返回到Web表单，并将保存的数据填充回文本框中。用户想要更改已填充文本框中的数据，但不能。数据会更改回数据库中填充的内容。我有一个SQL删除语句，但它似乎不起作用。我做错了什么？





这是带有删除声明的填充代码：

I have a web form that has a saved button and a couple of textboxes on it. The user can enter data into the textboxes and save it to the database temporary. The user can come back to the web form and the saved data is populated back into the textboxes. The user wants to change the data in the populated textbox but can't. The data changes back to what was populated from the database. I have a SQL Delete statement in place but it doesn't seem to work. What did I do wrong?


Here is the populated code with Delete Statement:

if (TextBoxINST_ID.Text.Trim().Length > 0)
            {
                SqlConnection con44 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
                con44.Open();

                SqlCommand scmd44 = new SqlCommand("Select FT_UNDERGR, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC from TableSave where INST_ID = '" + TextBoxINST_ID.Text + "'", con44);
                SqlDataReader dr44 = scmd44.ExecuteReader();
                if (dr44.Read())
                {
                    TextBoxFTUG.Text = dr44["FT_UNDERGR"].ToString();
                    TextBoxFTG.Text = dr44["FT_GRAD"].ToString();
                    TextBoxTHUGDR.Text = dr44["FTE_UNDERG"].ToString();
                    TextBoxTHGDR.Text = dr44["FTE_GRAD"].ToString();
                    TextBoxNCCDR.Text = dr44["NON_CREDIT"].ToString();
                    TextBoxTCNC.Text = dr44["TOTAL_FTE"].ToString();
                    TextBoxTNFUG.Text = dr44["FCFTUHC"].ToString();
                    TextBoxTNFG.Text = dr44["FCFTPBHC"].ToString();
                    TextBoxTNCPUG.Text = dr44["FCPTUHC"].ToString();
                    TextBoxTNCPG.Text = dr44["FCPTPBHC"].ToString();
                    TextBoxTNNCC.Text = dr44["NCHC"].ToString();

                    TextBoxTNFUG.Text = string.Format("{0:0,0}", double.Parse(TextBoxTNFUG.Text));
                    TextBoxTNFG.Text = string.Format("{0:0,0}", double.Parse(TextBoxTNFG.Text));
                    TextBoxTNCPUG.Text = string.Format("{0:0,0}", double.Parse(TextBoxTNCPUG.Text));
                    TextBoxTNCPG.Text = string.Format("{0:0,0}", double.Parse(TextBoxTNCPG.Text));
                    TextBoxTNNCC.Text = string.Format("{0:0,0}", double.Parse(TextBoxTNNCC.Text));
                    TextBoxFTUG.Text = string.Format("{0:0,0}", double.Parse(TextBoxFTUG.Text));
                    TextBoxFTG.Text = string.Format("{0:0,0}", double.Parse(TextBoxFTG.Text));
                    TextBoxTHUGDR.Text = string.Format("{0:0,0}", double.Parse(TextBoxTHUGDR.Text));
                    TextBoxTHGDR.Text = string.Format("{0:0,0}", double.Parse(TextBoxTHGDR.Text));
                    TextBoxNCCDR.Text = string.Format("{0:0,0}", double.Parse(TextBoxNCCDR.Text));
                    TextBoxTCNC.Text = string.Format("{0:0,0}", double.Parse(TextBoxTCNC.Text));

                    ButtonSubmit.Visible = true;
                    ButtonPrint.Visible = false;
                    ButtonSave.Visible = false;
                }
                con44.Close();
                dr44.Close();

                if (TextBoxINST_ID.Text.Trim().Length > 0)
                {
                    SqlConnection con45 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
                    con45.Open();
                    {
                        SqlCommand scmd45 = new SqlCommand("Delete FT_UNDERGR, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC from TableSave where INST_ID = '" + TextBoxINST_ID.Text + "'", con45);
                        ButtonPrint.Visible = false;
                    }
                    con45.Close();
                }   
            }
        }
    }

推荐答案

正确的删除 [ ^ ]语句为：

The proper DELETE[^] statement is:

DELETE
FROM TableName
WHERE NumericField = 1

而不是

instead of

DELETE
FROM TableName
WHERE NumericField = '1'

我需要警告你：不要在后面的代码中使用sql查询，因为 SQLInjection [ ^ ]。请改用存储过程。

演练：显示使用GridView Web服务器控件中的存储过程的数据 [ ^ ]

如何调用使用Visual Basic .NET在ASP.NET中的SQL Server存储过程 [ ^ ]

如何：执行存储过程返回单个值 [ ^ ]



示例程序：

I need to warn you: do not use sql queries in code behind, because of SQLInjection[^]. Use stored procedures instead.
Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server Control[^]
How to call SQL Server stored procedures in ASP.NET by using Visual Basic .NET [^]
How to: Execute a Stored Procedure that Returns a Single Value[^]

Sample procedure:

CREATE PROCEDURE DeleteFromDatabase
    @instid INT
AS
BEGIN
    DELETE
    FROM TableName
    WHERE INST_ID = @instid

    RETURN @@ROWCOUNT

END

有关SQLInjection的更多信息，请参阅：

如何：防止ASP.NET中的SQL注入 [ ^ ]

SQL注入以及如何避免它[ ^ ]

停止SQL注入攻击之前他们阻止你 [ ^ ]

For further information about SQLInjection, please see:
How To: Protect From SQL Injection in ASP.NET[^]
SQL Injection and how to avoid it[^]
Stop SQL Injection Attacks Before They Stop You[^]

在设计方面似乎有点亮 - 正如我所看到的，你有两个选择



a）删除数据一旦它恢复回文本框就从桌子上支付



或



b）保存数据到临时表 - 添加一个按钮，基本上是'保存最终'，将数据保存到永久表中

seems a bit light on the design side - as I see it, you have two options

a) delete the data from the table once it has been 'restored' back to the textboxes

or

b) save the data to a temporary table - add a button, basically 'save final' that saves the data into the permanent table

这篇关于删除SQL语句无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！