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?

But it doesn't say that `insertCreditor.Parameters` is *null*. It says that it's not an Object. That's huge difference. Check what does it really contain, e.g. using debugger. — David Ferenczy Rogožan, Sep 24 '15 at 00:46
Using messageboxes is a pretty weird way of debugging. By the way, what happens if you change you `if { } if { }` blocks into `if { } else if { }` ones? — bashis, Sep 24 '15 at 00:54
I use MessageBoxes as I need to run command line arguments and I am not sure how to do that with the debugger, however if I change them into else if statements instead of nested if statements, I get to the same final point and get the same error. If anyone can explain how to debug with command line parameters, I would be happy to try. — James Sunderland, Sep 24 '15 at 01:05
@DawidFerenczy What do you mean? The error *does* mean that something was null. — Rob, Sep 24 '15 at 01:38
Okay, got debug going with command line arguments, insertCreditor.Parameters is of Type System.Data.SqlClient.SqlParameterCollection and abn is of Type System.Data.SqlClient.SqlParameter. However, despite adding a breakpoint on the line "abn.Value = disbursement_trade_supplier.business_number;", it skips past that breakpoint. However if I drill down far enough using the debugger, it does appear the collection has a 4th parameter in it whihc is null. Is this normal? — James Sunderland, Sep 24 '15 at 01:39
@Rob But how it could get into `else` branch of `if (insertCreditor.Parameters == null)` condition if it's actually null. — David Ferenczy Rogožan, Sep 24 '15 at 01:41
@JamesSunderland The code you're running is different from your source code, in that case. Try a clean and rebuild. Also, it's almost definitely not `insertCreditor.Parameters.Add(abn);` that's throwing the null reference exception. I fail to see how that would be possible. Again, that leads me to the conclusion that the program you're running is different from the source, so the line number is not matching. — Rob, Sep 24 '15 at 01:41
I have already run clean code rebuilt it. It is picking up the breakpoints, mostly, with the exception of the line where I assign the sqlparameter a value. However as mentioned, the only anomaly I can see is that extra null parameter sitting at index 3 of the inner _items variable in the collection. It is not showing up as a member of the InnerList, but in the Raw View — James Sunderland, Sep 24 '15 at 01:50
@JamesSunderland Do you use **debug** configuration? Release configuration optimizes code and produces similar weird behavior because some part of code are optimized out (for example assignment to a variable which is not used is removed from compiled code, so breakpoint doesn't work there). — David Ferenczy Rogožan, Sep 24 '15 at 01:56
Have you checked that `disbursement` and `disbursement.trade_supplier` are both not null? — slugster, Sep 24 '15 at 01:57
Both disbursement and disbursement.trade_supplier are not null. @Dawid Ferenczy, I am indeed running in Release configuration, but I can see from the line after that point, that the value has been set correctly. From further testing, I can also see that the Parameters array initially has 0 items in the _items variable, but as soon as I add the first parameter, it adds an extra three sqlparameters to the _items variable, all set to null. — James Sunderland, Sep 24 '15 at 01:59
@JamesSunderland OK. Still I would start with changing configuration to **debug** while debugging :) — David Ferenczy Rogožan, Sep 24 '15 at 02:00
Changing to debug mode has not helped any unfortunately. Any ideas? — James Sunderland, Sep 24 '15 at 02:28
Please can you post the full stacktrace of the exception (call .ToString on the exception object). — MonkeyPushButton, Oct 14 '15 at 12:49

score 0 · Accepted Answer · answered Dec 03 '15 at 22:00

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.

NullReferenceException when Adding a SqlParameter to a SqlParameterCollection

1 Answers1