ExecuteScalar() always return NULL

Question

I am trying to return an integer from the database using ExecuteScalar(). However, when I run the query on the database itself, I get the correct answer, and c# gives me a 0 (Null) all the time. I know it returns a null because before i added id = Convert.ToInt32(command.ExecuteScalar()); it would give me an error telling me to make sure NULL is handled. I am expecting it to return a 3 btw.

 private int getFamilyId()
 {
    int id = 0;
    using (SqlConnection connection = new SqlConnection(Globaldata.ConnectionString))
    {
        using (SqlCommand command = new SqlCommand())
        {
            string sqlString = @"SELECT [Id] FROM [dbo].[FamilyDetails];";
            command.Connection = connection;
            command.CommandText = sqlString;
            try
            {
                connection.Open();
                id = Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "Error", MessageBoxButtons.OK);
            }
            finally
            {
                connection.Close();
            }
            return id;
        }
    }
}

Answer 1

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.

Answer 2

--UPDATE--

It works now, My connection string had one character missing. I think it happened when I took out the connection Timeout part of the connection string.

Thank you for your suggestions!!!