C# Bizarre Object Reference Not Set error with custom class

Question

I have a strange issue where a piece of code that I run all the time is suddenly throwing a "System.NullReferenceException: Object reference not set to an instance of an object." error.

The error occurs on a line involving a custom class that I made to simplify running TSQL action queries with parameters. With the class, I just have to use the naming convention @Param0, @Param1, etc, and pass an array of this type:

public struct SQLParam
{
    private SqlDbType m_Type;
    private dynamic m_Value;
    public SqlDbType Type
    {
        get { return m_Type; }
        set { m_Type = value; }
    }

    public dynamic Value
    {
        get { return m_Value; }
        set { m_Value = value; }
    }
    public SQLParam (SqlDbType ValType, dynamic val)
    {
        m_Value = val;
        m_Type = ValType;
    }
    public SQLParam(SqlParameter sqlParameter)
    {
        m_Value = sqlParameter.Value;
        m_Type = sqlParameter.SqlDbType;
    }
}

...into a method of an object of the class GCDB, called "ActionQueryWithParams", and it works. Except this one time.

Here's the code that's having trouble. I'm including various examples of code that DOES work, and marking the code that DOESN'T in comments:

        GCDB GeekConn = new GCDB();

        string sID = "";
        string sRecurrenceID = JobRecurrenceID.Text.ToString();

        if (string.IsNullOrEmpty(JobID.Text.ToString())) //added record
        {
            //insert job --- THIS WORKS
            SQLParam[] paramslist = new SQLParam[6];

            string sSQL = "INSERT INTO [dbo].[Jobs] ([CustomerID], [JobName], [JobDescription], [IsRecurringJob], [JobStatusID], [LastModifiedByStaffID]) " +
                "OUTPUT INSERTED.JobID " +
                "VALUES(@Param0, @Param1, @Param2, @Param3, @Param4, @Param5)";
            paramslist[0] = new SQLParam(SqlDbType.Int, Convert.ToInt32(cboCustomerID.SelectedValue));
            paramslist[1] = new SQLParam(SqlDbType.VarChar, JobName.Text);
            paramslist[2] = new SQLParam(SqlDbType.VarChar, JobDescription.Text);
            paramslist[3] = new SQLParam(SqlDbType.Bit, Convert.ToBoolean(IsRecurringJob.Checked));
            paramslist[4] = new SQLParam(SqlDbType.Int, Convert.ToInt32(cboJobStatusID.SelectedValue));
            paramslist[5] = new SQLParam(SqlDbType.Int, System.Web.HttpContext.Current.Session["StaffID"]);
            GeekConn.ActionQueryWithParams(sSQL, paramslist);
            if (GeekConn.InsertedID != null)
            {
                sID = GeekConn.InsertedID.ToString();
            }
            paramslist = null;

            //insert new assignment (if applicable - if adding a job to a staff person's list) -- THIS WORKS
            if (!string.IsNullOrEmpty(AssignedToStaffID.Text.ToString()))
            {
                paramslist = new SQLParam[2];
                sSQL = "INSERT INTO [dbo].[JobAssignments] ([JobID], [StaffID]) " +
                    "SELECT @Param0 AS JobID, @Param1 AS StaffID " +
                    "WHERE NOT EXISTS(SELECT * FROM[dbo].[JobAssignments] WHERE JobID = @Param0 AND StaffID = @Param1)";
                paramslist[0] = new SQLParam(SqlDbType.Int, Convert.ToInt32(sID));
                paramslist[1] = new SQLParam(SqlDbType.Int, Convert.ToInt32(AssignedToStaffID.Text.ToString()));
                GeekConn.ActionQueryWithParams(sSQL, paramslist);
            }
            paramslist = null;
        }
        else //edited record
        {
            //do the main update -- THIS WORKS
            SQLParam[] paramslist = new SQLParam[7];

            string sSQL = "UPDATE[dbo].[Jobs] " +
                "SET [CustomerID] = @Param0 " +
                    ",[JobName] = @Param1 " +
                    ",[JobDescription] = @Param2 " +
                    ",[IsRecurringJob] = @Param3 " +
                    ",[JobStatusID] = @Param4 " +
                    ",[LastModifiedByStaffID] = @Param5 " +
                    ",[DateModified] = getdate() " +
                "WHERE [JobID] = @Param6";
            paramslist[0] = new SQLParam(SqlDbType.Int, Convert.ToInt32(cboCustomerID.SelectedValue));
            paramslist[1] = new SQLParam(SqlDbType.VarChar, JobName.Text);
            paramslist[2] = new SQLParam(SqlDbType.VarChar, JobDescription.Text);
            paramslist[3] = new SQLParam(SqlDbType.Bit, Convert.ToBoolean(IsRecurringJob.Checked));
            paramslist[4] = new SQLParam(SqlDbType.Int, Convert.ToInt32(cboJobStatusID.SelectedValue));
            paramslist[5] = new SQLParam(SqlDbType.Int, System.Web.HttpContext.Current.Session["StaffID"]);
            paramslist[6] = new SQLParam(SqlDbType.Int, Convert.ToInt32(JobID.Text));
            GeekConn.ActionQueryWithParams(sSQL, paramslist);

            paramslist = null;

            //auto insert new occurrence (if this is a recurring job and there are no occurrences already) -- THIS THROWS AN ERROR
            if ((IsRecurringJob.Checked) && (JobRecurrenceID.Text.ToString() == ""))
            {
                paramslist = new SQLParam[2];
                sSQL = "INSERT INTO [dbo].[JobRecurrences] ([JobID], [StatusLastModified], [LastModifiedByStaffID]) " +
                    "OUTPUT INSERTED.[JobRecurrenceID] " +
                    "SELECT @Param0 AS JobID, getdate(), @Param1 AS StaffID " +
                    "WHERE NOT EXISTS (SELECT * FROM [dbo].[JobRecurrences] WHERE JobID = @Param0)";
                paramslist[0] = new SQLParam(SqlDbType.Int, Convert.ToInt32(JobID.Text));
                paramslist[1] = new SQLParam(SqlDbType.Int, System.Web.HttpContext.Current.Session["StaffID"]);
 // ERROR IS HERE: System.NullReferenceException: Object reference not set to an instance of an object.
                GeekConn.ActionQueryWithParams(sSQL, paramslist);
                if (GeekConn.InsertedID != null)
                {
                    sRecurrenceID = GeekConn.InsertedID.ToString();
                }
            }
            paramslist = null;
        }

        GeekConn = null;

