Executescalar pblm for null values

[Manikandan]

Hi,
I have a table with following data
Tablename:details

No(varchar) Name(varchar) Updated(Datetime)
1 mm 10/10/2006
2 nn 02/12/2005
3 kk NULL

I'm using executescalar to get the updated values from the table
Sql query is "select updated from details where no='1' and name ='kk'
When execute the above query it is throwing error as "object reference
not set to an instance of an object "

Code as below

string date;
string Query="select updated from details where no='"+ no+"' and
name='"+ name+"'";
SqlCommand Command=new SqlCommand(Query,connection);
date=Command.ExecuteScalar().ToString();
Is it possible to cast a date value to string in execute scalar
method?.
Kindly help me to solve the issue


Manikandan

 

[Nicholas Paldino [.NET/C# MVP]]

Manikandan,

The problem here is that calling ExecuteScalar is returning null (an
object reference of null, not the db value of null) because your query
returns no rows. Because of this, you are calling ToString on your null
reference, which gives you the NullReferenceException.

You need to do this:

// Get the result.
object date = Command.ExecuteScalar();

// If there is a value, continue.
if (date != null)
{
// Work with date here.
}

Also, don't create the query string the way you are, you are opening
yourself up to a SQL injection attack. Rather, use the Parameters
collection on the command to have a parameterized query.

Hope this helps.

 

[Bob Grommes]

Aside from Nicholas' recommendations, if field "No" is a number, then it
generally should be an int or bigint, not a varchar -- usually resulting
in better performance and less consumption of disk space. Not to
mention avoiding collation issues such as "10" coming before "2". At
the very least, if you must use varchar to store numeric data, left-pad
numbers in a varchar field with blanks or zeroes so they will order
properly.

--Bob