更新设置空值

本文介绍了更新设置空值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在我的网站上，有些用户会提供虚假地址。我想将这些地址设置为空值以保留其余数据。我的改变代码很简单;当我确定地址是假的时，我使用以下代码 SqlCommand clear1 = new SqlCommand（UPDATE PersonalData SET Address ='+ DBNull.Value +'WHERE UserName ='+ username +'，连接）; 但是这个NonQuery的执行不会返回null值，但在地址字段中给出一个空白。 我需要将它设置为空值，因为我的网格视图设置为忽略地址的空值，我不希望这种数据是显示在我的网站上。 显然我在更新命令中设置错误以设置空值。 请帮助我将所选字段更新为空值。我可以确保在网格视图中不显示这些选定地址的任何其他选项同样适用。 非常感谢所有人的帮助。

解决方案

请不要这样做！ 不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。这不仅有助于保存您的数据库，还可以解决您的问题... 使用（ SqlConnection con = new SqlConnection（strConnect）） { con.Open（）; 使用（SqlCommand cmd = new SqlCommand（ UPDATE myTable SET PersonalData = @ PD WHERE UserName = @ UN，con）） { cmd.Parameters.AddWithValue （ @ UN，用户名）; cmd.Parameters.AddWithValue（ @ PD，DBNull.Value）; cmd.ExecuteNonQuery（）; } }

On my site some users give their fake address. I want to set those addresses to null value keeping rest of data. my code for this change is simple; when I identify that an address is fake, I use the following code SqlCommand clear1 = new SqlCommand("UPDATE PersonalData SET Address='" + DBNull.Value + "' WHERE UserName='"+ username + "'",connection ); but execution of this NonQuery does not return null value but gives a blank in the address field. It is necessary for me to set it to null value because my grid view is set to ignore null values of address and I do not want this kind of data to be displayed on my site. Apparently I am making mistake in update command to set null value. Kindly help me to update the selected fields to null value. Any other option by which I can make sure that these selected addresses are not displayed in the grid view, will be equally good. Many thanks to all for their kind help.

解决方案

Please, don't do 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. This will not only help save your DB, it will also cure your problem...

using (SqlConnection con = new SqlConnection(strConnect)) { con.Open(); using (SqlCommand cmd = new SqlCommand("UPDATE myTable SET PersonalData=@PD WHERE UserName=@UN", con)) { cmd.Parameters.AddWithValue("@UN", username); cmd.Parameters.AddWithValue("@PD", DBNull.Value); cmd.ExecuteNonQuery(); } }