If database has no record, return a declared int

Question

I'm facing a problem. In my case, I would like to select max and + 1 in order to create a new receipt number, if it is a new receipt, it will create a new number as the code below shown. However, I'm getting the error

Object cannot be cast from DBNull to other type

here is my code.

this system is running on PDA, so I'm using this web service to call database

public string callReceipt(string terminal, ref string msg)
    {
        try
        {
            string sql = "Select MAX(ReceiptNo) from ReceiptStore where terminalno ='" + terminal + "'";
            Logging.Log.writeLog(sql);
            msg = "Success";
            int receiptno = Convert.ToInt32(Data.DAL.ExecuteScalar(sql));
            if (!(sql is DBNull))
            {
                return Convert.ToString(receiptno + 1);
            }
            else
            {
                int receipt = 100000;
                return receipt.ToString();
            }
        }
        catch (Exception e)
        {
            msg = "Error : " + e.Message.ToString();
            Logging.Log.writeLog(msg);
            return msg;
        }
        finally
        {

        }
    }

****** UPDATE ******

Thanks for all the answers, SimpleVar has provided the correct answer and it solved my problem. thanks again!

Answer 1

Try this:

object o = Data.DAL.ExecuteScalar(sql);
if (o is int)
{
    return ((int)o + 1).ToString();
}
else
{
    int receipt = 100000;
    return receipt.ToString();
}

Besides that, you should look into stored procedures and prepared queries.

And for incrementing keys, you shouldn't use either, but use the appropriate database functionality that is auto-increment id columns.