注册 登录
编程论坛 VB6论坛

求助,无效使用NULL的出错该如何

cut 发布于 2015-06-06 12:47, 576 次点击
Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
    If InStr(Node.Key, ",") = 0 Then Exit Sub
    Dim Temp
    txtSQL = ""
    Temp = Split(Node.Key, ",")
    For i = LBound(Temp) To UBound(Temp) - 1
        If XArray(i) <> "订单日期" Then
            txtSQL = txtSQL & XArray(i) & "='" & Temp(i) & "' and "
        Else
            txtSQL = txtSQL & XArray(i) & "=#" & Temp(i) & "# and "
        End If
    Next i
   
    If XArray(i) <> "订单日期" Then
        txtSQL = "SELECT 分店,订单日期,订单编号,产品名称,明细,单位数量,单位,采购数量,采购小计,备注 FROM [汇总记录$A1:J20000] WHERE " & txtSQL & XArray(i) & "='" & Temp(i) & "'"
    Else
        txtSQL = "SELECT 分店,订单日期,订单编号,产品名称,明细,单位数量,单位,采购数量,采购小计,备注 FROM [汇总记录$A1:J20000] WHERE " & txtSQL & XArray(i) & "=#" & Temp(i) & "#"
    End If
   
    RST.Open txtSQL, CNN, adOpenKeyset, adLockPessimistic
    iCount = RST.RecordCount
   
    LstvwInit
    With ListView1
        For j = UBound(Temp) + 1 To RST.Fields.Count - 1
            .ColumnHeaders.Add , , RST.Fields(j).Name, 60
        Next j
        For i = 1 To iCount
            .ListItems.Add , , RST.Fields(UBound(Temp) + 1)
            For j = UBound(Temp) + 2 To RST.Fields.Count - 1
                .ListItems(i).SubItems(j - UBound(Temp) - 1) = RST.Fields(j)  无效使用NULL
            Next j
        RST.MoveNext
        Next i
    End With
    RST.Close
End Sub
5 回复
#2
风吹过b2015-06-06 13:11
加一个判断。在数据库,存在 null 的情况。
if isnull(RST.Fields(j)) then
.ListItems(i).SubItems(j - UBound(Temp) - 1) = ""
else
.ListItems(i).SubItems(j - UBound(Temp) - 1) = RST.Fields(j)
end if
#3
cut2015-06-06 13:39
又另一个错误了~`
只有本站会员才能查看附件,请 登录
#4
cut2015-06-06 15:17
Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
    If InStr(Node.Key, ",") = 0 Then Exit Sub
    Dim Temp
    txtSQL = ""
    Temp = Split(Node.Key, ",")
    For i = LBound(Temp) To UBound(Temp) - 1
        If XArray(i) <> "订单日期" Then
            txtSQL = txtSQL & XArray(i) & "='" & Temp(i) & "' and "
        Else
            txtSQL = txtSQL & XArray(i) & "=#" & Temp(i) & "# and "
        End If
    Next i
   
    If XArray(i) <> "订单日期" Then
        txtSQL = "SELECT 分店,订单日期,订单编号,产品名称,明细,单位数量,单位,采购数量,采购小计,备注 FROM [汇总记录$A1:J20000] WHERE " & txtSQL & XArray(i) & "='" & Temp(i) & "'"
    Else
        txtSQL = "SELECT 分店,订单日期,订单编号,产品名称,明细,单位数量,单位,采购数量,采购小计,备注 FROM [汇总记录$A1:J20000] WHERE " & txtSQL & XArray(i) & "=#" & Temp(i) & "#"
    End If
   
    RST.Open txtSQL, CNN, adOpenKeyset, adLockPessimistic  为什么订单号改动其他数字后,都出现"标准表达式中数据类型不匹配"?
    iCount = RST.RecordCount
   
    LstvwInit
    With ListView1
        For j = UBound(Temp) + 1 To RST.Fields.Count - 1
            .ColumnHeaders.Add , , RST.Fields(j).Name, 60
        Next j
        For i = 1 To iCount
            .ListItems.Add , , RST.Fields(UBound(Temp) + 1)
            For j = UBound(Temp) + 2 To RST.Fields.Count - 1
                .ListItems(i).SubItems(j - UBound(Temp) - 1) = RST.Fields(j)
            Next j
        RST.MoveNext
        Next i
    End With
    RST.Close
End Sub
#5
风吹过b2015-06-06 20:15
For i = LBound(Temp) To UBound(Temp) - 1
        If XArray(i) <> "订单日期" Then
            txtSQL = txtSQL & XArray(i) & "='" & Temp(i) & "' and "
        Else
            txtSQL = txtSQL & XArray(i) & "=#" & Temp(i) & "# and "
        End If
    Next i
   
    If XArray(i) <> "订单日期" Then
        txtSQL = "SELECT 分店,订单日期,订单编号,产品名称,明细,单位数量,单位,采购数量,采购小计,备注 FROM [汇总记录$A1:J20000] WHERE " & txtSQL & XArray(i) & "='" & Temp(i) & "'"
    Else
        txtSQL = "SELECT 分店,订单日期,订单编号,产品名称,明细,单位数量,单位,采购数量,采购小计,备注 FROM [汇总记录$A1:J20000] WHERE " & txtSQL & XArray(i) & "=#" & Temp(i) & "#"
    End If


你把 txtsql 显示出来了吗?
前面 FOR 组合的条件,然后你生成 SQL 命令时又再次组合一次,是不是这重复组合造成的?

你把生成的  txtsql  显示一下吧。然后检查内容是否正确,是否与预期相同,可以使用 debug.print txtSQL 语句 。
1