.net – 表达式中未定义的函数’Nz’

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”.

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后会发生错误.

解决方法

由于Nz()是Access应用程序的一个功能而不是Access驱动程序,因此只要您尝试从Access应用程序外部使用它,它就会失败.您可以使用IIf(IsNull())构造替换Nz.

请参阅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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!