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?

Thanks for all the details. What does your stacktrace tell you when you get the exception? It should funnel down from the statement at which the exception is caught to the problem-causing component. Also, when you say it's working as expected, do you mean that the record gets inserted with all the values you entered in `btnAdd_Click()`? — Ash, May 30 '16 at 04:13
There is already a good answer posted here- http://stackoverflow.com/questions/18373461/execute-insert-command-and-return-inserted-id-in-sql — Souvik Ghosh, May 30 '16 at 04:42

score 1 · Answer 1 · answered May 30 '16 at 04:14

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.

answered May 30 '16 at 04:14

Shay Yizhak

91
4

So what would need to be changed in order to avoid this? – Psymbionic May 30 '16 at 04:23

score 1 · Answer 2 · answered May 30 '16 at 04:26

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

score 1 · Answer 3 · answered May 30 '16 at 04:32

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

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

3 Answers3