mysql - vb 6.0 谁能帮我处理我的代码？

Question

我正在使用我的项目库存系统，我想使用 2 DTPicker 在我的 listview1 的 mysql 中的 books 表中显示过滤日期，并为其制作报告。如果它只是查询，我在类模块 idk 中的查询中有一个错误，我真的很困惑，我是 vb 6.0 的初学者...请需要你们的帮助。

我使用了 2 个表，即 books 和 supplier。

我在“类模块”中的代码:

Sub DisplayList(ListView1 As ListView, DateFrom As Date, DateTo As Date)
Dim lstItem As ListItem, a As Integer
Dim rs As New ADODB.Recordset
Dim sql As String
If rs.State = adStateOpen Then rs.Close

sql = " SELECT supplier.category,books.title,books.dataAcquired,books.amount,books.quantity,books.accesionno,books.conditions" & _
      " From supplier INNER JOIN books" & _
      " ON supplier.code=books.code" & _
      " WHERE (((books.dataAcquired)>=#" & DateFrom & "#) and ((books.dataAcquired) <=#" & DateTo & "#))" & _
      " GROUP BY supplier.category,books.title,books.dataAcquired,books.amount,books.quantity,books.accesionno,books.conditions" & _
      " ORDER BY books.dataAcquired DESC;"
   rs.Open sql, cnn

   ListView1.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = ListView1.ListItems.Add(, , a, 1, 1)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            rs.MoveNext
            Loop

End Sub

我的代码在我的表单中:

Private Sub Show_Click()
clsData.DisplayList ListView1, DTPicker1.Value, DTPicker2.Value
lblCount.Caption = ListView1.ListItems.Count
End Sub

Private Sub Form_Load()
DTPicker1.Value = Date
DTPicker2.Value = Date
End Sub

Private Sub Form_Activate()
clsData.DisplayList ListView1, DTPicker1.Value, DTPicker2.Value
lblCount.Caption = ListView1.ListItems.Count
End Sub

Answer 1

将#更改为'

format date how yyyy-MM-dd or yyyyMMdd

sql = " SELECT supplier.category,books.title,books.dataAcquired,books.amount,books.quantity,books.accesionno,books.conditions" & _
  " From supplier INNER JOIN books" & _
  " ON supplier.code=books.code" & _
  " WHERE (((books.dataAcquired)>='" & format(DateFrom,"yyyy-MM-dd") & "') and ((books.dataAcquired) <='" & format(DateTo,"yyyy-MM-dd") & "'))" & _
  " GROUP BY supplier.category,books.title,books.dataAcquired,books.amount,books.quantity,books.accesionno,books.conditions" & _
  " ORDER BY books.dataAcquired DESC;"

在为记录集空添加验证时改变循环，一些如何

 if RecordsetIsClosed(rs) then exit sub

 While Not RecordSetIsEmpty(rs)
      a = a + 1
      Set lstItem = ListView1.ListItems.Add(, , a, 1, 1)
      lstItem.SubItems(1) = rs(0).Value
      lstItem.SubItems(2) = rs(1).Value
      lstItem.SubItems(3) = rs(2).Value
      lstItem.SubItems(4) = rs(3).Value
      lstItem.SubItems(5) = rs(4).Value
      lstItem.SubItems(6) = rs(5).Value
      lstItem.SubItems(7) = rs(6).Value
      rs.MoveNext
 wend

Public Function RecordSetIsEmpty(ByRef rs As ADODB.Recordset) As Boolean    
'   On Local Error GoTo RecordSetIsEmpty_Error    
'       RecordSetIsEmpty = True    
'       If rs Is Nothing Then
'           RecordSetIsEmpty = True
'           Exit Function
'       End If    
'       If RecordsetIsClosed(rs) = True Then
'           RecordSetIsEmpty = True
'           Exit Function
'       End If    
    RecordSetIsEmpty = (rs.BOF = True And rs.EOF = True)
'   RecordSetIsEmpty_Done:
'       Exit Function
'   RecordSetIsEmpty_Error:
'       Resume RecordSetIsEmpty_Done    
End Function


Public Function RecordsetIsClosed(ByRef rs As ADODB.Recordset) As Boolean    
On Local Error GoTo RecordsetIsClosed_Error
    RecordsetIsClosed = True    
    If rs Is Nothing Then
        RecordsetIsClosed = True
    End If        
    If rs.State <> adStateClosed Then
        RecordsetIsClosed = False
    End If
RecordsetIsClosed_Done:
    Exit Function
RecordsetIsClosed_Error:
    Resume RecordsetIsClosed_Done
End Function

不要忘记打开数据库连接

感谢 Mark Bertenshaw 更新

RecordSetIsEmpty 用于解决执行 movenext 时出现的问题。我记得

使用 RecordsetIsClosed 是因为在某些情况下，数据库管理器返回的不是记录集，或者记录集未正确初始化

例如在执行 movenext 或读取值之前必须使用 movefist 进行访问