Null parameter not working with Access and OLE

Question

My understanding is to pass a null via ole parameter you use dbnull.value.

This is my code

// Add user control to panel
pcMaster.Controls.Add(new ucIndexCodes(DBNull.Value, DBNull.Value));

The ucIndexCodes control has this in the

public ucIndexCodes(object CustomerID, object ProjectID)
{
    InitializeComponent();
    try
    {
        // Build parameters
        Queue<OleDbParameter> myParameters = new Queue<OleDbParameter>();

        myParameters.Enqueue(new OleDbParameter("@CustomerID", CustomerID));
        myParameters.Enqueue(new OleDbParameter("@ProjectID", ProjectID));

        // Build SQL statement
        string mySQL = "SELECT IndexCodeID, Attitude, Description, recursiveIndexCodeID, CustomerID, ProjectID FROM tblIndexCodes WHERE CustomerID = @CustomerID AND ProjectID = @ProjectID";
        // Get Data
        using (DataTable tblTopIndexCodes = Classes.clsDatabase.GetData(mySQL, myParameters))
        {
            if (tblTopIndexCodes != null)
            {
                foreach (DataRow tmpRow in tblTopIndexCodes.Rows)
                {
                    // Add Root node
                    TreeListNode objRoot = tlIndexCodes.Nodes.Add(new object[] { tmpRow["Attitude"], tmpRow["Description"].ToString() });
                    // Add child node of root
                    AddChildNode(objRoot, Convert.ToInt16(tmpRow["IndexCodeID"]));
                }
            }
        }
    }
    catch (Exception ex)
    {
        ErrorHandle(ex);
    }
}

The problem is that GetData returns a empty table, so there isn't any rows which means that it ran correctly just didn't return anything.

internal static DataTable GetData(string mySQL, Queue<OleDbParameter> myParameters = null)
{
    // Data set to return
    DataTable myTable = new DataTable();
    try
    {
        using (OleDbConnection myConn = new OleDbConnection())
        {
            // Open connection to database
            myConn.ConnectionString = getStringConnection();
            myConn.Open();

            // Build SQL command
            using (OleDbCommand myCMD = new OleDbCommand())
            {
                myCMD.CommandText = mySQL;
                myCMD.CommandTimeout = 15000;
                myCMD.CommandType = System.Data.CommandType.Text;
                myCMD.Connection = myConn;
                // Add parameters
                if (myParameters != null)
                {
                    foreach (OleDbParameter myParameter in myParameters)
                    {
                        myCMD.Parameters.Add(myParameter);
                    }
                }

                using (OleDbDataAdapter myData = new OleDbDataAdapter())
                {
                    myData.SelectCommand = myCMD;
                    myData.Fill(myTable);
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.Write(ex.Message.ToString());
        myTable = null;
    }
    return myTable;
}

When I run this SQL in Access, it returns the expected data.

SELECT IndexCodeID, Attitude, Description, recursiveIndexCodeID, CustomerID, ProjectID FROM tblIndexCodes WHERE CustomerID Is Null AND ProjectID Is Null

CustomerID and/or ProjectID may not always be null so I need the equal signal for queries like these.

SELECT IndexCodeID, Attitude, Description, recursiveIndexCodeID, CustomerID, ProjectID FROM tblIndexCodes WHERE CustomerID = 1 AND ProjectID = 1

SELECT IndexCodeID, Attitude, Description, recursiveIndexCodeID, CustomerID, ProjectID FROM tblIndexCodes WHERE CustomerID = 1 AND ProjectID = Null

I can't find a solution to this. I made sure CustomerID and ProjectID are in fact null by setting the default to null and also updating the customerid and projectid to null just for testing.

Thanks for the help, Jim

Answer 1

the meaning of NULL is 'unknown value' but NULL is not a value so it cannot be handled as values are; to compare and evaluate NULL you must use specific instructions.

your query could be rewritten as:

SELECT IndexCodeID, Attitude, Description, recursiveIndexCodeID, CustomerID, ProjectID
FROM tblIndexCodes
WHERE 
  (
    (@CustomerID is not null and CustomerID = @CustomerID)
    or
    (@CustomerID is null and CustomerID is null)
  )
  AND
  (
    (@ProjectID is not null and ProjectID = @ProjectID)
    or
    (@ProjectID is null and ProjectID is null)
  )