ExecuteScalar() sometimes returns empty but not null object. Why?

Question

ExecuteScalar() sometimes returns empty object -not null- although the record is exists. When I analyze this object with quickwatch, I see that object.GetType() is equal to DbNull. I can handle this empty object but I need to know why it is returns empty object sometimes although the record is exists.

string sql = @"SELECT SentDate 
               FROM dbo.EmailOut                                    
               WHERE ID = @ID";
SqlCommand cmd = new SqlCommand(sql, _cnn);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@ID", ID));
object obj = cmd.ExecuteScalar();
if (obj == null)
    return false
sentDate = (DateTime)obj;
cmd.Dispose();

Most of the time my query runs perfectly. Can you please check my code?

Records could be exist but maybe `SentDate` column is `null`? — Soner Gönül, Nov 08 '13 at 08:41
hoping this is only an example, but you should wrap you `SqlCommand` in a `using` statement — Squirrel5853, Nov 08 '13 at 08:43
@SecretSquirrel: In general, yes. In this case, it's not terribly bad, since SqlCommand.Dispose is essentially a no-op. One should not get used to it, though, since, for example, not disposing an OleDbCommand or SqlCeCommand *will* cause trouble. — Heinzi, Nov 08 '13 at 08:47
I have answered the reason under the Heinzi's answer. Please check it out — cihadakt, Nov 08 '13 at 09:23

score 9 · Answer 1 · answered Nov 08 '13 at 08:42

9

A return value of null means that no record was found.

A return value of DBNull means that a record was found, but the value of SentDate in that record is NULL.

answered Nov 08 '13 at 08:42

Heinzi

167,459
57
363
519

but sentdate is not null – cihadakt Nov 08 '13 at 08:43
@cihata87: That's... surprising. Are you sure about that? Can you reproduce the problem? – Heinzi Nov 08 '13 at 08:45
1

@cihata87: Then we need to investigate further. Can you prepare a minimal sample (including the database data) that reliably reproduces the problem? – Heinzi Nov 08 '13 at 08:48
Finally I have found out that record is creating after 1 or 2 seconds later. But when I check sentdate is null because query runs so fast. So I did this System.Threading.Thread.Sleep(2000); I always get the full object. Thank you – cihadakt Nov 08 '13 at 09:22

ExecuteScalar() sometimes returns empty but not null object. Why?

1 Answers1