Null参数不适用于Access和OLE

本文关键字：OLE Access 适用于 参数 不适用 Null | 更新日期: 2023-09-27 18:25:30

我的理解是通过ole参数传递null，您使用dbnull.value.

这是我的代码

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

ucIndexCodes控件在中有此项

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);
    }
}

问题是GetData返回一个空表，因此没有任何行，这意味着它运行正确，只是没有返回任何内容。

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;
}

当我在Access中运行此SQL时，它会返回预期的数据。

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

CustomerID和/或ProjectID可能并不总是为null，因此我需要对此类查询使用相同的信号。

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

我找不到解决这个问题的办法。我通过将默认值设置为null并将CustomerID和ProjectID更新为null以进行测试，确保CustomerID和ProjectID实际上为null。

谢谢你的帮助，Jim

Null参数不适用于Access和OLE

NULL的含义是"未知值"，但NULL不是值，因此不能按值处理；要比较和评估NULL，必须使用特定的说明。

您的查询可以重写为：

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)
  )

在SQL中，与NULL的比较结果总是为false，甚至NULL=NULL。

您可以通过如下修改SQL来修复它：

       string mySQL = "SELECT IndexCodeID, Attitude, Description, recursiveIndexCodeID, CustomerID, ProjectID FROM tblIndexCodes WHERE (@CustomerID IS NULL OR CustomerID = @CustomerID) AND (@ProjectID IS NULL OR ProjectID = @ProjectID)";
相关文章：