SQL C# NULL Value Allow Query

Question

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 20-Dec-12 23:24pm

abdulsafran

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2012-12-20T23:33:00

Solution 1

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]

Posted 20-Dec-12 23:33pm

OriginalGriff

Updated 20-Dec-12 23:34pm

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?

OriginalGriff 21-Dec-12 10:57am

Knock on my front door!

abdulsafran 21-Dec-12 10:59am

Ah Ah Great......., OK then I'll contact you through the code project.

OriginalGriff 21-Dec-12 11:01am

Probably best! :laugh:

abdulsafran 21-Dec-12 11:05am

Yes Sir

it.sudhiryadav · Answer 2 · 2012-12-21T02:01:00

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#

Sampath Kumar Sathiya · Answer 3 · 2012-12-20T23:36:00

Solution 2

Hi,

Change your command text like below,

C#

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

Posted 20-Dec-12 23:36pm

Sampath Kumar Sathiya

Updated 21-Dec-12 1:25am

v2

Aarti Meswania · Answer 4 · 2012-12-21T01:35:00

Solution 5

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!
:)

Posted 21-Dec-12 1:35am

Aarti Meswania

Jashobanta · Answer 5 · 2012-12-20T23:47:00

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.