无效使用null的问题

[hjd85926]

实时错误：94 无效使用null
代码：

Private Sub Form_Load()
Dim a As String
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
a = "provider=Microsoft.jet.OLEDB.4.0;Data source=" & App.path & "\jf.mdb;persist security info=false"
conn1.CursorLocation = adUseClient
conn1.Open a
a = "select cardid as 卡号,studentid as 学号,roomid as 机房号,computerid as 机器号,teacher as 值班老师, starttime as 上机时间,endtime as 下机时间,statue as 状态,bz as 备注 from recording where endtime is null"
rs1.Open a, conn1, adOpenKeyset, adLockPessimistic
Set DataGrid1.DataSource = rs1
DataGrid1.Refresh
a = "select cardid as 卡号,studentid as 学号,roomid as 机房号,computerid as 机器号,teacher as 值班老师, starttime as 上机时间,endtime as 下机时间,statue as 状态,bz as 备注 from recording where endtime is not null"
rs2.Open a, conn1, adOpenKeyset, adLockPessimistic
Set DataGrid2.DataSource = rs2
DataGrid2.Refresh
End Sub

Private Sub Form_Unload(Cancel As Integer)
conn1.Close
Set conn1 = Nothing
End Sub

Private Sub Timer1_Timer()
Dim a As String
Dim b As Date
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
a = "select * from recording where endtime is null"
rs1.Open a, conn1, adOpenKeyset, adLockPessimistic
If rs1.EOF = True And rs1.BOF = True Then
 Exit Sub
End If
rs1.MoveFirst
a = "select cardid as 卡号,studentid as 学号,roomid as 机房号,computerid as 机器号,teacher as 值班老师, starttime as 上机时间,endtime as 下机时间,statue as 状态,bz as 备注,usetime as 上机用时 from recording where endtime is  null"
rs3.Open a, conn1, adOpenKeyset, adLockPessimistic

While Not rs1.EOF
    If rs1.Fields(10) = "" Then
        rs1.Fields(10) = rs1.Fields(5)
        rs1.Update
    Else
    b = Format(rs1.Fields(10), Now)
    str1 = DateDiff("n", b, Now())
    rs1.Fields(10) = Now()
    rs1.Fields(9) = str1
    rs1.Update
    If str1 >= 1 Then
        a = "select * from card where cardid='" + rs1.Fields(0) + "'"
        rs2.Open a, conn1, adOpenKeyset, adLockPessimistic
        rs2.Fields(1) = rs2.Fields(1) - 1
        rs2.Update
    End If
    rs1.MoveNext
    End If
Wend
rs2.MoveFirst
While Not rs2.EOF
    If rs2.Fields(1) <= 0 Then
        Label5.Caption = "成功"
        rs2.MoveNext
    End If
Wend

Set DataGrid1.DataSource = rs3
DataGrid1.Refresh

[Adai011]

有可能是你数据库中有空值。

[饮水需思源]

rs1.Fields(10) = rs1.Fields(5) & ""

[hjd85926]

不太懂，请解释一下

[tim_zhang]

做个函数转换为""就可以了

Public Function RepNull(varIn As Variant) As Variant        '替换Null
    If IsEmpty(varIn) Then
        RepNull = Empty
    Else
        If IsNull(varIn) Then
            RepNull = Empty
        Else
            RepNull = varIn
        End If
    End If
End Function

[hjd85926]

    If rs1.Fields(10) = "" Then 
        rs1.Fields(10) = rs1.Fields(5) 
        rs1.Update 
    Else 
    b = Format(rs1.Fields(10), Now) 
    str1 = DateDiff("n", b, Now()) 
    rs1.Fields(10) = Now() 
    rs1.Fields(9) = str1 
    rs1.Update

好像老有问题
第一个：rs1.Fields(10) = rs1.Fields(5) ，值复制不过去
第二个： b = Format(rs1.Fields(10), Now) ，调试出现错误，就是那个null什么的

怎么解决呢？？

[of123]

说明你的库表各字段数据类型。
rs1.Fields(10) = rs1.Fields(5) 是在做什么？
b = Format(rs1.Fields(10), Now) 是什么意思？

[hjd85926]

While Not rs1.EOF
    If rs1.Fields(10) = "" Then
        rs1.Fields(10) = rs1.Fields(5)
        rs1.Update
    End If               ——————————rs1.Fields(10）是日期类型，当为空时，把rs1.Fields(5)里的日期赋给他。——出错
    b = rs1.Fields(10)
    c = Now()
    str1 = DateDiff("n", b, c)                                ——计算时间间隔。——出错
    rs1.Fields(10) = Now()
    rs1.Fields(9) = str1
    rs1.Update
    a = "select * from card where cardid='" + rs1.Fields(0) + "'"
    rs2.Open a, conn1, adOpenKeyset, adLockPessimistic
    If str1 >= 1 Then
        rs2.Fields(1) = rs2.Fields(1) - 1
        rs2.Update
    End If
    If rs2.Fields(1) <= 0 Then
        提醒框.Text1 = rs2.Fields(0)
        提醒框.Show
        rs2.MoveNext
    End If
    rs1.MoveNext
Wend

不好意思，这个才是运行错误的程序 。

[hjd85926]

并且 b = rs1.Fields(10) 出现null的错误

[of123]

1
If rs1.Fields(10) Is NullThen
rs1.Fields(10) = rs1.Fields(5)
rs1.Update
End If

2

str1 什么类型？DateDiff 的返回值是 Integer。如果参与运算的值有 Null，返回值就是 Null。

rs1.Fields(10) = Now()
rs1.Fields(9) = DateDiff("n", rs1.Fields(10), Now())

[hjd85926]

str1是string

[hjd85926]

If rs1.Fields(10) Is Null Then 这句出现 要求对象的错误

[hjd85926]

求救！

[Adai011]

VB中ISNull用法有问题：
If IsNull(rs1.Fields(10)) then
.....
Else
.....
End If

[hjd85926]

急....弄了好久了也没有头绪...

[zhouhaochen]

[Quote=引用 15 楼 的回复:]

VB中ISNull用法有问题：
If IsNull(rs1.Fields(10)) then
.....
Else
.....
End If
[/Quote]
我用这个解决的。8错