问题描述
我已经用Google搜索了此错误，还没有得出为什么我会收到此错误的结论.我正在尝试用一些数据填充DataGridView.这是我的代码.
I've Googled this error and haven't drawn up a conclusion to why I'm receiving this error. I'm trying to fill a DataGridView with some data. Here is my code.
Private Sub LoadGrid()
Dim cmd As New OleDbCommand
Dim dt As DataTable
With cmd
.CommandText = "project_master_query"
.CommandType = CommandType.StoredProcedure
.Connection = New OleDbConnection(My.Settings.cnnString)
End With
dt = GetData(cmd)
dgvData.DataSource = dt
End Sub
Private Function GetData(ByVal cmd As OleDbCommand) As DataTable
Dim dt As New DataTable
Using cmd.Connection
cmd.Connection.Open()
dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))
End Using
Return dt
End Function
查询存储在Access中的"project_master_query".
Query "project_master_query" stored within Access.
SELECT project_master.*, location_master.LocationName,
project_currentmilestonedef.MilestoneDefID,
project_currentmilestonedef.MilestoneName, project_regions.RegionName,
owner_fullname.FullName AS OwnerFullName, designer_fullname.FullName AS DesignerFullName,
project_issuecount.HasOpenIssues, project_updated_closedate.UpdatedCloseDate,
project_bonusdays.BonusDays, project_bonusdays.IsGreen, project_bonusdays.IsYellow,
project_bonusdays.IsRed, checklist_days_perproject_defined_1.Week1,
checklist_days_perproject_defined_1.Week2, checklist_days_perproject_defined_1.Week3,
checklist_days_perproject_defined_1.Week4, project_issueduration.ProjectIssueDurationDays,
project_active_status.ProjectIsOpen, project_requirement_status.RequirementStatusName,
priority_def.PriorityShortName
FROM project_requirement_status
RIGHT JOIN (project_regions
RIGHT JOIN (priority_def RIGHT JOIN (location_master
RIGHT JOIN ((((checklist_days_perproject_defined AS checklist_days_perproject_defined_1
RIGHT JOIN ((((((((contacts_fullname AS designer_fullname
RIGHT JOIN (contacts_fullname AS owner_fullname
RIGHT JOIN project_master ON owner_fullname.ContactID = project_master.ContactOwner)
ON designer_fullname.ContactID = project_master.ContactDesigner)
LEFT JOIN project_issuecount
ON project_master.ProjectID = project_issuecount.ProjectID)
LEFT JOIN project_currentmilestonedef
ON project_master.ProjectID = project_currentmilestonedef.ProjectID)
LEFT JOIN project_within_benchmark_week1
ON project_master.ProjectID = project_within_benchmark_week1.ProjectID)
LEFT JOIN project_within_benchmark_week2
ON project_master.ProjectID = project_within_benchmark_week2.ProjectID)
LEFT JOIN project_within_benchmark_week3
ON project_master.ProjectID = project_within_benchmark_week3.ProjectID)
LEFT JOIN project_updated_closedate
ON project_master.ProjectID = project_updated_closedate.ProjectID)
LEFT JOIN checklist_days_perproject_defined
ON project_master.ProjectID = checklist_days_perproject_defined.ProjectID)
ON checklist_days_perproject_defined_1.ProjectID = project_master.ProjectID)
LEFT JOIN project_issueduration
ON project_master.ProjectID = project_issueduration.ProjectID)
LEFT JOIN project_active_status
ON project_master.ProjectID = project_active_status.ProjectID)
LEFT JOIN project_bonusdays ON project_master.ProjectID = project_bonusdays.ProjectID)
ON location_master.LocationID = project_master.Location)
ON priority_def.PriorityDefID = project_master.ProjectPriority)
ON project_regions.RegionID = project_master.Region)
ON project_requirement_status.RequirementStatusID = project_master.RequirementStatus;
您可以看到查询中根本没有Nz，所以我根本不明白为什么会这样.一旦调用dt.Load，就会发生错误.
As you can see there is no Nz in the query at all so I don't understand why this happening at all. The error occurs once the dt.Load is called.
推荐答案
Nz函数最有可能在您通过多个联接之一引用的视图/查询中使用.您必须仔细阅读所有这些内容.
The Nz function is most likely in a view/query you are referencing in one of your many joins. You'll have to look through all of them.
由于Nz()是Access应用程序的功能，而不是Access驱动程序的功能，因此，当您尝试从Access应用程序外部使用它时，它将失败.您可以将Nz替换为IIf(IsNull())构造.
As Nz() is a function of the Access application and not the Access driver, it will fail anytime you try to use it from outside the Access application. You can replace the Nz with an IIf(IsNull()) construct.
请参阅IIf 和 IsNull
放在一起时:
Nz(expr, [valueifnull])
成为
IIf(IsNull(expr), valueifnull, valueifnotnull)
示例
默认值:Nz(tbl.A) => IIf(IsNull(tbl.A), '', tbl.A)
有后备广告:Nz(tbl.A, tbl.B) => IIf(IsNull(tbl.A), tbl.B, tbl.A)
这篇关于表达式中未定义的函数"Nz"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！