Click here to Skip to main content
15,938,446 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
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.

XML
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();
Posted

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?
SQL
SELECT COUNT(items) FROM test WHERE...
should do it.


[edit]Typo caused by accidental delete: "rst" for "First" - OriginalGriff[/edit]
 
Share this answer
 
v2
Comments
abdulsafran 21-Dec-12 5:38am    
Hi OriginalGriff, thanks for your reply, but, I excatly need the value not count. Could you please re visit and advise me.
OriginalGriff 21-Dec-12 5:48am    
If you need the actual value, then return it into an object and check it:
object o = comando56.ExecuteScalar();
textBoxResult.Text = o == null ? "" : o.ToString();
abdulsafran 21-Dec-12 10:33am    
This is worked. Thanks a lot Griff......
OriginalGriff 21-Dec-12 10:50am    
You're welcome!
abdulsafran 21-Dec-12 10:56am    
Griff, how can I contact you directly if there any urgent problem?
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.

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

C#

 
Share this answer
 
Hi,

Change your command text like below,

C#
comando56.CommandText = (@"SELECT ISNULL(ITEMS,'') from TEST where Date='" + textBoxDate.Text + "' and ID='" + textBoxID.Text + "'");
 
Share this answer
 
v2
instead of this,
C#
textBoxResult.Text = comando56.ExecuteScalar().ToString();

write this...
C#
string s = comando56.ExecuteScalar();
textBoxResult.Text = iif(s=DbNull.value,"",s);

or
C#
textBoxResult.Text = comando56.ExecuteScalar() + "" ;

Happy Coding!
:)
 
Share this answer
 
why don't you use a Stored Proc to execute the same. In that proc you can use Return statements and fetch those in your program to set the values and it is safer and easy as well.
The program would be :-

C#
If exists( select the criteria)
BEGIN
(write the program logic here and then return a suitable return code)
RETURN 1;
END
ELSE
return 0;


C#
In your code, you can use 
SqlCommand command= new SqlCommand("procname",Connection);
Sqlparameter parret=new sqlparameter();
parret.Direction= parameterDirection.ReturnVal;
Command.CommandType=CommandType.StoredProcedure();
.
.
.
cmd.ExecuteNonQuery();
fetch the return value and then proceed.

.
.
.
I have not used any tool to type the code, so the codes might vary, but the logic remains the same.
Hope this helps. Or else, the other options would work good but are not safe if proper validations are not made on front end.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900