Handling Executescalar() When No Results Are Returned

handing ExecuteScalar returning null when no results are found

Try this:

result = (sqlcom.ExecuteScalar() ?? "").ToString();

If it returns null, the result will be an empty string. You can handle that case by an if-statement and notify some message to the user, such as like this:

object r = sqlcom.ExecuteScalar();  
if(r != null) result = r.ToString();
else {
  //code to handle the null case here...
}

ExecuteScalar() always return NULL

When you do this:

string sqlString = @"SELECT [Id] FROM [dbo].[FamilyDetails];";

You don't want to do this:

id = Convert.ToInt32(command.ExecuteScalar());

Three things could go wrong here.

Problem #1:
If there are no rows in the table, command.ExecuteScalar() wil return Null.
Problem #2:
If there are any rows in the table, command.ExecuteScalar() wil return the value of the first rows it happens to encounter because the SELECT statement is not restricted to 1 value.
Problem #3:
If the Id column is not declared as NOT NULL command.ExecuteScalar() could return a DbNull, which which makes no sense when converted to an Integer.

Try and see what happens when there are 0 or 1 or 2 records in the table.

Why is ExecuteScalar returning NullReferenceException error

ExecuteScalar returns null if your WHERE condition doesn't produce any result.

If you apply any kind of conversion when ExecuteScalar returns null you are in trouble.

Said that, I really suggest some changes to your query

public int AddDataScalar(string strU)
{
    string strQueryExistence = @"IF EXISTS(SELECT 1 FROM [OB].[h].[OP_PEONS] 
                                 WHERE Executive= @stru) SELECT 1 ELSE SELECT 0";
    int inNum = 0;
    using (SqlConnection con = new SqlConnection(strConn))
    using ( SqlCommand cmd = new SqlCommand(strQueryExistence, con))
    {
        con.Open();
        cmd.Parameters.AddWithValue("@stru", strU);
        inNum = Convert.ToInt32(cmd.ExecuteScalar());
    }
    return inNum;
}

The first thing is the IF EXISTS t-sql functions that is the fastest way to discover if a specific record exists or not in the table. The second point is the usage of a parameterized query to avoid parsing problems and Sql Injection scenarios.

The IF EXISTS statement allows to be sure that the ExecuteScalar doesn't return null because in that case the ELSE part will return zero

Return value using String result=Command.ExecuteScalar() error occurs when result returns null

There is no need to keep calling .ToString() as getValue is already a string.

Aside that, this line could possibly be your problem:

 string getValue = cmd.ExecuteScalar().ToString();

If there are no rows .ExecuteScalar will return null so you need to do some checking.

For instance:

var firstColumn = cmd.ExecuteScalar();

if (firstColumn != null) {
    result = firstColumn.ToString();
}

ExecuteScalar() return error Specified cast is not valid. when there is no record

You don't have to call the ExecuteScalar twice.

var value = selectCommand.ExecuteScalar();
if(value != DBNull.Value)
{
    return (decimal)value;
}

Update

In broad strokes, the DBNull class represents a non existing value. It's different from the null, which means the absence of a reference to an object. So When the result of an sql query is NULL, the value that is returned by ADO.NET (it's the technology you used to access the database) is DBNull.

For further info, please have a look here.

ExecuteScalar returns null or DBNull (development or production server)

Clearly in production you have either a NULL returned from the command execution or something different in the connectionstring or whatever; as a general rule you should always test for DBNull before casting/converting directly to another type the result of ExecuteScalar.

Check Rein's answer here (and vote him up) for his nice suggested solution:

Unable to cast object of type 'System.DBNull' to type 'System.String`

DBNull check for ExecuteScalar

You probably want to change your if-statement to

if (obj != null && DBNull.Value != obj) { 
    ... 
}

Right now you're trying to convert if obj == DBNull.Value.

Execute Scalar to trap error in case of no records returned

I try to avoid comparing a string to Nothing, even though it does work in VB.

The Visual Basic .NET runtime evaluates Nothing as an empty string; that is, "". The .NET Framework, however, does not, and will throw an exception whenever an attempt is made to perform a string operation on Nothing.

Plus pseudocoder's answer wont work as currently shown (oJobname is never set to anything)

Dim lJobName as String = String.Empty
Dim oJobName as object = SqlCommand.ExecuteScalar()

If oJobName Is Nothing Then
    'Do something with the error condition
Else
    lJobName = oJobName.ToString
End If