SQL C＃NULL值允许查询 [英] SQL C# NULL Value Allow Query

问题描述

请参阅以下代码。我的问题是：一旦执行查询，结果将是一个空值或非空值，一旦从查询中识别出一个空值，C＃程序就会发生一个错误，因为对象引用未设置为对象的实例。。 />


因此，我不需要上面提到的错误。必需的是，textbox应该能够显示null并且一旦得到记录就应该显示非null值。

Please refer below code. My problem is: once execute the query, result will be a null value or not null value, once identify a null value from query, C# program occurred an error as "Object reference not set to an instance of an object.".

Therefore, I don’t need above mentioned error. Required is, textbox should be able to display the null and once got a record it should be displayed the not null value.

SqlCommand comando56 = new SqlCommand();
string myConnectionString56 = (@"Persist Security Info=True;Password=123;User ID=user;Initial Catalog=TEST;Data Source=192.168.99.99");
SqlConnection conn56 = new SqlConnection(myConnectionString56);
comando56.Connection = conn56;
comando56.CommandText = (@"SELECT ITEMS from TEST where Date='" + textBoxDate.Text + "' and ID='" + textBoxID.Text + "'");
conn56.Open();
textBoxResult.Text = comando56.ExecuteScalar().ToString();
conn56.Close();

推荐答案

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



其次，如果没有选择记录，ExecuteScalar总是返回 null - 请参阅msdn ： http://msdn.microsoft.com/en-us/library /system.data.sqlclient.sqlcommand.executescalar.aspx [ ^ ]



如果你想知道的是多少项符合你的标准，为什么而不是返回计数？

First off, 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.

Secondly, if no records are selected, the ExecuteScalar always returns null - see msdn: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx[^]

IF all you want to know is how many items would match your criteria, why not return the count instead?

SELECT COUNT(items) FROM test WHERE...

应该这样做。





由于意外删除造成的错误：rst为First - OriginalGriff [/ edit]

should do it.


[edit]Typo caused by accidental delete: "rst" for "First" - OriginalGriff[/edit]

1）将查询结果合并为一变种在分配检查之前它是否为空？



2）总是使用Convert.ToString而不是使用ToString（）。因为Convert.ToString处理如果有空值，则返回空值和ToString（）抛出异常。

1) Take query result in one ''var'' and before assigning check is it null or not ?

2) Always use "Convert.ToString" than using "ToString()" .Because "Convert.ToString" Handles the null values and "ToString()" throws exception if there is null value.

var varTemp =comando56.ExecuteScalar();
if (varTemp != null)                                                                          
{
    textBoxResult.Text=Convert.ToString(varTemp );
}

更改下面的命令文本，

Hi,

Change your command text like below,

comando56.CommandText = (@"SELECT ISNULL(ITEMS,'') from TEST where Date='" + textBoxDate.Text + "' and ID='" + textBoxID.Text + "'");

这篇关于SQL C＃NULL值允许查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！