I've confirmed that the SQL statement for auto-inserting a new occurrence works in SSMS. And, as far as I can tell, there's nothing in the broken code that isn't also in the working code. Can anybody spot anything I'm missing?

Many thanks!

You should be using a `class` instead of a `struct`. See https://stackoverflow.com/questions/441309/why-are-mutable-structs-evil — , Mar 20 '19 at 19:56
Have you checked that all the bits and pieces of `System.Web.HttpContext.Current.Session["StaffID"]` are non-null? Debuggers are very handy for debugging — Flydog57, Mar 20 '19 at 20:04
You need to identify which variable is null. A debugger can be helpful for this, as Flydog stated. — John Wu, Mar 20 '19 at 20:19

score 0 · Answer 1 · answered Mar 20 '19 at 20:42

Many thanks, all! The problem appears to have fixed itself when I fixed another issue (where it wasn't always loading the most recent JobRecurrence into JobRecurrenceID.Text on load).

I ran another test (using the debugger, as recommended), and it does appear to work correctly when the query is actually inserting a record. The issue was that I sometimes had the query run but, because of the WHERE condition (NOT EXISTS), it wasn't actually inserting a record, and it seems it was probably breaking this line of code in the GCDB.ActionQueryWithParams method (only including relevant code):

        bool bolIsInsert = (sSQLUpper.Contains("OUTPUT INSERTED."));
        try
        {
            if (bolIsInsert)
            {
                cmd.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
                InsertedID = (int)cmd.ExecuteScalar();
            } else
            {
                cmd.ExecuteNonQuery();
            }
        }
        catch (SqlException e)
        {
            Error = e.Message.ToString();
        }

Apparently I need to include a way to handle INSERT statements that did not actually insert anything. Hmm... Well, that's a separate question. :)

C# Bizarre Object Reference Not Set error with custom class

1 Answers1