我的理解是通过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
的含义是"未知值",但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)";