Check if data reader has rows?

Question

I've found lot's of answers, but none seem to fix my issue.

The user clicks the "New" button to begin adding a new customer record. A stored procedure returns the maximum value of CustID, this is then incremented and assigned to the txtCustID text box. As long as there is a record in the Customer database, this works perfect.

But to create the very first record, this obviously doesn't work as you can't increment nothing.

What I need to do is see if the data reader has anything, if it doesn't I need to set txtCustID to "1", otherwise run through the existing code as normal.

Here's my latest try:

protected void btnNew_Click(object sender, EventArgs e)
{
    Clear();
    SqlCommand command = conn.CreateCommand();
    SqlDataReader reader;
    try
    {
        command.CommandText = "GetMax";
        command.CommandType = CommandType.StoredProcedure;
        conn.Open();
        reader = command.ExecuteReader();
        if (reader.HasRows)
        {
            while (reader.HasRows)
            {
                while (reader.Read())
                {
                    int CustMax = reader.GetInt32(0);
                    CustMax++;
                    txtCustID.Text = CustMax.ToString();
                }
                reader.NextResult();
            }
        }
        else
        {
            txtCustID.Text = "1";
        }
        reader.Dispose();
    }
    catch (SqlException)
    {
        lblMessage.Text = "Cannot connect to database";
    }
    catch (Exception ex)
    {
        lblMessage.Text = ex.Message;
    }
    finally
    {
        command.Dispose();
        conn.Dispose();
    }
}

Here's my stored procedure:

USE [30004243]
GO
/****** Object:  StoredProcedure [dbo].[GetMax]    Script Date: 26/11/2014 2:29:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMax]
AS
SELECT MAX(CustID)
FROM Customer

And here is the error that get's set to my lblMessage label: "Data is Null. This method or property cannot be called on Null values."

While the user could simply see that message and enter "1" manually into txtCustID (Which works fine), for usability it should set it to 1.

All help is greatly appreciated, also I'll add after being asked in a previous question, I am using .net 4.5.

Answer 1

Following comments/answers here and found elsewhere, I replaced reader with execute scalar. Here's what works.

        protected void btnNew_Click(object sender, EventArgs e)
    {
        Clear();
        SqlCommand command = conn.CreateCommand();
        try
        {
            command.CommandText = "GetMax";
            command.CommandType = CommandType.StoredProcedure;
            conn.Open();

            object cMax = command.ExecuteScalar();
            if (cMax != DBNull.Value)
            {
                int CustMax = (int)cMax;
                CustMax++;
                txtCustID.Text = CustMax.ToString();
            }
            else
            {
                txtCustID.Text = "1";
            }

        }
        catch (SqlException)
        {
            lblMessage.Text = "Cannot connect to database";
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
        finally
        {
            command.Dispose();
            conn.Dispose();
        }
    }

Answer 2

I would normally check if reader is not null.

example:

if(reader != DNNull.value){


}else{
    txtCustID.Text = "1";
}

Answer 3

This is not the correct way of adding new record. But, answer for your question is this:

if (reader.HasRows == false)
{
    txtCustID.Text = "1";
}

EDIT: you can do it like this, which is really simple:

        while (dr.Read())
        {
            if (dr.HasRows)
            {
                //Do things, insert, update
            }
            else
            {

            }
        }