How can i have a NullReferenceException but my code still work exactly how i want? how can i correct it?

Question

I'm getting a NullReferenceException, but yet my code is still doing exactly what I want. How is that so? How can I get rid of it? I cant figure out why its throwing this.

On the line

            int result = (int)sqlCmd.ExecuteScalar();

In the method:

    public override int AddCustomer(Customer customer)
    {

        try
        {
            SqlCommand sqlCmd = new SqlCommand("INSERT INTO [AXMAP].[Customers] ([LegalEntity], [OldSourceEnvironment], [OldCompanyCode], [OldAcctNum], [AccountNum], [Name], [BusinessUnit], [DefaultDimensionStr]) VALUES (@LegalEntity, @OldSourceEnvironment, @OldCompanyCode, @OldAcctNum, @AccountNum, @Name, @BusinessUnit, @DefaultDimensionStr)");
            sqlCmd.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@LegalEntity", customer.LegalEntity), new SqlParameter("@OldSourceEnvironment", customer.OldSourceEnvironment),
            new SqlParameter("@OldCompanyCode", customer.OldCompanyCode), new SqlParameter("@OldAcctNum", customer.OldAccountNumber), new SqlParameter("@AccountNum", customer.NewAccountNumber),
            new SqlParameter("@Name", customer.BusinessName), new SqlParameter("@BusinessUnit", customer.BusinessUnit), new SqlParameter("DefaultDimensionStr", customer.DefaultDimensionString) });

            sqlCmd.Connection = OpenConnection();
            int result = (int)sqlCmd.ExecuteScalar();
            sqlCmd.Connection.Close();
            return result;
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

I call AddCustomer in this method:

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        try
        {
            int result = new Customer() { LegalEntity = ddlLegalEntity.Text.Trim(), OldSourceEnvironment = ddlOldSourceEnvironment.Text.Trim(),
                OldCompanyCode = ddlOldCompanyCode.Text.Trim(), OldAccountNumber = txtOldAccountNumber.Text.Trim(), NewAccountNumber = txtNewAccountNumber.Text.Trim(),
                BusinessName = txtBusinessName.Text.Trim(), BusinessUnit = txtBusinessUnit.Text.Trim(), DefaultDimensionString = txtDefaultDimensionString.Text.Trim()}.AddCustomer();

            if (result > 0)
            {
                lvCustomers.EditIndex = -1;
                lblCurrent.Text = "Added Successfully!";
                ddlLegalEntity.Text = string.Empty;
                ddlOldSourceEnvironment.Text = string.Empty;
                ddlOldCompanyCode.Text = string.Empty;
                txtOldAccountNumber.Text = string.Empty;
                txtNewAccountNumber.Text = string.Empty;
                txtBusinessName.Text = string.Empty;
                txtBusinessUnit.Text = string.Empty;
                txtDefaultDimensionString.Text = string.Empty;
            }
            else
            {
                lvCustomers.EditIndex = -1;
                lblCurrent.Text = "Sorry, Add failed.";
            }
        }
        catch (Exception ex)
        {
            lblCurrent.Text = ex.Message;
        }
    }

At the end of the line:

            int result = new Customer() { LegalEntity = ddlLegalEntity.Text.Trim(), OldSourceEnvironment = ddlOldSourceEnvironment.Text.Trim(),
                OldCompanyCode = ddlOldCompanyCode.Text.Trim(), OldAccountNumber = txtOldAccountNumber.Text.Trim(), NewAccountNumber = txtNewAccountNumber.Text.Trim(),
                BusinessName = txtBusinessName.Text.Trim(), BusinessUnit = txtBusinessUnit.Text.Trim(), DefaultDimensionString = txtDefaultDimensionString.Text.Trim()}.AddCustomer();

The customer class looks like this:

public class Customer
{

    public string LegalEntity { get; set; }
    public string OldSourceEnvironment { get; set; }
    public string OldCompanyCode { get; set; }
    public string OldAccountNumber { get; set; }
    public string NewAccountNumber { get; set; }
    public string BusinessName { get; set; }
    public string BusinessUnit { get; set; }
    public string DefaultDimensionString { get; set; }

    public int AddCustomer()
    {
        try
        {
            return DataAccessLayer.GetDataAccessLayer().AddCustomer(this);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

}

Could it possibly be in the customer class?

Answer 1

It's probably because (int)sqlCmd.ExecuteScalar() returns null and you're trying to put it into an int. The value is returned AFTER the command has been executed so technically the code does what you wanted, but fails afterwards.

Answer 2

according to msdn,when you call ExecuteScalar it "returns the first column of the first row in the result set returned by the query." so you need to return a value inside your Insert Into Command. while it did not return any thing trying to cast Null to int will throw NullException. in order to return the ID of inserted row you can use something like this:

INSERT INTO table (name1,name2,name3,...)
OUTPUT Inserted.ID
VALUES(val1,val2,val3,...);

hope this helps

Answer 3

You probably need to call 'ExecuteNonQuery' and not 'ExecuteScalar'.

        int result = sqlCmd.ExecuteNonQuery();

ExecuteScalar, according to MSDN:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx

ExecuteNonQuery, according to MSDN:

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery%28v=vs.110%29.aspx