Why don't I have a definition for 'ExecuteScalar'?

Question

I have searched for my answer only to find that there was a typo in someone's code.

I have not found why this code does not have a definition for ExecuteScalar(). I might actually need to capture Customer_Id when I actually add the row to the SQL database because it is auto increment.

Here is my code that had the problem:

if (customer_IDTextBox == null)
{
    sqlConnection.Open();
    string SQL = "SELECT MAX(Customer_ID) FROM Customer";
    int maxId = Convert.ToInt32(SQL.ExecuteScalar());
    sqlConnection.Close();
}

Answer 1

You need to create an instance of SqlCommand, then assign your connection and query to it.

Try this code instead. (A couple bits of advice. I've surrounded your connection and command in using statements so there's no need to close anything... they'll be disposed of. Also, always try to create connections and commands as close as possible to the point where you're going to need them.)

int maxId = -1;

if (customer_IDTextBox == null)
{
    using (var sqlConnection = new SqlConnection(/* your connection string */))
    {
        sqlConnection.Open();
        string query = "SELECT MAX(Customer_ID) FROM Customer";

        using (var sqlCommand = new SqlCommand(query, sqlConnection))
        {
            maxId = Convert.ToInt32(sqlCommand.ExecuteScalar());
        }
    }
}