NullReferenceException when Adding a SqlParameter to a SqlParameterCollection

Question

I have been wracking my brain for hours trying to solve this so hopefully someone here can help me work this out.

I am inserting into a database in a c# application. Being a good coder, I am parameterizing my query.

The relevant code is:

int contactId = -1;

oString = "INSERT into {0}.Creditors (AccountID, DissectionId, Reference, FileAs, ABN, Created, Modified, GUID)"
    + " output INSERTED.ID"
    + " values (1, @dissectionId, ";
if(disbursement.trade_supplier.trust_id.Length > 0)
{
    oString += "@reference, ";
}
else
{
    oString += "null, ";
}
if(disbursement.trade_supplier.trading_name.Length > 0)
{
    oString += "@name, ";
}
else
{
    oString += "null, ";
}
if(disbursement.trade_supplier.business_number.Length > 0)
{
    oString += "@abn, ";
}
else
{
    oString += "null, ";
}
oString += " CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, @guid)";
oString = string.Format(oString, "[" + textBox1.Text + "].[dbo]");
using (SqlCommand insertCreditor = new SqlCommand(oString))
{
    insertCreditor.Connection = myConnection;
    insertCreditor.Parameters.Add("@dissectionId", SqlDbType.Int).Value = Convert.ToInt32(defaultDissectionID);
    if (disbursement.trade_supplier.trust_id.Length > 0)
    {
        insertCreditor.Parameters.Add("@reference", SqlDbType.NVarChar, 8).Value = disbursement.trade_supplier.trust_id;
    }
    if (disbursement.trade_supplier.trading_name.Length > 0)
    {
        insertCreditor.Parameters.Add("@name", SqlDbType.NVarChar, 200).Value = disbursement.trade_supplier.trading_name;
    }
    if (disbursement.trade_supplier.business_number.Length > 0)
    {
        SqlParameter abn = new SqlParameter("@abn", SqlDbType.NVarChar, 14);
        abn.Value = disbursement.trade_supplier.business_number;
        MessageBox.Show(abn.GetType().ToString());
        MessageBox.Show(abn.Value.ToString());
        if (insertCreditor == null)
        {
            MessageBox.Show("InsertCreditor is null");
        }
        if (insertCreditor.Parameters == null)
        {
            MessageBox.Show("Parameters is null");
        }
        if(abn == null)
        {
            MessageBox.Show("null object sql parameter");
        }
        else
        {
            MessageBox.Show("sql parameter is not null");
            if (insertCreditor.Parameters == null)
            {
                MessageBox.Show("Parameters collection is null?");
            }
            else
            {
                MessageBox.Show("Parameters collection is not null");
                insertCreditor.Parameters.Add(abn);
            }
        }
    }
    if (disbursement.trade_supplier.guid.Length > 0)
    {
        insertCreditor.Parameters.Add("@guid", SqlDbType.UniqueIdentifier).Value = disbursement.trade_supplier.guid;
    }
    else
    {
        insertCreditor.Parameters.Add("@guid", SqlDbType.UniqueIdentifier).Value = new Guid();
    }
}

The offending line is insertCreditor.Parameters.Add(abn);

This causes a NullReferenceExecption: Object Reference not set to an instance of an Object.

However, my MessageBox calls show me that neither abn, nor insertCreditor.Parameters are null.

If I remove this section and just use null there, it gets past it but then happens again with a different value in the next insert after this.

The variables shown when I break point at the offending line:

abn {@abn}  System.Data.SqlClient.SqlParameter
abn.Value   "1223334555"    object {string}
disbursement.trade_supplier {PSConsoleExtractor.TradeSupplier}  PSConsoleExtractor.TradeSupplier
disbursement.trade_supplier.business_number "1223334555"    string
insertCreditor.Parameters   {System.Data.SqlClient.SqlParameterCollection}  System.Data.SqlClient.SqlParameterCollection
this    {PSConsoleExtractor.Form1, Text: PropertySafe Console Exporter (v 2.0.2.0)} PSConsoleExtractor.Form1

Any thoughts?

Answer 1

I ended up resolving the issue by adding in some extra null checks so that it could not reach the point where it was erroring if it was null. This still does not make sense to me as I checked every part of the variables along the way and none of them were null, but it worked somehow.